5 tables need help with LEFT JOIN SQL!!

[Justin Hennessy]

Hi all,

I am working with a computer hardware asset database and I am trying to get
information out of it for each PC in my organisation.

Here is the basic table structure:

Table1 -> Table2 -> Table3
-> Table4 -> Table5

Table1 is the master, table2 and table4 a linking tables to table3 and
table5

This is what I am trying to do:

select table1.name, table3.TotalVisibleMemory, table5.label from (LEFT JOINS
HERE)

I have successfully retrieved the information from table 3 using this SQL:

select table1.name, table3.TotalVisibleMemorySize
from
(table1 LEFT OUTER JOIN table2 ON (table1.id$=table2.GroupComponent))
LEFT OUTER JOIN table3 ON (table2.PartComponent=table3.id$)
order by table1.name;

NOW, if I wanted to add a field table5.label how would I add the 2 LEFT
OUTER statements to this SQL?

I hope someone can help.

Thanks,

Vypre

[Bill Karwin]

Justin Hennessy wrote:

select table1.name, table3.TotalVisibleMemorySize
from
(table1 LEFT OUTER JOIN table2 ON (table1.id$=table2.GroupComponent))
LEFT OUTER JOIN table3 ON (table2.PartComponent=table3.id$)
order by table1.name;

NOW, if I wanted to add a field table5.label how would I add the 2 LEFT
OUTER statements to this SQL?

I'm not sure why you're using an outer join between the linking table
and table3.

I'd try writing the query in the following way:

SELECT table1.name, table3.TotalVisibleMemorySize, table5.label
FROM table1 LEFT OUTER JOIN
(table2 INNER JOIN table3 ON table2.PartComponent = table3.id$)
ON table1.id$ = table2.GroupComponent
LEFT OUTER JOIN
(table4 INNER JOIN table5 ON table4.someField = table5.id$)
ON table1.id$ = table4.someOtherField
ORDER BY table1.name

Of course, replace someField and someOtherField with the actual fields
in table4 that you use to link to table1 and table5.

Regards,
Bill K.