September 24th, 2012, 10:35 AM
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.
September 24th, 2012, 06:47 PM
Look at LEFT JOIN, something like this: (not tested)
LEFT JOIN tabel2
WHERE table2.amount > 2
September 25th, 2012, 08:40 AM
Hello, thanks for the reply.
Originally Posted by MrFujin
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.
September 25th, 2012, 09:17 AM
You need to move the condition for the amount into the JOIN clause (out off the WHERE clause)
from table1 t1
left join table2 t2
on t1.id = t2.t1_id
and table2.amount > 2
Comments on this post
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
Tips on how to ask better questions:
September 25th, 2012, 09:18 AM
Originally Posted by shammat
Thanks, I will try that.
September 25th, 2012, 02:48 PM
That did the trick! Thank you! i did not know you could place conditions in a join,