Select among three tables - 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 2005 Forums

Transact-SQL (2005)

Select among three tables

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2008-12-15 : 01:02:47

I have three tables, I want to have a select order to find the rows which PersonnelId=@Id among these three tables.
I dont want the repeated rows be shown. I mean if it found User1 in both table1 and table2, write it once in result table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 01:16:02

[code]SELECT user from table1
where PersonnelId=@Id
UNION
SELECT user from table2
where PersonnelId=@Id
UNION
SELECT user from table3
where PersonnelId=@Id[/code]

Exir
Posting Yak Master

151 Posts

Posted - 2008-12-15 : 01:20:15

Thanks alot for your fast response as always. but my tables has not same fields so this error occured:
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 01:22:21

quote:
Originally posted by Exir

Thanks alot for your fast response as always. but my tables has not same fields so this error occured:
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

you should have same number of fields on each side of union and also the datatypes of corresponding ones should also be same.

Exir
Posting Yak Master

151 Posts

Posted - 2008-12-15 : 01:25:06

I know, but they are not equal in the tables which i have. now what should i do in this situation? no way for these tables which thay have not equal number of fields?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 01:30:05

quote:
Originally posted by Exir

I know, but they are not equal in the tables which i have. now what should i do in this situation? no way for these tables which thay have not equal number of fields?

]
yup. you've a workaround. fill the other fields positions with null values

for example supose your tables have fields
table1
f1 int,f2 varchar(50)
table2
f1 int,f2 datetime
table3
f1 varchar(50),f2 datetime

you can do like below to combine them using UNION


SELECT f1,f2,CAST(NULL AS datetime) AS f3
FROM table1
UNION
SELECT f1,NULL,f2
FROM table1
UNION
SELECT NULL,f1,f2
FROM table1

Exir
Posting Yak Master

151 Posts

Posted - 2008-12-15 : 01:59:52

Thank you very much :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-15 : 02:04:01

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources