How do I do this? Three tables only, but ...

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)

How do I do this? Three tables only, but ...

Author

Topic

beady
Starting Member

28 Posts

Posted - 2006-08-22 : 04:47:43

I need some help with writing this query:
There are three tables:
CE
idX

A
idX
idCE -> foreign key to CE

F
idX
idCE - foreign key to CE

Table CE may or may not have idX. If idX is not in CE then it will probably be in tables A or F, otherwise no records are returned.

The search parameter is idX.

It's probably really easy to write this query, but it's got me beat.

Advice would be gratefully received.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-22 : 04:54:42

Somthing like this


Select IDX From CE Inner  join 
A on CE.IDX = A.IDCE Inner  join 
F on F.IDX = F.IDCE  Where
COALESCE(F.IDX,A.IDX,F.IDX) = @SearchIDX

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 05:09:43

Or this

SELECT	*
FROM	CE
WHERE	SomeCol = @idX
UNION ALL
SELECT	*
FROM	A
WHERE	SomeOtherCol = @idX
SELECT	*
FROM	F
WHERE	SomeOtherColAgain = @idX

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 05:10:29

quote:

Originally posted by chiragkhabaria

Select IDX From CE Inner  join 
A on CE.IDX = A.IDCE Inner  join 
F on F.IDX = F.IDCE  Where
COALESCE(F.IDX,A.IDX,F.IDX) = @SearchIDX

Select IDX From CE Inner  join 
A on CE.IDX = A.IDCE Inner  join 
F on F.IDX = F.IDCE  Where
COALESCE(CE.IDX,A.IDX,F.IDX) = @SearchIDX

Peter Larsson
Helsingborg, Sweden

beady
Starting Member

28 Posts

Posted - 2006-08-22 : 06:22:00

quote:

Originally posted by Peso

Or this

SELECT	*
FROM	CE
WHERE	SomeCol = @idX
UNION ALL
SELECT	*
FROM	A
WHERE	SomeOtherCol = @idX
SELECT	*
FROM	F
WHERE	SomeOtherColAgain = @idX

This particular approach results in the following error message:
All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists. I'm not sure what's going on here...

In case it matters, I'm using SQL Server 2000.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-22 : 06:31:38

It has to be like this


SELECT	 IDX 
FROM	CE
WHERE	SomeCol = @idX
UNION ALL
SELECT	 IDX 
FROM	A
WHERE	SomeOtherCol = @idX
UNION ALL 
SELECT	 IDX 
FROM	F
WHERE	SomeOtherColAgain = @idX

Chirag

beady
Starting Member

28 Posts

Posted - 2006-08-22 : 06:36:54

Beautiful! Thank you Chirag, you corrected the problem of Peter's solution. Thanks to all!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 06:44:20

Yes, those darn copy & paste operations...

Peter Larsson
Helsingborg, Sweden

beady
Starting Member

28 Posts

Posted - 2006-08-22 : 06:44:35

Oops! I see what caused the error with the UNION. I cannot retrieve the other fields from the CE table, which is what I need to do. How can I get at the CE fields while still making sure that I can get at idX, if they exist, from the other tables?

beady
Starting Member

28 Posts

Posted - 2006-08-22 : 06:49:21

One more niggly thing: It is possible that CE does not contain any idX value at all! In this solution it seems to assume that CE will have some idX value.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-22 : 06:49:23

Somthing like this


Select * From CE Where IDX In 
(
SELECT	 IDX 
FROM	CE
WHERE	SomeCol = @idX
UNION ALL
SELECT	 IDX 
FROM	A
WHERE	SomeOtherCol = @idX
UNION ALL 
SELECT	 IDX 
FROM	F
WHERE	SomeOtherColAgain = @idX
)

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-22 : 06:51:33

quote:
Originally posted by beady

One more niggly thing: It is possible that CE does not contain any idX value at all! In this solution it seems to assume that CE will have some idX value.

you mean to say that it should not look into table CE for the IDX Value??
if so then the query should be like this..


Select * From CE Where IDX In 
(
SELECT	 IDX 
FROM	A
WHERE	SomeOtherCol = @idX
UNION ALL 
SELECT	 IDX 
FROM	F
WHERE	SomeOtherColAgain = @idX
)

It will be great if you can post some sample data with the expected output. so someone over here can give you extact solutions, what is required.

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 06:52:29

The trick is that each select in the union has to have the same number of columns.

SELECT	SomeCol, Col2, Col3
FROM	CE
WHERE	SomeCol = @idX
UNION ALL
SELECT	SomeOtherCol, null, null
FROM	A
WHERE	SomeOtherCol = @idX
UNION ALL 
SELECT	SomeOtherCol, col2, col4
FROM	F
WHERE	SomeOtherColAgain = @idX

Peter Larsson
Helsingborg, Sweden

beady
Starting Member

28 Posts

Posted - 2006-08-22 : 08:13:28

quote:
Originally posted by Peso

The trick is that each select in the union has to have the same number of columns.
SELECT SomeCol, Col2, Col3
FROM CE
WHERE SomeCol = @idX
UNION ALL
SELECT SomeOtherCol, null, null
FROM A
WHERE SomeOtherCol = @idX
UNION ALL
SELECT SomeOtherCol, col2, col4
FROM F
WHERE SomeOtherColAgain = @idX

Peter Larsson
Helsingborg, Sweden

Yes, and the columns will have to be of the same datatype, which is not the case in my CE table :(

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-22 : 08:14:59

have you tried the approach which i posted?

Chirag

beady
Starting Member

28 Posts

Posted - 2006-08-22 : 08:23:38

quote:
Originally posted by chiragkhabaria

quote:
Originally posted by beady

One more niggly thing: It is possible that CE does not contain any idX value at all! In this solution it seems to assume that CE will have some idX value.

you mean to say that it should not look into table CE for the IDX Value??
if so then the query should be like this..

Select * From CE Where IDX In
(
SELECT IDX
FROM A
WHERE SomeOtherCol = @idX
UNION ALL
SELECT IDX
FROM F
WHERE SomeOtherColAgain = @idX
)

It will be great if you can post some sample data with the expected output. so someone over here can give you extact solutions, what is required.

Chirag

I do need to look for the idX value in the CE table. If it is NOT there, then either table F OR A will contain the idX.

Wait, I have to nut this out a bit more before I post any more info/questions.

I'll probably be back about this a bit later. Thanks for your patience!

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-22 : 08:28:50

try this then


Select CE.* From CE Inner  join 
A on CE.IDX = A.IDCE Inner  join 
F on F.IDX = F.IDCE  Where
COALESCE(CE.IDX,A.IDX,F.IDX) = @SearchIDX

Chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources