[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Query help!

Posted on 2009-05-11
6
Medium Priority
?
187 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
  • 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 expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 

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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

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 article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

612 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