Inner Join Woes

I want a set even where Inner Join doesn't match

[trillianjedi]

Hi guys.

I'm using Inner Join to pull data out of a second table, but I want a row even where the "ON" is not matched, eg the query looks something like:-

SELECT name, age, table2.job FROM table1
INNER JOIN
table2 ON name = surname
ORDER BY DateTime DESC
LIMIT 5;

Basically I want a row returned even where name <> surname and in that case I want name to be displayed as "NULL".

Any thoughts?

Thanks,

TJ

[Rebrandt]

Hi,

I think you have to use LEFT JOIN instead og the INNER JOIN. That way all rows from the left table will be returned with the NULL values of the column that did not match the column from the right table.

[trillianjedi]

Thank you Rebrant, I'll give it a go!

TJ

[aspdaddy]

Yes that should fix it. On any join columns where you allow null values, you do have to remember to use outer joins instead of inner joins.

The other way is to have a dummy record in the master table and set a default value in the child table to point to the dummy. That keeps the db using all inner joins but might not be appropriate for your appp.