September 24th, 2012, 09:35 AM
-
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.
September 24th, 2012, 05:47 PM
-
Look at LEFT JOIN, something like this: (not tested)
Code:
SELECT *
FROM table1
LEFT JOIN tabel2
WHERE table2.amount > 2
September 25th, 2012, 07:40 AM
-
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.
September 25th, 2012, 08:17 AM
-
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
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
September 25th, 2012, 08:18 AM
-
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.
September 25th, 2012, 01:48 PM
-
That did the trick! Thank you! i did not know you could place conditions in a join,