Solved

Query help!

Posted on 2009-05-11
6
182 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

734 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