query to get a subset - 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 2012 Forums

Transact-SQL (2012)

query to get a subset

Author

Topic

lmayer4
Starting Member

33 Posts

Posted - 2013-12-30 : 11:17:32

Morning,

I am having a total brain cramp this morning. I am trying to write a query that says, show me all the fiber customers who do not have at least one outage contact assigned. The ContactTypeID in this case is '1007','1008','1009' for Outage Contact #1,#2 and #3. Fiver is defined as anything like SFC. This is what I have so far:


select distinct b.CustomerAcctNumber,b.CustID,b.CustomerName,t.Description OutageContact,c.FirstName,c.lastName, c.Email1, 
c.phone1, c.phone2,c.hoursofaccess 
from infodir.CustomerBase b
	inner join infodir.UsgSvc u
		on b.custid = u.custid
	left join infodir.CustomerContactLink l
		on b.custid = l.custid
	left join infodir.Contact c
		on l.ContactID = c.ContactID
		and c.ContactTypeID in ('1007','1008','1009')
	left join infodir.ContactType t
		on c.ContactTypeID = t.ContactTypeID
where ServiceNumber like '%SFC%'

Any thoughts would be great!

Thanks

Laura

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-31 : 10:15:57

can you show some sample data and then explain what you're looking at as output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

lmayer4
Starting Member

33 Posts

Posted - 2014-01-02 : 13:36:53

I had to rewrite it to pull more data in. Thank you for checking in.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-01-02 : 18:46:13

I can't know if this works without schema and sample data but...[CODE]select distinct b.CustomerAcctNumber,b.CustID,b.CustomerName,c.FirstName,c.lastName, c.Email1,
c.phone1, c.phone2,c.hoursofaccess
from infodir.CustomerBase b
inner join infodir.UsgSvc u
on b.custid = u.custid
where ServiceNumber like '%SFC%'
and not exists (
select *
from infodir.CustomerContactLink l
inner join infodir.Contact c
on l.ContactID = c.ContactID
and c.ContactTypeID in ('1007','1008','1009')
where
b.custid = l.custid
)[/CODE]Also, I don't see how the t.Description could be anything but NULL since we are picking the ones that don't have a contact but I could (easily) be overlooking something.

=================================================
No, no, you're not thinking, you're just being logical. -Niels Bohr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources