Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

TSQL Correlated sub query question

Posted on 2011-09-15
7
Medium Priority
?
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

705 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