March 30, 2014 at 10:32 pm
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:doze:
March 30, 2014 at 10:58 pm
jordon.shaw (3/30/2014)
SELECT IS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAMEFROM 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'
😎
March 30, 2014 at 11:03 pm
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.LASTNAMEFROM 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
March 30, 2014 at 11:10 pm
Could you supply a DDL with a sample records and the expected results?
March 30, 2014 at 11:27 pm
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:
INSPNOStartDateCompDateStatus
123 3/30/143/30/14Passed
124 3/31/143/31/14Passed
Then my InspectionContact table would look like this:
ConactNoINSPNOFirstNameLastNamePhoneNumberCapacity
1000 123 John Doe 555-555-5555Owner
1001 123 Jane Doe 555-555-5555Owner
1002 123 Jane Handy555-555-5555Contractor
1003 124 Fred Smith555-555-5555Contractor
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:
INSPNOFIRSTNAMELASTNAME
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?
March 31, 2014 at 1:42 am
This is a case of premature exclusion, nothing to worry about 😀
USE tempdb;
GO
DECLARE @inspection TABLE
(
INSPNO INT NULL
,StartDateDATE NULL
,CompDateDATE 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;
March 31, 2014 at 1:50 am
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!!!
March 31, 2014 at 7:57 am
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')
March 31, 2014 at 10:20 am
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! 🙂
March 31, 2014 at 10:54 am
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.
March 31, 2014 at 2:33 pm
Luis Cazares (3/31/2014)
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.
No more than the running total solution I proposed, even better if anything. One always learns something!
March 31, 2014 at 9:49 pm
Thank you all for your help. I'm now good to go!
Thanks,
Jordon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply