[Resolved] left join empty row

lobster

I have a query joining 4 tables. vessel_pk being a common thread... but sometimes there will not be a vessel_media row or vessel_note row if the vessel_media.display_order = 1 does not exist and same with vessel_note.

Also notice with this query that all of the vessel_pk in the query are showing up 3 times (instead of the same values combining into one column... how do I fix this?


SELECT vessel.vessel_pk,vessel.vessel_year,vessel.vessel_length,vessel.price,vessel.vessel_model,vessel.num,vessel_manufacturer.vessel_manufacturer_pk,vessel_manufacturer.manufacturer,user.first_name,user.last_name,user.email_address,user.number_1,user.broker_pk,vessel_media.vessel_pk,vessel_media.filename,vessel_media.display_order,vessel_note.vessel_pk,vessel_note.note_type,vessel_note.note
FROM vessel
LEFT JOIN vessel_media ON vessel_media.vessel_pk = vessel.vessel_pk
LEFT JOIN vessel_note ON vessel_note.vessel_pk = vessel.vessel_pk
LEFT JOIN vessel_manufacturer ON vessel_manufacturer.vessel_manufacturer_pk = vessel.vessel_manufacturer_pk
LEFT JOIN user ON user.broker_pk = vessel.broker_pk
WHERE vessel_note.note_type = 1
AND vessel_media.display_order = 1
AND vessel.num > 1000
ORDER by vessel.vessel_length desc

Thanks!

liquorvicar

Also notice with this query that all of the vessel_pk in the query are showing up 3 times (instead of the same values combining into one column... how do I fix this?

remove vessel_media.vessel_pk and vessel_note.vessel_pk from your list of fields.

I have a query joining 4 tables. vessel_pk being a common thread... but sometimes there will not be a vessel_media row or vessel_note row if the vessel_media.display_order = 1 does not exist and same with vessel_note.

not sure what you mean.

lobster

great that took care of the extra columns of vessel pks!

I will try to explain the other problem better.

I am pulling from these 4 tables but sometimes a vessel will not have a row in vessel_media vessel_note. So I have 250 vessels and only 102 come up in the query because the ones that did not have media or notes of display_order or note_type 1 were not returned.

Does this make sense?

mrhappiness

try moving

AND vessel_media.display_order = 1

LEFT JOIN vessel_media ON vessel_media.vessel_pk = vessel.vessel_pk

what happens now?

lobster

This is what I am working with now.

SELECT vessel.vessel_pk,vessel.vessel_year,vessel.vessel_length,vessel.price,vessel.vessel_model,vessel.num,
								vessel_manufacturer.vessel_manufacturer_pk,vessel_manufacturer.manufacturer,
								user.first_name,user.last_name,user.email_address,user.number_1,user.broker_pk,
								vessel_media.filename,vessel_media.display_order,
								vessel_note.note_type,vessel_note.note
								FROM vessel
								LEFT JOIN vessel_media ON vessel_media.vessel_pk = vessel.vessel_pk
								LEFT JOIN vessel_note ON vessel_note.vessel_pk = vessel.vessel_pk
								LEFT JOIN vessel_manufacturer ON vessel_manufacturer.vessel_manufacturer_pk = vessel.vessel_manufacturer_pk
								LEFT JOIN user ON user.broker_pk = vessel.broker_pk
								WHERE vessel_note.note_type = 1
								AND vessel_media.display_order = 1
								AND vessel.num > 1000
								ORDER by vessel.vessel_length desc
								LIMIT $page, $limit

which eliminates the duplicates... if I take out display_order = 1 then I get up to 12 rows for the same vessel. I only need the picture that is listed for display order 1

mrhappiness

what does my tip produce?

SELECT vessel.vessel_pk,vessel.vessel_year,vessel.vessel_length,vessel.price,vessel.vessel_model,vessel.num,
                                vessel_manufacturer.vessel_manufacturer_pk,vessel_manufacturer.manufacturer,
                                user.first_name,user.last_name,user.email_address,user.number_1,user.broker_pk,
                                vessel_media.filename,vessel_media.display_order,
                                vessel_note.note_type,vessel_note.note
                                FROM vessel
                                LEFT JOIN vessel_media ON vessel_media.vessel_pk = vessel.vessel_pk AND vessel_media.display_order = 1
                                LEFT JOIN vessel_note ON vessel_note.vessel_pk = vessel.vessel_pk
                                LEFT JOIN vessel_manufacturer ON vessel_manufacturer.vessel_manufacturer_pk = vessel.vessel_manufacturer_pk
                                LEFT JOIN user ON user.broker_pk = vessel.broker_pk
                                WHERE vessel_note.note_type = 1
                                                                AND vessel.num > 1000
                                ORDER by vessel.vessel_length desc
                                LIMIT $page, $limit

lobster

I get the same result. If there are no media files for a vessel, it is not returned in the results.

lobster

OK I got it!

I also moved the note type to the left join and I go the result I wanted!

Thanks so much!