SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast sanjeev's Avatar
    Join Date
    Sep 2002
    Location
    jaipur - india
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to join tables on a condition

    hi,

    i have one tracking table in which i have two coloum, tracking_no and service_id,

    the related information is stored in different tables for different service id,

    now i want to run a sql query which join the tables with service_id conditions as

    if service_id = 1 join table1
    else
    service_id = 2 join table 2
    so on

    is this possible??? how can i put condition on join.

    thanks a lot

    Sanjeev

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
      from tracking
    left outer 
      join table1
        on table1.somecolumn = tracking.somecolumn
       and tracking.service_id = 1
    left outer 
      join table2
        on table2.somecolumn = tracking.somecolumn
       and tracking.service_id = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast sanjeev's Avatar
    Join Date
    Sep 2002
    Location
    jaipur - india
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks rudy,

    its working, but i have a "status" field in all the services tables so its giving me error

    "Column 'status' in field list is ambiguous"

    how can i sort this problem in select statement.

    thanks a lot.

    sanjeev

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sanjeev
    how can i sort this problem in select statement.
    by not using SELECT *

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast sanjeev's Avatar
    Join Date
    Sep 2002
    Location
    jaipur - india
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hey im not using select * here is my query:

    Select tracking_no, status
    from ((tracking_mast tm left outer join res_mast rm on (rm.tracking_no = tm.tracking_no and tm.service_id = 1 ))
    left outer join res_itinerary_mast rim on (rim.res_tracking_no = tm.res_tracking_no and and rtm.service_id = 2))

    but the problem is that i have "status" coloum in both the tables

    any idea to sorts this out...

    sanjeev

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by sanjeev
    any idea to sorts this out...
    qualify which one you want
    Code:
    select tracking_no
         , rim.status
    i know what your next question is gonna be, too

    "how do i pick the one that is returned by the join (since the join will return only one of the two tables)?"
    Code:
    select tracking_no
         , coalesce(rm.status,rim.status) as status
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast sanjeev's Avatar
    Join Date
    Sep 2002
    Location
    jaipur - india
    Posts
    94
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how do i pick the one that is returned by the join (since the join will return only one of the two tables)?
    yes it will be, my sql is not so good, so im here,

    thnaks a lot to pointing out first

    sanjeev

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thank you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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