Solved

Query help!

Posted on 2009-05-11
6
183 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

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

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.

Question has a verified solution.

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

628 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