SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help With WHERE Statement


Help With WHERE Statement

Author
Message
jordon.shaw
jordon.shaw
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 224
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!

JordonDoze
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39006 Visits: 19430
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'



Cool
jordon.shaw
jordon.shaw
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 224

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'



Cool


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
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39006 Visits: 19430
Could you supply a DDL with a sample records and the expected results?
jordon.shaw
jordon.shaw
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 224
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?
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39006 Visits: 19430
This is a case of premature exclusion, nothing to worry about :-D

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;


jordon.shaw
jordon.shaw
Right there with Babe
Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)Right there with Babe (728 reputation)

Group: General Forum Members
Points: 728 Visits: 224
Eirikur Eiriksson (3/31/2014)
This is a case of premature exclusion, nothing to worry about :-D


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!!!
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40328 Visits: 19806
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39006 Visits: 19430
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! :-)
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40328 Visits: 19806
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search