asked on

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')

Patrick Matthews

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

effes

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

akku101

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

roger v

ASKER

@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

Patrick Matthews

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.

roger v

ASKER

@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'

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial