Solved

TSQL Correlated sub query question

Posted on 2011-09-15
7
240 Views
Last Modified: 2012-05-12
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')
0
Comment
Question by:roger_v
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36545267
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
 
LVL 6

Expert Comment

by:effes
ID: 36545269
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
 
LVL 2

Expert Comment

by:akku101
ID: 36545406
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:roger_v
ID: 36545582
@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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36545783
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
 
LVL 1

Author Comment

by:roger_v
ID: 36545961
@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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36546212
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question