Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2007
    Posts
    21

    Unanswered: Select , populate from diff tables based on condition

    I have 3 tables....
    table 1: jobs
    columns: job_id, customer_id, site_Id;
    table 2: customers
    columns: customer_id, address1, state, postcode
    table3: addresses
    columns: site_id, address1, state, postcode

    My select statement should return job_id, address1, state, postcode
    from jobs as j, customers as c, addresses as a.

    If j.site_id > 1
    then a.address1, a.state, a.postcode should be filled
    else c.address1, c.state, c.postcode should be filled

    any solution on how to do it?

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Does the platform you are using support the CASE statement?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Apr 2007
    Posts
    21
    yes it does....

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    CASE statements, as RedNeckGeek suggested, are SQL's version of an If statement.
    The syntax is generally
    Code:
    CASE WHEN <criteria> THEN <output when true> ELSE <output when false> END
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    CASE will not do the job here because it is a question of joining to one table or the other, and in order to use CASE as suggested you'd have to join to them both

    there's a different approach:
    Code:
    select a.address1, a.state, a.postcode 
      from jobs
    inner
      join addresses as a
        on a.site_id = jobs.site_id
     where jobs.site_id > 1
    union all
    select c.address1, c.state, c.postcode 
      from jobs
    inner
      join customers as c
        on c.customer_id = jobs.customer_id
     where jobs.site_id <= 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    nevermind.... I see the error in my ways.
    Rudy wins again
    Last edited by RedNeckGeek; 04-10-07 at 09:01.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Apr 2007
    Posts
    21
    thanks a lot r937, yet to check if it gets the right data

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Am I being foolish? Why wouldn't CASE work?
    Because the data is in different tables?
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2007
    Posts
    21
    i have tried it with case....
    but it only returns one row......which is the last one.
    unless i've being trying it the wrong way

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Re-read r937's post (#5) - he's the SQL king-thing...
    If you can't get that to work then care to post your SQL statement?
    George
    Home | Blog

Posting Permissions

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