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!