?
Solved

TSQL Correlated sub query question

Posted on 2011-09-15
7
Medium Priority
?
252 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 93

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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 93

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

579 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