sub data in table - 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

General SQL Server Forums

New to SQL Server Programming

sub data in table

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-06 : 11:51:06

Hi all,
ID------Question -----Answer
1--------A-------------ZZ1
1--------B-------------OPD
1--------C-------------WTT

2--------A-------------KFF
2--------B-------------SGG
2--------C-------------HHH

4--------A-------------DGG
4--------B-------------SGG
4--------C-------------HHH
...

7--------A-------------ZZ1
7--------B-------------OPD
7--------C-------------WTT
....
Expected Output:
Select all values which answers are zz1,opd,wtt

1--------A-------------ZZ1
1--------B-------------OPD
1--------C-------------WTT

7--------A-------------ZZ1
7--------B-------------OPD
7--------C-------------WTT
...

999--------A-------------ZZ1
999--------B-------------OPD
999--------C-------------WTT

Thank you very much :)

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-06 : 12:27:47

SELECT * FROM table_name
WHERE Answer IN('zz1','opd','wtt');

Sachin.Nand

2937 Posts

Posted - 2010-03-06 : 12:33:59

[code]
select id from yourtable where val in('zz1','opd','wtt')
group by id
having Count(case (id) when 3 then 1 else 0 end)=3
[/code]

PBUH

Sachin.Nand

2937 Posts

Posted - 2010-03-06 : 12:37:07

quote:
Originally posted by RobertKaucher

SELECT * FROM table_name
WHERE Answer IN('zz1','opd','wtt');

Your solution will work if the OP really has the data the way he has posted.But will not work if distinct id's has atleast one value from 'zz1'or 'opd' or 'wtt'
example


1--------A-------------ZZ1
1--------B-------------OPD
1--------C-------------WTT

2--------A-------------KFF
2--------B-------------SGG
2--------C-------------WTT

PBUH

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-06 : 22:27:21

quote:
Originally posted by Idera

Your solution will work if the OP really has the data the way he has posted.But will not work if distinct id's has atleast one value from 'zz1'or 'opd' or 'wtt'
example

1--------A-------------ZZ1
1--------B-------------OPD
1--------C-------------WTT

2--------A-------------KFF
2--------B-------------SGG
2--------C-------------WTT

PBUH

I'm sorry. I do not follow your correction. Would you be kind enough to explain this to me again? Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 02:06:18

[code]
SELECT t.ID,t.Question,t.Answer
FROM yourtable t
INNER JOIN (SELECT ID
FROM YourTable
GROUP BY ID
HAVING COUNT(DISTINCT CASE WHEN Answer IN ('ZZ1','OPD','WTT') THEN Answer ELSE NULL END) = 3
)t1
ON t.ID=t1.ID
[/code]

one question here is what you want to do if that id has extra records with different answers? do them still have to be included? or is that you need to include only those having answers 'ZZ1','OPD','WTT' alone?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 02:07:34

quote:
Originally posted by RobertKaucher

quote:
Originally posted by Idera

Your solution will work if the OP really has the data the way he has posted.But will not work if distinct id's has atleast one value from 'zz1'or 'opd' or 'wtt'
example

1--------A-------------ZZ1
1--------B-------------OPD
1--------C-------------WTT

2--------A-------------KFF
2--------B-------------SGG
2--------C-------------WTT

PBUH

I'm sorry. I do not follow your correction. Would you be kind enough to explain this to me again? Thank you!

Well your IN condition will ensure that record is returned when Answer is one among 'ZZ1','OPD','WTT' but it wont look whether that ID group has all others. OPs reqmnt was to consider each ID group and return only those that has answers 'ZZ1','OPD','WTT'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-07 : 05:39:27

quote:

Originally posted by Idera


select id from yourtable where val in('zz1','opd','wtt')
group by id
having Count(case (id) when 3 then 1 else 0 end)=3

PBUH

It's good ! if in my table questions includes as :

ID---------Question ----------Answer
567--------Z12345-------------NNNN1
567--------B12345-------------HHHH9

it's only two questions and two answer or more,your above statement still good ???
Because i'm new to sql ! can you explain your statement with words !

One problem :
I want to loop data in table as :
if (question='A' and answer='A1') and (question='B' and answer='B1') ...) i will list all data.
Can you write statement in sql ?

Thank you very much.

Sachin.Nand

2937 Posts

Posted - 2010-03-07 : 06:50:01

Pleas post all the combinations of the data that you can have in your table.Please refer the links below for on how do it.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Also visakh solutions is much cleaner.

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 08:24:36

quote:
Originally posted by pamyral_279

quote:
Originally posted by Idera

select id from yourtable where val in('zz1','opd','wtt')
group by id
having Count(case (id) when 3 then 1 else 0 end)=3

PBUH

It's good ! if in my table questions includes as :

ID---------Question ----------Answer
567--------Z12345-------------NNNN1
567--------B12345-------------HHHH9

