Need help: Select a row if exists in any 1 of 3 tables

[JosefSchmidt]

Is it possible to select a row if it exists in at least one of three tables? For example, if 3 different tables all contain a field named "USERID", can I obtain a list of all the USERIDs that are in at least 1 of the 3 tables?

Currently I'm using a SQL statement similar to the following:

SELECT DISTINCT a.userid
FROM table a
WHERE a.userid IN (SELECT DISTINCT b.userid FROM tableb b)
OR IN (SELECT DISTINCT c.userid FROM tablec c)
OR IN (SELECT DISTINCT d.userid FROM tabled d)

The problem is that I have WHERE criteria in each of the subqueries which makes the query a memory hog.

Any thoughts/advice?

 

[mwolf00]

SELECT DISTINCT CASE
  WHEN a.userID IS NOT NULL THEN a.userID
  ELSE CASE
    WHEN b.userID IS NOT NULL THEN b.userID
    ELSE c.userID
  END
END
FROM tableA a
LEFT JOIN tableB b 
LEFT JOIN tableC c
WHERE a.userID = 123
OR b.userID = 123
OR c.userID = 123

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[tlbroadbent]

The following should work well and be more efficient than using WHERE IN (SELECT ...). Make sure tableb, tablec and tabled are indexed on the UserID column.

SELECT DISTINCT a.userid 
FROM table a  
WHERE Exists (SELECT * FROM tableb Where UserID = a.userid)
   OR Exists (SELECT * FROM tablec Where UserID = a.userid)
   OR Exists (SELECT * FROM tabled Where UserID = a.userid)

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[mwolf00]

Terry - what if the userID is not in tableA? Won't this fail to return a record?

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[tlbroadbent]

mwolf00,

I based the query on the original query which returns the TableA.UserID if the UserID exists in one of the other three tables.

Your code only returns rows that exist in TableA. In order to return the UserID from one of three tables you will need to use a FULL OUTER JOIN rather than LEFT JOIN. In addition, the LEFT JOIN in your query is negated by the WHERE criteria so the query will only return rows that exist in A, B and C.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[RaymondPang]

just a query, can the following work for the case

select user_id from tablea where user_id=123 union
(select user_id from tableb where user_id=123) union
(select user_id from tablec where user_id=123)

is performance poor than using full outer join ?

 

[tektipdjango]

try this but I have not SQL SERVER on my computer right now, so I'm not quite sure

[b]SELECT DISTINCT[/b] a.userid 
[b]FROM[/b] table a  
[b]INNER JOIN[/b]
(
  (
    [b]SELECT[/b] 
    b.userid [b]AS[/b] userid
    [b]FROM[/b]
    tableb
  )
  [b]union[/b]
  (
    [b]SELECT[/b] 
    c.userid [b]AS[/b] userid
    [b]FROM[/b]
    tablec
  )
  [b]union[/b]
  (
    [b]SELECT[/b] 
    d.userid [b]AS[/b] userid
    [b]FROM[/b]
    tabled
  )
)AS b
[b]ON[/b] a.userid=b.userid

django
bug exterminator
tips'n tricks addict