Record in 1 table but not 2

[debbie1212]

I'm trying to set this up so that it only gets those that have a REF_EMP_REC_DATE but it's returning a record that is in the CAC table but not the Referrals table. Therefore it's putting a NULL in the REFERRALS.REF_EMP_REC_DATE. I don't want it to return a record if there is not a record in Referrals or if REFERRALS.REF_EMP_REC_DATE is NULL.

This is my code:

CREATE Procedure sto_GetCases
@cac_chd_id int
AS
SELECT CAC.CAC_ID, REFERRALS.REF_EMP_REC_DATE,
CAC.CAC_CASE_NUMBER FROM CAC,REFERRALS
WHERE CAC.CAC_ID*=REFERRALS.REF_CAC_ID
AND CAC_CHD_ID = @cac_chd_id
AND REFERRALS.REF_EMP_REC_DATE > '1/1/1950'
ORDER BY CAC_ID

Thanks,
Debbie

 

[tlbroadbent]

Use an INNER JOIN rather than an OUTER JOIN. Remove the * before the = in the Where clause.

SELECT
CAC.CAC_ID,
REFERRALS.REF_EMP_REC_DATE,
CAC.CAC_CASE_NUMBER
FROM CAC, REFERRALS
WHERE CAC.CAC_ID=REFERRALS.REF_CAC_ID
AND CAC_CHD_ID = @cac_chd_id
AND REFERRALS.REF_EMP_REC_DATE > '1/1/1950'
ORDER BY CAC_ID

As always, I recommend using ANSI styandard syntax for the JOIN. The old style JOIN syntax may be eliminated in future versions of SQL Server. Why? See the following link.

http://www.microsoft.com/sql/techinfo/tips/development/July23.asp

ANSI JOIN syntax:
SELECT
CAC.CAC_ID,
REFERRALS.REF_EMP_REC_DATE,
CAC.CAC_CASE_NUMBER
FROM CAC
INNER JOIN REFERRALS
ON CAC.CAC_ID=REFERRALS.REF_CAC_ID
WHERE CAC_CHD_ID = @cac_chd_id
AND REFERRALS.REF_EMP_REC_DATE > '1/1/1950'
ORDER BY CAC_ID Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[debbie1212]

Thank you so much. I don't know much about join's. Someone else wrote this code and I'm just modifying it.

I will give it a try.

Thanks again,
Debbie

 

[SQLSister]

I've also read that those old style joins can give inconsistent results in some cases. Best to follow Terry's suggestion and use the ANSI Join syntax.

If you are going to be doing much T-SQL programming, you really need to get up-to-speed on joins. Read about them in
Book on line and any books you might have. Then go to Enterprise Manager and use the query designer to play around with them and see what the different types of joins will give you as results. You rally need to know deep in your bones exactly what data will be returned from what kind of join in order to effectively get the right data out of a database. In the last 20+ years, I've dealt with hundreds of databases and have seen an amazing amount of disinformation used to make decisions in organizations because their database queries weren't pulling up the correct information. And the mistake was often because the programmer did not use the right kind of table join.

 

[debbie1212]

Thank you so much. I intend to learn as much as possible about joins.

Thanks again,
Debbie

 

[debbie1212]

Thanks tlbroadbent. It worked!!!!!