Those Darn Joins - 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)

Those Darn Joins

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-26 : 20:08:56

Hey guys and gals. I have been looking and learning joins inner and outer, but none of thse work for what i'm trying to do. I think there maybe a way to do what I need, but not sure, using join.

I have 3 tables with an ID and NAME field, among other info which is different per table. I'm trying to get a result that puts all the values in the NAME field as one big list without doing temp tables. Is this possible? For example:

Table 1:
-----------
ID    Name
1     John
2     Joe

Table 2:
-----------
ID    Name
1     Tony
2     Henry

Table 3:
-----------
ID    Name
1     Danny
2     Kenny

The result set I would like is

Table 1:
-----------
Name
John
Joe
Tony
Henry
Danny
Kenny

While I'm at it, lemme explain the purpose in more detail. I have 3 different types of clients in my db which i prefer to store in seperate tables since each require much different type of info. Anyhow, when a client is added to any of the client type tables they are going to be given a random 10 digit number with a prefix consisting of 3 letters, ie: random number = 3657485, then the client number will be DLR0003657485, or ADV0003657485 (the dlr, or ADV depends on the client type.) What I like to happen is the number be different of any one client, I do not want one client to be DLR0003657485 and another be ADV0003657485. Does that make sense? So I want to write an sql statement that checks for the number. I obviously do not want to do three checks, one for each table, so I'm looking to join the three and then add an WHERE clause to see of the number exists by removing the first 3 characters. In the sample code above I used names since it's easier to see, I think. Any extra help/info for my way of doing thise is greatfully appreciated.

Thanks in advance.

- RoLY roLLs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-26 : 20:12:17

Just UNION them together:

SELECT Name
From Table1
UNION ALL
SELECT Name
FROM Table2
UNION ALL
SELECT Name
FROM Table3

Tara

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-26 : 20:15:05

Aha! thank you! I just found that out! haha! too quick for me

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-26 : 20:21:18

one question though...the where clause...

I just tried ther where clause at the end, but here's the catch, each of the column names for name are not the same so i made it look like this:

SELECT Name1 as Name
From Table1
UNION ALL
SELECT Name2 as Name
FROM Table2
UNION ALL
SELECT Name3 as Name
FROM Table3
WHERE name = 'henry'

but I got an error saying that 'name' was not a column name.

I'm building this up slowly, so I may have more questions soon. Thanks.

- RoLY roLLs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-07-26 : 20:24:39

SELECT * FROM
(SELECT Name1 as Name
From Table1
UNION ALL
SELECT Name2 as Name
FROM Table2
UNION ALL
SELECT Name3 as Name
FROM Table3)
WHERE name = 'henry'

Tara

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-26 : 20:30:03

hey tara, i get an error:

Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'WHERE'.

- RoLY roLLs

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-07-26 : 21:06:23

quote:
Originally posted by RoLYroLLs

hey tara, i get an error:

Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'WHERE'.

you need to alias the subquery. try this

SELECT * FROM
(SELECT Name1 as Name
From Table1
UNION ALL
SELECT Name2 as Name
FROM Table2
UNION ALL
SELECT Name3 as Name
FROM Table3) as sbqry
WHERE name = 'henry'

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources