MySql 'Select' help

Question

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.classified_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

Answer 1

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

Answer 2

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

Answer 3

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