Help on SQL query

  • 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

  • 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

  • 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.

  • Hi,

    Thanks for your help.

    Thanks

    NLV

  • I got answer from Gsquared user.

    Thanks

  • 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