Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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')
0
roger v
Asked:
roger v
1 Solution
 
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
 
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now