Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Help With WHERE Statement Expand / Collapse
Author
Message
Posted Sunday, March 30, 2014 10:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:48 PM
Points: 147, Visits: 201
Okay friends, I'm having a hard time with this query. I have two tables, Inspections and InspectionsContacts. This is a one to many relationship with INSPNO being the joining key. Here is my query:

SELECT IS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAME
FROM INSPECTION IS
INNER JOIN INSPECTIONCONTACT CNT
ON IS.INSPNO = CNT.INSPNO
WHERE CNT.CAPACITY <> 'Contractor'

Basically, I only want to pull contacts that do not have a capacity of contractors, which is easy; however, where it gets difficult is that if there isn't a contact that isn't a contractor, then I want to pull the FirstName and LastName of the contact with the capacity of contractor. That's where I'm having difficulty. Anyone that could help me would be greatly appreciated!!!

Thank you!

Jordon
Post #1556323
Posted Sunday, March 30, 2014 10:58 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 1,919, Visits: 5,288
jordon.shaw (3/30/2014)
SELECT IS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAME
FROM INSPECTION IS
INNER JOIN INSPECTIONCONTACT CNT
ON IS.INSPNO = CNT.INSPNO
WHERE CNT.CAPACITY <> 'Contractor'


Quick note, be careful not to use reserved keywords in this manner, that is the alias for INSPECTION being IS.

SELECT INS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAME
FROM INSPECTION INS
INNER JOIN INSPECTIONCONTACT CNT
ON INS.INSPNO = CNT.INSPNO
WHERE CNT.CAPACITY <> 'Contractor'

Post #1556327
Posted Sunday, March 30, 2014 11:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:48 PM
Points: 147, Visits: 201

Quick note, be careful not to use reserved keywords in this manner, that is the alias for INSPECTION being IS.

SELECT INS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAME
FROM INSPECTION INS
INNER JOIN INSPECTIONCONTACT CNT
ON INS.INSPNO = CNT.INSPNO
WHERE CNT.CAPACITY <> 'Contractor'



Thanks for the tip; however, I'm actually using completely different alias all together. I just tried to simplify this to make it easy for y'all to help. With that being said, any suggestions on how to accomplish what I'm trying to accomplish?

Thanks,
Jordon
Post #1556328
Posted Sunday, March 30, 2014 11:10 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 1,919, Visits: 5,288
Could you supply a DDL with a sample records and the expected results?
Post #1556329
Posted Sunday, March 30, 2014 11:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:48 PM
Points: 147, Visits: 201
Eirikur Eiriksson (3/30/2014)
Could you supply a DDL with a sample records and the expected results?


I'm not on my DB server right now; however, it's a simple setup. I have two tables, Inspection and InspectionContact. The inspection table data will look like this:

INSPNO StartDate CompDate Status
123 3/30/14 3/30/14 Passed
124 3/31/14 3/31/14 Passed

Then my InspectionContact table would look like this:

ConactNo INSPNO FirstName LastName PhoneNumber Capacity
1000 123 John Doe 555-555-5555 Owner
1001 123 Jane Doe 555-555-5555 Owner
1002 123 Jane Handy 555-555-5555 Contractor
1003 124 Fred Smith 555-555-5555 Contractor

So with my current query, I would only get John Doe and Jane Doe with Inspno 123; however, since Inspno 124 doesn't have any Contact other than the contractor, I also want that one to show up with the contractor. So my desired results would look like this:

INSPNO FIRSTNAME LASTNAME
123 John Doe
123 Jane Doe
124 Fred Smith

You see that Jane Handy doesn't show up because there is a contact on the inspection that isn't a contractor, whereas Fred Smith does show up because he's the only contact on the inspection, even though he's a contractor.

Make sense?
Post #1556331
Posted Monday, March 31, 2014 1:42 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 1,919, Visits: 5,288
This is a case of premature exclusion, nothing to worry about

USE tempdb;
GO
DECLARE @INSPECTION TABLE
(
INSPNO INT NULL
,StartDate DATE NULL
,CompDate DATE NULL
,Status NVARCHAR(50) NULL
);
INSERT INTO @INSPECTION(INSPNO,StartDate,CompDate,Status)
VALUES
(123, '3/30/14', '3/30/14', 'Passed')
,(124, '3/31/14', '3/31/14', 'Passed');

DECLARE @INSPECTIONCONTACT TABLE
(
ConactNo INT NULL
,INSPNO INT NULL
,FirstName NVARCHAR(50)
,LastName NVARCHAR(50)
,PhoneNumber NVARCHAR(50)
,Capacity NVARCHAR(50)
);

