joining 4 tables with sql

[musicales]

Not quite the right forum but :
I have four tables (levels of a directory) and I'm building a search query which needs to join all four levels and check values in all four tables. Only trouble is sometimes the fourth level/table doesn't exist - the directory only goes down three levels sometimes. If I inner join the tables then everything is fine, except that I miss all routes that don't have a fourth level (there's nothing to join). But I can't do an outer join on the fourth table because I can't combine an inner and outer join in the same query (I would be inner joining the first three tables and outer joining the last one.)

If you understood that, can you suggest a solution?

[chameleon]

musicales - what database solution are you using?

You need to use a LEFT JOIN on the 4th table. You INNER JOIN the first three, the LEFT JOIN the 4th. The SQL gets to be a NIGHTMARE, but if done right, it will work.

I can't speak for all database platforms, but I know it will work on Microsoft SQL Server -- I've done it! :)

[musicales]

I'm using SQL server too - oh well perhaps I'll go and try again - you don't have an example by any chance?
Many Thanks
M

[sun818]

You can also create a view that displays the data of the four tables and select off that.

[musicales]

In the end I cracked it - I was using OUTER JOIN rather than LEFT JOIN or RIGHT JOIN - when I used LEFT JOIN on the last table it worked fine.

Thanks for your suggestions.

[hakre]

LEFT JOIN takes all from the left and the right table is an extra option. so this should have been worked, or your SQL server is corrupt.

but i would think about your db design, such a query will stress it a lot if your table will grow.

[sun818]

With nested joins, I've been told to join the smallest recordsets first to be efficient.