MySQL INNER JOIN confusion -- go easy on me, I'm learning
plat: plat_title, plat_initial, builder_id
company: company_title, company_type
address: ref_city, ref_state, ref_map_x, ref_map_y
graphic: img_file, img_type, featured, show_public, current_item, img_sequence
img_rep: assoc_table, assoc_id, photo_id
(among other fields)
So what I need is to get the information (I was trying INNER JOIN but it doesn't like me) starting with the "plat" (community)
- and then the name of the builder for that plat (plat.builder_id to company.company_title if company.company_type is 'builder')
- and then the logo for the plat (via the "graphic" table using the "img_rep" table's assignment, i.e. img_rep.assoc_table = plat and img_rep.assoc_id = plat.id and img_rep.photo will be the graphic.id for several images -- the photo I need will have graphic.img_type = 'logo'
- and then the "main photo" for the image, which will be the same as the logo except graphic.img_type = 'photo' and then it will need to have the lowest value of graphic.img_sequence value of the possible options
I don't know if I should just do this with several queries or if there's a way to just grab what I need in one fell swoop... I'm trying to practice writing more efficient code; I used to just do each table in its own query, so I've been using MySQL a long time, but I haven't been doing it all that well, haha.
Any advice or suggestions would be greatly appreciated; thanks in advance!