Stumped on Operators

[skrause]

Folks:

I have a string which looks like:

SELECT DISTINCT QUOTE_NO FROM QUOTE_NEW,CONTACT,GENERAL WHERE QUOTE_PRIMARY=CONT_ID AND QUOTE_PRODUCT_TYPE='ACT' AND PROCESS_TYPE IN(n1,n2,n3,n4)

** the n's are integers

The string works as desired if n1 through n4 does not exist in the GENERAL table.

My problem is that if any of n1 through n4 exists in a single record, the entire table is returned, instead of that particular records that have n1,n2,n3,n4, etc.

Any suggestions?

Thanks,
Steve

 

[tlbroadbent]

The problem is that you have included three tables in your query with NO join criteria. Thus you get a cartesian product of the three tables. Every row on each table matched to every row on the other two. Therefore, every Quote_No will match your selection criteria.

Only include tables required in the From clause. Make sure you indicate the relationship between the tables. I also recommend using Group By rather than Distinct. I've read articles indicating Group By is more efficient.

SELECT QUOTE_NO
FROM QUOTE_NEW
INNER JOIN CONTACT
ON <enter join criteria>
INNER JOIN GENERAL
ON <enter join criteria>
WHERE QUOTE_PRIMARY=CONT_ID
AND QUOTE_PRODUCT_TYPE='ACT'
AND PROCESS_TYPE IN (n1,n2,n3,n4)
GROUP BY QUOTE_NO Terry L. Broadbent - DBA
Computing Links:

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

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[skrause]

Terry:

Your suggestion was spot on! Thanks for the help.

Steve