SELECT Statement

Dev Shed Forums Sponsor:

September 24th, 2012, 09:35 AM

mstopkey

Registered User

Join Date: Jan 2011

Posts: 6

Time spent in forums: 1 h 4 m 23 sec
Reputation 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.

September 24th, 2012, 05:47 PM

MrFujin

Lord of the Dance

Join Date: Oct 2003

Posts: 3,129

Time spent in forums: 2 Months 2 Weeks 23 h 6 m 34 sec
Reputation Power: 1736

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

mstopkey

Registered User

Join Date: Jan 2011

Posts: 6

Time spent in forums: 1 h 4 m 23 sec
Reputation Power: 0

Quote:

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

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 53 m 13 sec
Reputation Power: 284

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

September 25th, 2012, 08:18 AM

mstopkey

Registered User

Join Date: Jan 2011

Posts: 6

Time spent in forums: 1 h 4 m 23 sec
Reputation Power: 0

Quote:

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

mstopkey

Registered User

Join Date: Jan 2011

Posts: 6

Time spent in forums: 1 h 4 m 23 sec
Reputation Power: 0

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

Developer Shed Advertisers and Affiliates