select of select - 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

select of select

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-05 : 02:49:10

Hi all,

I have structure table as , include 2 tables : person,vote
person (pid,name,ip,city,time)
vote (pid,question,answer)

person table
pid ---name --ip--address-city--time
1--abc--203.162.33.55--Manchester -xxx
1--def--203.182.32.55--London -xxx
1--mgn--203.122.22.55--Westham -xxx

vote table
pid--question --answer
1-- A---------------A1
1-- B---------------B1
1-- C---------------C1
2-- A---------------M1
2-- B---------------L1
2-- C---------------N1
3-- A---------------J1
3-- B---------------L1
3-- C---------------N1

I use statement : select * from person,vote where person.pid=vote.pid
Now i want sub data as person in question='A' and answer ='A1' and question ='B' and answer='L1' and question='C' and answer='N1'......
How can i write ??
Thank you very much.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-05 : 03:20:28

could you please write us expected output...

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-05 : 04:12:39

My expected output :

pid---name--- ip --- city --- question---answer
1----A---203.162.33.55--manchester-xxx---A---A1
....
100----Z1---33.62.73.16--london-xxx---B---L1

....
200----Z1---33.62.73.16--london-xxx---C---N1

Thank you very much.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-05 : 04:34:39

quote:
Originally posted by pamyral_279

My expected output :

pid---name--- ip --- city --- question---answer
1----A---203.162.33.55--manchester-xxx---A---A1
....
100----Z1---33.62.73.16--london-xxx---B---L1

....
200----Z1---33.62.73.16--london-xxx---C---N1

Thank you very much.

Could you please try this..

declare @person table
(
pid int,
name varchar(5)
)

declare @vote table
(
pid int,
question varchar(21),
Answer varchar(2)
)

insert @person

select 1,'abc' union all
select 1,'def' union all
select 1,'mgn'

insert @vote

select 1, 'A','A1' union all
select 1, 'B', 'B1' union all
select 1, 'C' ,'C1' union all
select 2, 'A','M1' union all
select 2, 'B' ,'L1' union all
select 2,'C' ,'N1' union all
select 3, 'A' ,'J1' union all
select 3, 'B', 'L1' union all
select 3, 'C','N1'

select * from @person
select * from @vote


select p.pid,p.name,v.question,v.Answer from @person as p
inner join @vote as v on p.pid=v.pid and
v.question='A' AND v.Answer='A1' OR
 v.question ='B' AND v.answer='L1' OR
 v.question='C' AND v.answer='N1' GROUP BY p.pid,p.name,V.question,V.Answer

if doesn't meet ur expectation,write back please

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-05 : 12:49:05

quote:
Originally posted by haroon2k9

quote:
Originally posted by pamyral_279

My expected output :

pid---name--- ip --- city --- question---answer
1----A---203.162.33.55--manchester-xxx---A---A1
....
100----Z1---33.62.73.16--london-xxx---B---L1

....
200----Z1---33.62.73.16--london-xxx---C---N1

Thank you very much.

Could you please try this..

declare @person table
(
pid int,
name varchar(5)
)

declare @vote table
(
pid int,
question varchar(21),
Answer varchar(2)
)

insert @person

select 1,'abc' union all
select 1,'def' union all
select 1,'mgn'

insert @vote

select 1, 'A','A1' union all
select 1, 'B', 'B1' union all
select 1, 'C' ,'C1' union all
select 2, 'A','M1' union all
select 2, 'B' ,'L1' union all
select 2,'C' ,'N1' union all
select 3, 'A' ,'J1' union all
select 3, 'B', 'L1' union all
select 3, 'C','N1'

select * from @person
select * from @vote

select p.pid,p.name,v.question,v.Answer from @person as p
inner join @vote as v on p.pid=v.pid and
v.question='A' AND v.Answer='A1' OR
v.question ='B' AND v.answer='L1' OR
v.question='C' AND v.answer='N1' GROUP BY p.pid,p.name,V.question,V.Answer

if doesn't meet ur expectation,write back please

Thank you but it's wrong, I want both A1,L1 and N1,three conditions have to satisfy,your statement added L1,N1 without A1 (using or)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources