WHERE statement woes. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

WHERE statement woes.

Author

Topic

DaveyB
Starting Member

10 Posts

Posted - 2008-05-28 : 16:02:14

SELECT T1.*
FROM Cust_Table T1
INNER JOIN
(
SELECT Family_Name
FROM Cust_Table
WHERE Cust_Name IN ('Billy', 'John')
AND RowNum < 100
GROUP BY Family_Name
HAVING COUNT(*) > 1
)
T2 ON T1.Family_Name= T2.Family_Name
WHERE RowNum < 100

( This code above finds all the familys that contain either multiple billys and/or multiple Johns and displays all the duplicates ordered by the family_name. )

The problem is that what I want it to do is search through the whole table and find within each Family (Family_ID) who has both a sibling called Billy AND John (Cust_Name) wether they have multiple johns and multiple billys I don't mind as long as they have a minimum of 1 of each. I then want to just output all those examples only not anything else. An example of the table is bellow, I hope this helps. Thanks for your advice.

BEFORE
Family_Name CUST_Name
Bruce Billy
Bruce John
Bruce Mike
Bruce Oli
Smith Billy
Smith Billy
Harold John

AFTER
Family_Name CUST_Name
Bruce Billy
Bruce John

nr
SQLTeam MVY

12543 Posts

Posted - 2008-05-28 : 16:18:16

SELECT T1.*
FROM Cust_Table T1
INNER JOIN
(
SELECT Family_Name
FROM Cust_Table
WHERE Cust_Name IN ('Billy', 'John')
GROUP BY Family_Name
HAVING COUNT(distinct Cust_Name) = 2
)
T2 ON T1.Family_Name= T2.Family_Name

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DaveyB
Starting Member

10 Posts

Posted - 2008-05-29 : 17:02:05

quote:

SELECT T1.*
FROM Cust_Table T1
INNER JOIN
(
SELECT Family_Name
FROM Cust_Table
WHERE Cust_Name IN ('Billy', 'John')
GROUP BY Family_Name
HAVING COUNT(distinct Cust_Name) = 2
)
T2 ON T1.Family_Name= T2.Family_Name

That returns:
Bruce Billy
Bruce John
Bruce Mike
Bruce Oli

When I wanted it to return Only.
Family_Name CUST_Name
Bruce Billy
Bruce John

Any ideas what I need to do, the only thing I can think of doing is adding another WHERE clause like this.
[CODE]
SELECT T1.*
FROM Cust_Table T1
INNER JOIN
(
SELECT Family_Name
FROM Cust_Table
WHERE Cust_Name IN ('Billy', 'John')
GROUP BY Family_Name
HAVING COUNT(distinct Cust_Name) = 2
)
T2 ON T1.Family_Name= T2.Family_Name
WHERE Cust_Name IN ('Billy', 'John')
[/CODE]

But then I lose the functionality of GROUP BY. Any ideas?

gavakie
Posting Yak Master

221 Posts

Posted - 2008-05-29 : 17:13:52

Have you tried
Where Cust_Name like ('%Billy%')
OR Cust_Name like ('%John%')

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-30 : 14:12:13

quote:

But then I lose the functionality of GROUP BY. Any ideas?

What do you mean by that? What is wrong with the results when adding the WHERE clause -- doesn't that return exactly what you want?

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources