Results 1 to 11 of 11

Thread: Query

  1. #1
    Join Date
    May 2008
    Posts
    5

    Question Unanswered: Query

    i have two tables with the following inserted values
    table1 - spid, spno, amt
    values - 100, 0, 1000
    - 111, 444, 2000
    - 200, 0, 1000

    table2 - spid, spno
    values - 100, 0
    200, 0

    my answer should be like this

    values - 100, 0, 1000
    - 200, 0, 1000
    - 111, 444, 2000

    any help is much appreciated..thanks

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Code:
    SELECT *
    FROM   table1
    This will return exactly what you want, just with a different ordering. And since you didn't give us any details on the semantics that you would like to achieve, we cannot say anything further.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    raj, that makes no sense at all

    you could do this and get the same result:
    Code:
    SELECT * FROM table1 ORDER BY amt,spid

    Edit: rats, sniped again!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    May 2008
    Posts
    5

    Question

    sorry I could have given you more details...
    here it is...

    I need to join both the tables using spid & spno...spid is primary key in table2..if i'm executing the following sql..

    select spid, spno, amt from db2.table1 a join db2.table2 b on a.spid = b.spid...
    the above query returns..this is right...


    spid, spno, amt
    100, 0, 1000
    200, 0, 1000
    .....,...,...

    but i need the different version..i need all the values..it means...

    [I]select spid, spno, amt from db2.table1 a join db2.table2 b on a.spid = b.spid and a.spno=b.spno[I]..

    this will return redundant data..but i need only the values available in both the tables based on the condition...can i add any extra column in any of the table to achieve this...?

    note: please look into table1.spno, which has values 0's and non 0's...non 0's are the values equal to table2.spid...i need both the 0's and non 0's should be combined and displayed in a single query.....the first query gives only the non 0's in two tables...the second query gives redundant data of both 0's and non 0's...i dont want to use distinct keyword or group by....but i can add any extra column or some alternatives....pls help

    i think this will give you more details...if not, pls let me know....

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rajkumaron
    i think this will give you more details...if not, pls let me know....
    more details, yes

    but i still don't understand
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2008
    Posts
    5
    i have 3 tables...we have two different kinds of suppliers....supplier1 made a transaction through credit card(pay mthd=1)..supplier 2 using other options like cheque, etc...(pay mthd=2)..i need to get both the transactions in a single query...sample table structure below...see the transaction table..if it is pay mthd 1, then sply id = 0 and if pay mthd =2, then sply no=0...how can i join supplier 2 table with transaction table to get all the transactions made...?

    supplier 1 table
    --------------
    sply no(pk), sply name,......
    111, ttttt, .....
    222, rrrrr, ......
    ....


    transaction table
    ----------------
    transno(pk), sply id(fk sup2 table), pay mthd, sply no(fk sup1 table)

    1, 100, 2, 0
    2, 200, 2, 0
    3, 0, 1, 111
    4, 0, 1, 222
    .......


    supplier 2 table
    ---------------
    sply id(pk), sply name, sply no(FK)
    100, test1, 0
    200, test2, 0
    .....

    hope this would help.....

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    woohoo, we are getting somewhere

    now there are 3 tables instead of 2!!!

    why does supplier2 table link to supplier1 table?

    how can i join supplier 2 table with transaction table to get all the transactions made...?
    like this --

    FROM transactions LEFT OUTER JOIN supplier2
    ON supplier2.splyid = transactions.splyid

    easy, yes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2008
    Posts
    5
    sorry for the confusion...I gave you the supplier 1 table just for the information only....

    yeah...you are right...this is what i did..is there any alternative to achieve the same resultset without using left outer join..?

    if i use left outer join, performance would be affected?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's wrong with LEFT OUTER JOIN?

    no, performance will not be affected

    of course, the best performance is not running a query at all -- that will reduce the total CPU time to zero
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2008
    Posts
    5
    Thank you...Thanks for your patience too.....

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I think another piece of this puzzle was left out. If I'm reading/understanding this correctly, then you will need a union as well. Something like:
    select cols_u_want
    from transaction t
    left outer join supplier1 s1
    on t.key = s1.key
    union
    select cols_u_want
    from transaction t
    left outer join supplier2 s2
    on t.key = s2.key
    Dave Nance

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •