TSQL Correlated sub query question

Hi Experts,

I'm having trouble writing a sub query to retrieve data from two tables. Here are my tables:

tableA
********
processid              name                            
========            =========
12211                  junior high athletics - jump rope
12211                  middle school ath - relay
12211                  high school ath - relay
12211                  elementary school ath - 100m
12211                  middle school ath - 200m
13243                   middle school ath - 200m
13243                   middle school ath - 100m
13243                   middle school ath - relay

tableB
********
processid              name                                                                  medal          
========            =========                                                          ==========
12211                  junior high athletics - jump rope                           gold
12211                  middle school ath - relay                                      gold
12211                  high school ath - relay                                         null
12211                  elementary school ath - 100m                             silver
12211                  middle school ath - 200m                                     bronze
13243                   middle school ath - 200m                                    null
13243                   middle school ath - 100m                                    null
13243                   middle school ath - relay

I need to retrieve all the rows from tableA with processid 12211 that have "gold" in the medal column. This is my query and it's giving me the wrong results:

select tb1.processid, tb1.name
from tableA tb1
where tb1.processid in (select tb2.processid from tableB tb2 where tb2.processid = tb1.processid and tb2.medal = N'gold')
LVL 1
roger vAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Patrick MatthewsConnect With a Mentor Commented:
SELECT a.processid, a.name
FROM tableA a INNER JOIN
    tableB b ON a.processid = b.processid AND a.name = b.name
WHERE b.processid = 12211 AND b.name = N'gold'

UNION

SELECT b.processid, b.name
FROM tableB b
WHERE b.processid = 12211 AND b.name = N'gold'

Open in new window

0
 
Patrick MatthewsCommented:
SELECT a.processid, a.name
FROM tableA a INNER JOIN
    tableB b ON a.processid = b.processid AND a.name = b.name
WHERE b.processid = 12211 AND b.name = N'gold'

Open in new window


If you need distinct rows:

SELECT a.processid, a.name
FROM tableA a INNER JOIN
    tableB b ON a.processid = b.processid AND a.name = b.name
WHERE b.processid = 12211 AND b.name = N'gold'
GROUP BY a.processid, a.name

Open in new window

0
 
effesCommented:
Hi,

try using this statement

select tb1.processid, tb1.name
from tableA tb1
where tb1.processid in (select tb2.processid from tableB tb2 where tb2.medal = N'gold')

Hope that helps,
Frank

0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
akku101Commented:
select * from
TableA A
Inner Join TableB B on (a.processid = b.processid and a.name =b.name)
where b.medal='gold' and a.processid =12211
0
 
roger vAuthor Commented:
@mathewpatrick:

The problem with your inner join is that tableA could sometimes be empty. Sorry I didn't explain this but sometimes tableA will be empty. In order to account for this, I need to always get all the rows that are in tableB plus any additional rows that are in tableA for medal = N'gold' and processid = 12211
0
 
Patrick MatthewsCommented:
That's not what your question says:

>>I need to retrieve all the rows from tableA with processid 12211 that have "gold" in the medal column.

Please take a step back, and do the following:

1) Take another shot at explaining what you need.  Please be very explicit

2) Based on the sample data from your question, please list exactly what output you would expect.
0
 
roger vAuthor Commented:
@mattewspatrick:

OK, so here's the exact output that I need:

If tableA is not empty then:
********************************

All the rows from tableA with processid of 12211 and medal = N'gold'
Plus any additional rows from tableB with processid of 12211 and medal = N'gold'

If tableB is empty then:
****************************

All the rows from tableB with processid of 12211 and medal = N'gold'
0
All Courses

From novice to tech pro — start learning today.