select from subquery two times

Question

I've got TableA with userAId and UserBId.

I also got another TableB with UserId

I would like to select all records from TableA in which userAId and UserBId are in tableB

My solution is:

select * from TableA where 
  userAId IN
  (
  select UserId from Table B
  )

  and UserBId IN
  (
  select UserId from Table B
  )

but i think that it is not the optimal solution as I do select UserId from Table B two times

Any other solutions?

Im using t-sql sql seerver 2008 R2

score 2 · Answer 1 · edited Oct 17 '14 at 20:00

2

Maybe something like this:

select * from TableA a where (
    select count(DISTINCT UserID) from TableB where UserID in (a.UserAId, a.UserBId)
) = 2

Update: I changed Count(*) to Count(DISTINCT UserID) as noted by @Thorsten Kettner

edited Oct 17 '14 at 20:00

Thorsten Kettner

89,309
7
49
73

answered Oct 17 '14 at 19:36

Felk

7,720
2
35
65

1

The only correct answer so far and the only one without upvotes. Incredible. Here is +1 from me. In case there can be multiple entries for one user in tableB this should be `COUNT(DISTINCT UserID)` however. – Thorsten Kettner Oct 17 '14 at 19:49
This query would be of similar speed as OP's attempt, doing join twice on tableB would be much faster – radar Oct 17 '14 at 23:07

score 1 · Answer 2 · answered Oct 17 '14 at 19:59

Your own solution may look a bit too little sophisticated on first glimpse, but it is likely to be the best and most efficient approach. Provided there is an index on tableB.userId, access will be fast.

If access is too slow though, then you can probably speed it up by providing an index on tableA(userAId,userBId).

Stay with your query. It is easy to read, does what it is supposed to do and does it fast.

radar · Answer 3 · 2014-10-17T23:05:20.700

0

Instead of IN clause , doing a JOIN with tableB two times would be faster You can compare it with execution plan on

select A.* from TableA A 
join TableB B1
on A.userAID  = B1.UserId
join TableB B2
AND A.userBId = B2.userID

edited Oct 17 '14 at 23:05

answered Oct 17 '14 at 19:38

radar

13,270
2
25
33

Strange to see upvotes for this. Your query is looking for one record in tableB where userID matches both userAId and userBId. This is quite far from what is being asked. – Thorsten Kettner Oct 17 '14 at 19:45
Yes, and yours doesn't do that. If userAId is 'A' and userBId is 'B' and both users exist in tableB, your query still doesn't find it, because it is looking for a tableB record where UserId is 'A' and 'B' at the same time, which is impossible. Your statement only works for records where userAId = userBId. – Thorsten Kettner Oct 17 '14 at 19:52
@Thorsten Kettner, +1 for pointing out, doing join twice would be faster I thin than in condition – radar Oct 17 '14 at 20:11

score 0 · Answer 4 · answered Oct 17 '14 at 19:54

you can use cte ONLY if data in table b is less. If your query is giving you what you desired then it cannot be converted in join because join will match one to one i.e tableA's one record will be matched tableB's one on join criteria/condition, but here you are matching one's userAID with all and similarly UserBId with all.

;WITH cte_usrId(UserId)
 AS (SELECT UserId
     FROM   TableB)
SELECT *
FROM   TableA
WHERE  userAId IN (SELECT UserId
                   FROM   cte_usrId)
       AND UserBId IN (SELECT UserId
                       FROM   cte_usrId)

score 0 · Answer 5 · answered Oct 18 '14 at 05:33

0

Try this

SELECT *
FROM   TableA a
WHERE  EXISTS (SELECT 1
               FROM   table b
               WHERE  a.userAId = b.userid
                      AND a.UserBId = b.userid)

answered Oct 18 '14 at 05:33

Pரதீப்

91,748
19
131
172

select from subquery two times

5 Answers5