it's only two questions and two answer or more,your above statement still good ???
Because i'm new to sql ! can you explain your statement with words !

One problem :
I want to loop data in table as :
if (question='A' and answer='A1') and (question='B' and answer='B1') ...) i will list all data.
Can you write statement in sql ?

Thank you very much.

did you try my suggestion? and what about question i asked?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-07 : 15:48:06

quote:
Originally posted by visakh16

SELECT t.ID,t.Question,t.Answer
FROM yourtable t
INNER JOIN (SELECT ID
FROM YourTable
GROUP BY ID
HAVING COUNT(DISTINCT CASE WHEN Answer IN ('ZZ1','OPD','WTT') THEN Answer ELSE NULL END) = 3
)t1
ON t.ID=t1.ID

one question here is what you want to do if that id has extra records with different answers? do them still have to be included? or is that you need to include only those having answers 'ZZ1','OPD','WTT' alone?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for the query

. your query is compact.

SELECT t.ID,t.Question,t.Answer
FROM yourtable t
INNER JOIN (SELECT ID
            FROM YourTable
            GROUP BY ID
            HAVING MAX(CASE WHEN Answer = 'ZZ1' THEN 1 ELSE 0 END) = 1
               AND MAX(CASE WHEN Answer = 'OPD' THEN 1 ELSE 0 END) = 1
               AND MAX(CASE WHEN Answer = 'WTT' THEN 1 ELSE 0 END) = 1
            )t1
ON t.ID=t1.ID

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-07 : 23:12:10

quote:
Originally posted by ms65g

quote:
Originally posted by visakh16

SELECT t.ID,t.Question,t.Answer
FROM yourtable t
INNER JOIN (SELECT ID
FROM YourTable
GROUP BY ID
HAVING COUNT(DISTINCT CASE WHEN Answer IN ('ZZ1','OPD','WTT') THEN Answer ELSE NULL END) = 3
)t1
ON t.ID=t1.ID

one question here is what you want to do if that id has extra records with different answers? do them still have to be included? or is that you need to include only those having answers 'ZZ1','OPD','WTT' alone?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for the query . your query is compact.

SELECT t.ID,t.Question,t.Answer
FROM yourtable t
INNER JOIN (SELECT ID
FROM YourTable
GROUP BY ID
HAVING MAX(CASE WHEN Answer = 'ZZ1' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN Answer = 'OPD' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN Answer = 'WTT' THEN 1 ELSE 0 END) = 1
)t1
ON t.ID=t1.ID

if i change order of answer as :
1--------A-------------OPD
1--------B-------------WTT
1--------C-------------ZZ1

your statement still list data,i think it should not be show !
because : question is A and answer is ZZ1 (not OPD)
Can you correct help me ?
1--------A-------------ZZ1
1--------B-------------OPD
1--------C-------------WTT

i'm greatly appreciate your kindness

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 23:53:23

[code]
SELECT t.ID,t.Question,t.Answer
FROM yourtable t
INNER JOIN (SELECT ID
FROM YourTable
GROUP BY ID
HAVING MAX(CASE WHEN Question = 'A' THEN Answer ELSE NULL END) = 'ZZ1'
AND MAX(CASE WHEN Question = 'B' THEN Answer ELSE NULL END) = 'OPD'
AND MAX(CASE WHEN Question = 'C' THEN Answer ELSE NULL END) = 'WTT'
)t1
ON t.ID=t1.ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-08 : 12:00:32

quote:

Originally posted by visakh16


SELECT t.ID,t.Question,t.Answer
FROM yourtable t
INNER JOIN (SELECT ID
            FROM YourTable
            GROUP BY ID
            HAVING MAX(CASE WHEN Question = 'A' THEN Answer ELSE NULL END) = 'ZZ1'
               AND MAX(CASE WHEN Question = 'B' THEN Answer ELSE NULL END) = 'OPD'
               AND MAX(CASE WHEN Question = 'C' THEN Answer ELSE NULL END) = 'WTT'
            )t1
ON t.ID=t1.ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Seem that's good !

special thanks visakhs.
I will check it again tomorrows

PS : if you explain :"CASE WHEN Question = 'A' THEN Answer ELSE NULL END) = 'ZZ1'" , it's quite perfect ! because i feel difficult to understand !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-08 : 12:02:01

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-10 : 09:48:45

quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hix..Can you explain :
"CASE WHEN Question = 'A' THEN Answer ELSE NULL END) = 'ZZ1'" , i feel difficult to understand !

--------------------------------------------------------------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 11:50:24

quote:
Originally posted by pamyral_279

quote:
Originally posted by visakh16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hix..Can you explain :
"CASE WHEN Question = 'A' THEN Answer ELSE NULL END) = 'ZZ1'" , i feel difficult to understand !

--------------------------------------------------------------------------------

its just making sure that for considered ID group the Answer to A question is ZZ1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources