0

I am trying to run a query given three tables.

DECLARE @TABLE1 TABLE (ID CHAR(2))
DECLARE @TABLE2 TABLE (ID CHAR(2))
DECLARE @TABLE3 TABLE (ID CHAR(2))

INSERT INTO @TABLE1 VALUES('1')
INSERT INTO @TABLE1 VALUES('2')
INSERT INTO @TABLE2 VALUES('1')

--NOTHING in TABLE3

I Need to get only the values that are present and ignore the null table. This doesn't work since TABLE3 has no values.

SELECT ID 
  FROM @TABLE1 
INTERSECT 
SELECT ID 
  FROM @TABLE2 
INTERSECT  
SELECT ID 
 FROM @TABLE3

 **Result should be 1**

How do I ignore the any table if it's null but keep the other values?

kiev
  • 2,040
  • 9
  • 32
  • 54
  • What do you expect the result to be? 1? 1,2? or 1,2,1? – JohnnyO Jan 22 '13 at 03:44
  • @BenjiWiebe: TSQL means either Sybase or MS SQL Server. Please don't edit if you're not sure. – OMG Ponies Jan 22 '13 at 03:46
  • Isn't the idea of INTERSECT to return only rows where the values are present in all of the INTERSECT's? Maybe you don't want INTERSECT – Greg Jan 22 '13 at 03:47
  • Instead of describing the result you want with a word problem, please show the actual desired results you want as part of the question (not listed in a comment). – Aaron Bertrand Jan 22 '13 at 03:52
  • ok, i edited the question with that – kiev Jan 22 '13 at 03:55
  • What would you expect if there was data in table3 but not table2? – Greg Jan 22 '13 at 04:15
  • same thing- ignore table2 – kiev Jan 22 '13 at 04:41
  • Those who marked this as "Duplciate" didn't read what the OP was after. He's using intersect to try and get results intersect can't get. But instead of reading the question and understanding that the OP is using the wrong function, they quickly agreed that it was a duplicate when it's no where close to a duplicate – xQbert Jan 22 '13 at 15:25

3 Answers3

1

Why not do a union of select distincts from each table, and then group that by ID and select count(*), and select only rows with count(*) equal to the maximum value of count(*) in the result?

It's a bit of a mess of subqueries at this point unfortunately but you should get the logic :)

Patashu
  • 21,443
  • 3
  • 45
  • 53
1

Intersect is not going to work for you as you can't add conditions to it.

From what I understand you want to select all records where the ID appears in at least 2 of the tables. I am assuming that the ID is unique to each table.

The following works in MS SQL Server:

DECLARE @TABLE1 TABLE (ID CHAR(2))
DECLARE @TABLE2 TABLE (ID CHAR(2))
DECLARE @TABLE3 TABLE (ID CHAR(2))

INSERT INTO @TABLE1 VALUES('1')
INSERT INTO @TABLE1 VALUES('2')
INSERT INTO @TABLE2 VALUES('1')

--NOTHING in TABLE3
;WITH AllValues AS
(
    SELECT ID 
    FROM @TABLE1 
    UNION ALL
    SELECT ID 
    FROM @TABLE2 
    UNION ALL
    SELECT ID 
    FROM @TABLE3
)
SELECT  ID
FROM    AllValues
GROUP BY ID
HAVING  COUNT(*) > 1
Greg
  • 3,442
  • 3
  • 29
  • 50
0

Maybe... But the design of the system is extremely foreign; a real world example would help understand what you're trying to do.

Select count(*), ID FROM (
Select ID from @table1
UNION
Select ID from @table2
UNION
Select ID from @table3) Derived
Where RowNum =1   
GROUP BY ID
ORder by count(*) DESC

Updated where clause was in wrong place

xQbert
  • 34,733
  • 2
  • 41
  • 62