union distinct to a 2 fields - 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

SQL Server 2000 Forums

Transact-SQL (2000)

union distinct to a 2 fields

Author

Topic

liquid1mike
Starting Member

1 Post

Posted - 2009-07-21 : 16:34:02

This script deals with 2 tables, customers and leads.
The PK cst_id on the customers table is the FK on the leads table. Customers can have multiple leads.
I want to pull cst_cust, cst_id, and ld_id (if it has a lead), whenever there is a duplicate item on cst_cust. Right now it is given me one extra of each customer that has a lead. example below:

Currently I am getting:
cst_cust cst_id ld_id
Doe, John 58 77
Doe, John 58 NULL
Doe, John 79 NULL

What I want:
cst_cust cst_id ld_id
Doe, John 58 77
Doe, John 79 NULL

The only time I want the cst_id to be repeated is if a single customer has multiple leads on it.

The script im currently using now is below:

SELECT c.cst_cust, c.cst_id, l.ld_id
FROM customers c, leads l
where c.cst_id = l.cst_id AND c.cst_cust IN (select cst_cust from customers group by cst_cust HAVING (COUNT(cst_cust) > 1))
UNION
SELECT c.cst_cust, c.cst_id, NULL
from customers c, leads l
where c.cst_id <> l.cst_id AND c.cst_cust IN (select cst_cust from customers group by cst_cust HAVING (COUNT(cst_cust) > 1))
order by c.cst_cust, c.cst_id

Thanks,
Mike

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-21 : 19:48:41

[code]
SELECT c.cst_cust, c.cst_id, l.ld_id
FROM customers c
INNER JOIN leads l ON c.cst_id = l.cst_id
INNER JOIN
(
SELECT cst_cust
FROM customers
GROUP BY cst_cust
HAVING COUNT(*) > 1
) m ON c.cst_cust = m.cst_cust
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources