?
Solved

Query help!

Posted on 2009-05-11
6
Medium Priority
?
184 Views
Last Modified: 2012-05-06
Not sure which type of join to use to add another table to my attached query....
I'd tried to add another table with a left join and received way too many records.  Currently this query returns 169 records, I'd like to add another table and still have 169 records.  I tried this and got a ton of records

left outer join TmsEprd.dbo.ad_org_tracking ad on ad.id_num = st.id_num

Thanks for any guidance
SELECT 
'0'+ cd.high_school
 
from  TmsEprd.dbo.stud_term_sum_div st
left outer join TmsEprd.dbo.candidate cd
       on st.id_num = cd.id_num
 
where     
	  (st.div_cde = 'UG') and
      (st.yr_cde = '0809') and
      (st.trm_cde = 'SP')and
      (st.num_of_crs > 0)

Open in new window

0
Comment
Question by:jasonbrandt3
[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
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24358095
what about:
SELECT 
'0'+ cd.high_school
 
from  TmsEprd.dbo.stud_term_sum_div st
 
where     
        (st.div_cde = 'UG') and
      (st.yr_cde = '0809') and
      (st.trm_cde = 'SP')and
      (st.num_of_crs > 0)
 and exists ( select null from TmsEprd.dbo.candidate cd WHERE st.id_num = cd.id_num )

Open in new window

0
 

Author Comment

by:jasonbrandt3
ID: 24358165
I'm not having problems with the join in my code:  I tried to add this in addition to what's in my code:


left outer join TmsEprd.dbo.ad_org_tracking ad on ad.id_num = st.id_num

Now I have 255 records instead of 169.  Does that make sense?  I want to add another table (ad_org_tracking) and still have 169 records.  If I use the join above I get 255.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24358352
>Now I have 255 records instead of 169.  Does that make sense?  
yes, you have multiple records in TmsEprd.dbo.ad_org_tracking  per id_num value ...

anyhow, you see how I modified the JOIN into EXISTS.
do the same with that other table
0
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.

 

Author Comment

by:jasonbrandt3
ID: 24358400
I'll give it a try..
0
 

Author Comment

by:jasonbrandt3
ID: 24358420
Is this what you meant?  I only get 81 rows..

SELECT distinct
'0'+ cd.high_school

from  ad_org_tracking ad, TmsEprd.dbo.stud_term_sum_div st
      left outer join TmsEprd.dbo.candidate cd
    on st.id_num = cd.id_num

where
     
        (st.div_cde = 'UG') and
      (st.yr_cde = '0809') and
      (st.trm_cde = 'SP')and
      (st.num_of_crs > 0)
and exists ( select null from TmsEprd.dbo.ad_org_tracking cd WHERE st.id_num = ad.id_num )
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 24358492
>Is this what you meant?  
yes

>I only get 81 rows..
that's what the data is.
please double-check
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

719 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