Filter by in-query results - 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

Filter by in-query results

Author

Topic

blund
Starting Member

23 Posts

Posted - 2012-01-05 : 16:25:27

I have a database with tables: customer, address, and contact.
Each customer has 2 address types: Del and Inv (which are fields in the address table
Each address type has a contact name linked to it.
I can query the customers and list the customer with the address type and contact name via joins, so that I get 2 rows for every customer, as shown:

Customer A : Del : John Doe
Customer A : Inv : John Doe
Customer B : Del : Bill Smith
Customer B : Inv : Bill Smith

What I would like to do, is show just the customers that have a different contact name for the 2 different address types, as shown:

Customer C : Del : Mary Anderson
Customer C : Inv : Sara Jones

Is that possible in side of the single query or do I need to create and save multiple queries/views?
If possible,either way, could I get a sample of the code?

Please let me know if more information is needed,
Thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-05 : 17:40:32

[code]WITH cte AS
(
SELECT * FROM yourOriginalQuery
)
SELECT * FROM cte c1
WHERE EXISTS
(
SELECT * FROM cte c2 WHERE c2.customer = c1.customer AND c2.contact <> c1.contact
);
[/code]

pduffin
Yak Posting Veteran

68 Posts

Posted - 2012-01-05 : 17:43:31

Not the best but it will work for you.

create table #customer (Custid char(1),name char(30))
create table #address(Custid char(1),AddressId int,type char(3),Address char(100),contactId int)
create table #contact(ContactId int, Contact char(30))

insert into #customer values('a','bob')
insert into #customer values('b','brian')

insert into #address values('a',1,'del','address a',1)
insert into #address values('a',2,'inv','address a',1)
insert into #address values('b',3,'del','address a',2)
insert into #address values('b',4,'inv','address b',3)

insert into #contact values(1,'james')
insert into #contact values(2,'dave')
insert into #contact values(3,'thomas')

select a.custid,b.type,c.contact
from #customer a
join #address b on a.custid = b.custid
join #contact c on c.contactid = b.contactid

select *
from #tmp
where custid in
(
select t.custid
from #tmp t
join #tmp t2 on t2.custid = t.custid and t2.type = 'del'
where t.type = 'inv' and t.contact <> t2.contact
)

Likes to run, hates the runs!

pduffin
Yak Posting Veteran

68 Posts

Posted - 2012-01-05 : 17:45:12

Nice one sunitabeck! Much better than my sql 2000 solution!

Likes to run, hates the runs!

blund
Starting Member

23 Posts

Posted - 2012-01-05 : 18:23:30

Thank you both.
So far though, I am only at "single level query" level in the SQL world...

How do I incorparate my "original query" into your suggested code?
Also, are "cte", "c1", and "c2" query specific variables, or do I need to change them to match output from my query?

If it helps, this is my query which I saved as a file on my pc:

SELECT cicmpy.cmp_name AS customer, cicntp.FullName AS contact, Addresses.Type
FROM Addresses INNER JOIN
cicntp ON Addresses.ContactPerson = cicntp.cnt_id INNER JOIN
cicmpy ON Addresses.Account = cicmpy.cmp_wwn
WHERE (Addresses.Type = N'inv') OR
(Addresses.Type = N'del')

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-05 : 20:00:29

Thanks, pduffin!

CTE is just a name for the virtual table (but I picked that name because the virtual table is generated by a Common Table Expression. http://msdn.microsoft.com/en-us/library/ms190766.aspx )

c1 and c2 are table aliases: http://msdn.microsoft.com/en-us/library/ms187455.aspx

To incorporate your query into what I had posted earlier, simply copy it between the brackets as in:

WITH cte AS
(
   -- Your original query-----
   SELECT cicmpy.cmp_name AS customer, cicntp.FullName AS contact, Addresses.Type
   FROM Addresses INNER JOIN
   cicntp ON Addresses.ContactPerson = cicntp.cnt_id INNER JOIN
   cicmpy ON Addresses.Account = cicmpy.cmp_wwn
   WHERE (Addresses.Type = N'inv') OR
   (Addresses.Type = N'del')
   ---------------------------
)
SELECT * FROM cte c1
WHERE EXISTS
(
	SELECT * FROM cte c2 WHERE c2.customer = c1.customer AND c2.contact <> c1.contact
);

blund
Starting Member

23 Posts

Posted - 2012-01-06 : 09:36:46

Thank you sunitabeck.
Thanks both for the working structure and the bonus information!

I think I am slowly figuring out the basics of SQL, I don't get to use it that often.
Now I am beginning to get some opportunities to combine the basics into more valuable tools, and your extra details sure will help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-06 : 10:17:13

You are quite welcome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources