Need a SQL Query

Question

Need a SQL Query

vaibhav.tiwari

SSCommitted

Points: 1976
More actions
June 25, 2012 at 2:12 am

#258037

Hi everyone,
I have table named ENCOUNTER_PARTY
CREATE TABLE ENCOUNTER_PARTY( ENCOUNTER_KEY INT, PARTY_KEY INT, PARTY_ROLE_TYPE_KEY INT)
INSERT INTO ENCOUNTER_PARTY
SELECT 101, 11, 3 UNION ALL
SELECT 101, 12, 13 UNION ALL
SELECT 101, 13, 4 UNION ALL
SELECT 102, 21, 3 UNION ALL
SELECT 102, 22, 13 UNION ALL
SELECT 102, 23, 2 UNION ALL
SELECT 102, 31, 3 UNION ALL
SELECT 102, 32, 13 UNION ALL
SELECT 102, 33, 5
I have to select list of encounter for which PARTY_ROLE_TYPE_KEY = 2 is not available
please provide me an sql query for this.
Thanks.
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113499 More actions · Answer 1

Are you familiar with the WHERE clause?

I would start by looking this up and then posting the query you have written and then if still stuck providing the expected outcome from that sample data you have provided.

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 2

Bit change in sample data.

CREATE TABLE ENCOUNTER_PARTY( ENCOUNTER_KEY INT, PARTY_KEY INT, PARTY_ROLE_TYPE_KEY INT)

INSERT INTO ENCOUNTER_PARTY

SELECT 101, 11, 3 UNION ALL

SELECT 101, 12, 13 UNION ALL

SELECT 101, 13, 4 UNION ALL

SELECT 102, 21, 3 UNION ALL

SELECT 102, 22, 13 UNION ALL

SELECT 102, 23, 2 UNION ALL

SELECT 103, 31, 3 UNION ALL

SELECT 103, 32, 13 UNION ALL

SELECT 103, 33, 5

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 3

Expected output is 101 and 103 because those encounters dont have party_role_type_key = 2

But how to select with query?

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Ant-Green SSC Guru Points: 113499 More actions · Answer 4

Use a where clause, http://msdn.microsoft.com/en-US/library/ms188047%28v=sql.105%29

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 5

I cant use where clause directly because if will say

SELECT * FROM ENCOUNTER_PARTY WHERE PARTY_ROLE_TYPE_KEY NOT IN (2)

It will give me all the rows but except with PARTY_ROLE_TYPE_KEY NOT = 2

But I need only those encounter for those there is no now with PARTY_ROLE_TYPE_KEY NOT = 2

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Ant-Green SSC Guru Points: 113499 More actions · Answer 6

Ehat do you want to see, just EncounterKey or every column in the table where there is no encounterkey which has a type key of 2

So Just 1 column with 101, 103 or 3 columns like this

101, 11, 3

101, 12, 13

101, 13, 4

103, 31, 3

103, 32, 13

103, 33, 5

For some additional reading, please follow the second link in my signature on posting code data and expected outcomes.

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 7

Just one column 101 and 103.

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Ant-Green SSC Guru Points: 113499 More actions · Answer 8

Then just select that 1 column instead of using * then add in either a distinct or a group by clause with a subquery to filter out 102.

carthik SSC Veteran Points: 273 More actions · Answer 9

Try this....

Select Distinct ENCOUNTER_KEY from #ENCOUNTER_PARTY where ENCOUNTER_KEY not in

(Select ENCOUNTER_KEY from #ENCOUNTER_PARTY where PARTY_ROLE_TYPE_KEY = 2)

Regards,

Carthik

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 10

Sample Data

CREATE TABLE ENCOUNTER_PARTY( ENCOUNTER_KEY INT, PARTY_KEY INT, PARTY_ROLE_TYPE_KEY INT)

INSERT INTO ENCOUNTER_PARTY

SELECT '3','13855','2' UNION ALL

SELECT '3','103192','13' UNION ALL

SELECT '3','110986','3' UNION ALL

SELECT '4','103192','13' UNION ALL

SELECT '4','112112','3' UNION ALL

SELECT '6','13855','2' UNION ALL

SELECT '6','103192','13' UNION ALL

SELECT '6','116211','3' UNION ALL

SELECT '7','103192','13' UNION ALL

SELECT '7','111951','3' UNION ALL

SELECT '8','13855','2' UNION ALL

SELECT '8','103192','13' UNION ALL

SELECT '8','111952','3' UNION ALL

SELECT '9','103192','13' UNION ALL

SELECT '9','111470','3' UNION ALL

SELECT '10','13855','2' UNION ALL

SELECT '10','103192','13' UNION ALL

SELECT '10','111471','3'

Expected Output

410319213

41121123

710319213

71119513

910319213

91114703

Logic : Because Encounter_key 4,7,9 is not have PARTY_ROLE_TYPE_KEY = 2 in their group.

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Ant-Green SSC Guru Points: 113499 More actions · Answer 11

carthik has provided you with a solution, just change it to suit your needs

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 12

Thanks it works...

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together