MySql 'Select' help
I have some tables.
These tables all have one column in common called 'classified_id':
main_table: cars_table:
id (PK) => 4222 id (PK) => 1021
classified_id => 25 classified_id => 25
Now, I want whenever a search is performed, to compare if any of the main_table.classified_id matches any of the cars_table.classified_id (in this case).
the cars_table may return no matches! the classified_id in every table is the only relation. I would never need to compare the cars_table.classified_id to main_table.classifie开发者_如何学运维d_id, but the other way around is what I need (main_table.classified_id=cars_table.classified_id).
I don't know what type of Join to use... Any help? Left Join?
Thanks
Yes, it is a left join
Something like
SELECT *
FROM main_table mt LEFT JOIN
cars_table ct ON mt.classified_id = ct.classified_id
SELECT * FROM main_table LEFT JOIN cars_table ON main_table.classified_id = cars_table.classified_id
Will return 1-?? copies of main_table rows depending on how many in cars_table that matches. If there is no match all values from cars_table will be NULL
I'm not quite sure what you're asking, but assuming you're looking for all the records in main_table that match some condition, and you'd like to bring along any records in the cars_table that have the same classified_id as any of the matching records in the main table, even if there are no such records in the cars_table, you should be using left join like so:
SELECT * FROM main_table AS M LEFT JOIN cars_table as C ON C.classified_id = M.classified_id WHERE f(M.foo)
When there is an M record such that f(M.foo) evaluates to TRUE, but no corresponding C record, the M record will still appear in the result set, but all the C fields will be NULL.
A more thorough explanation of left outer joins (same thing as a left join) can be found here:
http://en.wikipedia.org/wiki/Left_outer_join#Sample_tables
You'll need to look at the sample data to make sense of the example they give for left outer join:
http://en.wikipedia.org/wiki/Left_outer_join#Sample_tables
精彩评论