#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    6
    Rep Power
    0

    SELECT Statement


    Hello all. This is probably an easy one for you but I am having difficulty with it. Here goes.
    Let's say I have two tables.
    Table1 has two columns: id & name
    Table2 has two columns: id & amount
    Column id is customer identity in both tables. Customer in Table1 may or may not have records in Table2.
    I want to select all customers from Table1 (whether or not they have records in Table2) and amount (Where amount > '2.00') from Table2.

    This will help me greatly in supplying reports for our customers.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,730
    Rep Power
    1959
    Look at LEFT JOIN, something like this: (not tested)
    Code:
    SELECT *
    FROM table1
    LEFT JOIN tabel2
    WHERE table2.amount > 2
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    6
    Rep Power
    0
    Originally Posted by MrFujin
    Look at LEFT JOIN, something like this: (not tested)
    Code:
    SELECT *
    FROM table1
    LEFT JOIN tabel2
    WHERE table2.amount > 2
    Hello, thanks for the reply.
    That WHERE clause will omit any records from table1 that do not meet that condition. I am looking to show all records from table1 whether or not they have any records in table2 but any records in table2 must meet the condition.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,788
    Rep Power
    349
    You need to move the condition for the amount into the JOIN clause (out off the WHERE clause)
    Code:
    select t1.id,
           t1.name,
           t2.amount
    from table1 t1
      left join table2 t2
             on t1.id = t2.t1_id
            and table2.amount > 2

    Comments on this post

    • MrFujin agrees : Can also see I even forgot the base join condition. :S
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    6
    Rep Power
    0
    Originally Posted by shammat
    You need to move the condition for the amount into the JOIN clause (out off the WHERE clause)
    Code:
    select t1.id,
           t1.name,
           t2.amount
    from table1 t1
      left join table2 t2
             on t1.id = t2.t1_id
            and table2.amount > 2

    Thanks, I will try that.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2011
    Posts
    6
    Rep Power
    0
    That did the trick! Thank you! i did not know you could place conditions in a join,

IMN logo majestic logo threadwatch logo seochat tools logo