[mstopkey]

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.

[MrFujin]

Look at LEFT JOIN, something like this: (not tested)

SELECT *
FROM table1
LEFT JOIN tabel2
WHERE table2.amount > 2

[mstopkey]

Originally Posted by MrFujin

Look at LEFT JOIN, something like this: (not tested)

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.

[shammat]

You need to move the condition for the amount into the JOIN clause (out off the WHERE clause)

select t1.id,
       t1.name,
       t2.amount
from table1 t1
  left join table2 t2
         on t1.id = t2.t1_id
        and table2.amount > 2

[mstopkey]

Originally Posted by shammat

You need to move the condition for the amount into the JOIN clause (out off the WHERE clause)

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.

[mstopkey]

That did the trick! Thank you! i did not know you could place conditions in a join,