By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,313 Members | 1,240 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,313 IT Pros & Developers. It's quick & easy.

5 tables need help with LEFT JOIN SQL!!

P: n/a
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

Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.