Post reply

Help on SQL query

  • August 30, 2011 at 10:58 am

    #242456

    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

    #1375138

    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

    #1375143

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 30, 2011 at 11:14 am

    #1375148

    Hi,

    Thanks for your help.

    Thanks

    NLV

  • August 30, 2011 at 11:16 am

    #1375149

    I got answer from Gsquared user.

    Thanks

  • August 30, 2011 at 11:21 am

    #1375152

    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