Solved

Query help!

Posted on 2009-05-11
6
180 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
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.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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