INSERT INTO @INSPECTIONCONTACT
(
ConactNo
,INSPNO
,FirstName
,LastName
,PhoneNumber
,Capacity
)
VALUES
(1000, 123, 'John', 'Doe', '555-555-5555', 'Owner')
,(1001, 123, 'Jane', 'Doe', '555-555-5555', 'Owner')
,(1002, 123, 'Jane', 'Handy', '555-555-5555', 'Contractor')
,(1003, 124, 'Fred', 'Smith', '555-555-5555', 'Contractor');

SELECT
X.INSPNO
,X.FIRSTNAME
,X.LASTNAME
FROM
(
SELECT
INS.INSPNO
,ISC.FIRSTNAME
,ISC.LASTNAME
,CASE
WHEN SUM
(
CASE
WHEN ISC.Capacity = N'Contractor' THEN 0
ELSE 1
END ) OVER
(
PARTITION BY INS.INSPNO
) > 0 AND ISC.Capacity <> N'Contractor' THEN 1
WHEN SUM
(
CASE
WHEN ISC.Capacity = N'Contractor' THEN 0
ELSE 1
END ) OVER
(
PARTITION BY INS.INSPNO
) = 0 AND ISC.Capacity = N'Contractor' THEN 1
ELSE 0
END AS SHOW_CONT
FROM @INSPECTION INS
INNER JOIN @INSPECTIONCONTACT ISC
ON INS.INSPNO = ISC.INSPNO
) AS X
WHERE X.SHOW_CONT = 1;

Post #1556363
Posted Monday, March 31, 2014 1:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:48 PM
Points: 147, Visits: 201
Eirikur Eiriksson (3/31/2014)
This is a case of premature exclusion, nothing to worry about


Dang, I hate when that happens

I'll check this out and let you know if I run into any issues. Thank you so much!!!
Post #1556366
Posted Monday, March 31, 2014 7:57 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:03 PM
Points: 3,584, Visits: 8,046
Here are 2 other options.

WITH Contacts AS(
SELECT INS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAME, CNT.CAPACITY,
ROW_NUMBER() OVER( PARTITION BY INS.INSPNO ORDER BY CASE WHEN Capacity = 'Contractor' THEN -1 ELSE INS.INSPNO END DESC) rn
FROM @INSPECTION INS
INNER JOIN @INSPECTIONCONTACT CNT
ON INS.INSPNO = CNT.INSPNO
)
SELECT INSPNO, FIRSTNAME, LASTNAME
FROM Contacts
WHERE CAPACITY <> 'Contractor'
OR rn = 1;

SELECT INS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAME
FROM @INSPECTION INS
INNER JOIN @INSPECTIONCONTACT CNT
ON INS.INSPNO = CNT.INSPNO
WHERE CNT.CAPACITY <> 'Contractor'
UNION ALL
SELECT INS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAME
FROM @INSPECTION INS
INNER JOIN @INSPECTIONCONTACT CNT
ON INS.INSPNO = CNT.INSPNO
WHERE INS.INSPNO IN ( SELECT C.INSPNO FROM @INSPECTIONCONTACT C
GROUP BY C.INSPNO
HAVING MAX(C.CAPACITY) = 'Contractor'
AND MIN(C.CAPACITY) = 'Contractor')




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1556477
Posted Monday, March 31, 2014 10:20 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 1,919, Visits: 5,288
Luis Cazares (3/31/2014)
Here are 2 other options.

WITH Contacts AS(
SELECT INS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAME, CNT.CAPACITY,
ROW_NUMBER() OVER( PARTITION BY INS.INSPNO ORDER BY CASE WHEN Capacity = 'Contractor' THEN -1 ELSE INS.INSPNO END DESC) rn
FROM @INSPECTION INS
INNER JOIN @INSPECTIONCONTACT CNT
ON INS.INSPNO = CNT.INSPNO
)
SELECT INSPNO, FIRSTNAME, LASTNAME
FROM Contacts
WHERE CAPACITY <> 'Contractor'
OR rn = 1;



Short, sweet and about the same cost as my code, respect!
Post #1556580
Posted Monday, March 31, 2014 10:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:03 PM
Points: 3,584, Visits: 8,046
Eirikur Eiriksson (3/31/2014)
Luis Cazares (3/31/2014)
Here are 2 other options.

WITH Contacts AS(
SELECT INS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAME, CNT.CAPACITY,
ROW_NUMBER() OVER( PARTITION BY INS.INSPNO ORDER BY CASE WHEN Capacity = 'Contractor' THEN -1 ELSE INS.INSPNO END DESC) rn
FROM @INSPECTION INS
INNER JOIN @INSPECTIONCONTACT CNT
ON INS.INSPNO = CNT.INSPNO
)
SELECT INSPNO, FIRSTNAME, LASTNAME
FROM Contacts
WHERE CAPACITY <> 'Contractor'
OR rn = 1;



Short, sweet and about the same cost as my code, respect!

It might slow down if there are too many contractors. But it's worth the try.

The important thing is that the OP tests under the real conditions.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1556598
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse