Easy Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Easy Question

Author

Topic

bglodde
Starting Member

25 Posts

Posted - 2003-06-09 : 15:15:18

Here's what I have:
4 tables that form a heirarchy with a simple foreign key relationship:

TABLE1
- id
- desc

TABLE2
- id
- table1 id
- desc

TABLE3
- id
- table2 id
- desc

TABLE4
- id
- table3 id
- desc

I need to select the "desc" out of TABLE 1 with the "id" from TABLE 4.

What I'm running into is, I can either get the "desc" I need or the "ids" I need but not both. Some values in these tables can be null.

SELECT TABLE1.DESC, TABLE4.ID
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.ID = TABLE2.TABLE1ID
LEFT JOIN TABLE3 ON TABLE2.ID = TABLE3.TABLE2ID
LEFT JOIN TABLE4 ON TABLE4.ID = TABLE4.TABLE3ID
ORDER BY TABLE1.DESC

Returns my "desc" fields, but doesn't pick up the child records below it. Switching to a RIGHT JOIN brings me the "id" fields but NULL for the "desc" fields. INNER JOIN cuts off the null values and I need those.

Any suggestions?

Edited by - bglodde on 06/09/2003 15:16:34

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-09 : 15:46:53

I don't understand the statement "I need null values".

If your talking about the Id's, and let say your left join 2 to 3, and get null, there won't be any level 4 rows either.

Is that what you mean?

Brett

8-)

bglodde
Starting Member

25 Posts

Posted - 2003-06-09 : 15:57:10

It's a *little* convoluted but here's what I need to get from the query.

A list of all TABLE1.desc, regardless if they have a corresponding TABLE4.id (child attached). For those that DO have a child attached, I'd like them listed with their respective child id.

To illustrate:
I know there are 5 bottom level nodes that belong to a particular table1.id

parent element1
- child (table2)
- - child (table 3)
- - - child1 (table 4)
- - - child2 (table 4)
- - - child3(table 4)
- - - child4 (table 4)
- - - child5 (table 4)

I'd like to see returned:
parent element1 - child1
parent element1 - child2
parent element1 - child3
parent element1 - child4
parent element1 - child5

For elements with no children, I just need them listed once:
parent element2 - NULL
parent element3 - NULL

That's where I need the null value. The main concern is to list the parent node values.

I hope that's not too ridiculous :]

Edited by - bglodde on 06/09/2003 15:58:21

bglodde
Starting Member

25 Posts

Posted - 2003-06-09 : 16:23:51

Even better would be this:
parent element1 (table1.desc) - 3 (sum of table4.ids)
parent element2 - 0
parent element3 - 0
parent element4 - 6

But I can't figure out a way to summarize that succinctly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources