August 30, 2011 at 10:58 am
Hi,
I am having data in 2 tables in the following format and I should get data in a single row with out duplicate eid's. Please correct my query without having duplicate rows.
Following is the sample format:
--===== Create the test table with
CREATE TABLE #mytable1
(eid numeric,fname varchar(10),lname varchar(10))
create table #mytable2
(eid numeric,estatus varchar(10),points numeric)
--inserting data into 1st table (#mytable1)
insert into #mytable (eid,fname,lname)
select '1', 'abc','def' union all
select '2', 'zzz','yyy' union all
select '3', 'ada','sda' union all
select '4', 'wqw','aas'
--inserting data into 2nd table (#mytable2)
insert into #mytable2 (eid,estatus,points)
select '1', 'good',100 union all
select '1', 'verygood',200 union all
select '2', 'good',300 union all
select '2', 'verygood',400 union all
select '3', 'good',100 union all
select '3', 'verygood',300 union all
select '4', 'good',500 union all
select '4', 'verygood',500
Query:
select a.eid,a.fname,a.lname,b.points
from #mytable1 a
inner join #mytable2 b
on a.eid=b.eid
where b.estatus in ('good','verygood')
Expecting Result:
select 1 as eid,'abc'as fname,'def' as lname,100 as points1,200 as points2 union all
select 2 as eid,'zzz'as fname,'yyy' as lname,300 as points1,400 as points2 union all
select 3 as eid,'ada'as fname,'sda' as lname,100 as points1,300 as points2 union all
select 4 as eid,'wqw'as fname,'aas' as lname,500 as points1,600 as points2
August 30, 2011 at 11:04 am
You need to query the second table twice.
Like this:
SELECT a.eid,
a.fname,
a.lname,
b.points AS points1,
c.points AS points1
FROM #mytable1 a
INNER JOIN #mytable2 b
ON a.eid = b.eid
AND b.estatus = 'good'
INNER JOIN #mytable2 c
ON a.eid = c.eid
AND c.estatus = 'verygood';
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 30, 2011 at 11:09 am
Was just about to post what Gus did. You're confusing rows and columns a bit.
Since you have multiple rows with points, you get those results as rows returned if you just join the tables once.
August 30, 2011 at 11:14 am
Hi,
Thanks for your help.
Thanks
NLV
August 30, 2011 at 11:16 am
I got answer from Gsquared user.
Thanks
August 30, 2011 at 11:21 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply