By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,143 Members | 709 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,143 IT Pros & Developers. It's quick & easy.

Left Join Query Problem

Coldfire
100+
P: 289
Two tables

Expand|Select|Wrap|Line Numbers
  1. customer (id,name) = {
  2. '1','ABC'; 
  3. '2','DEF';
  4. '3','GHI'
  5. }
Expand|Select|Wrap|Line Numbers
  1. images (id,customer_id,subscription_id,imageURL) = {
  2. '1','1','123','abc123.jpg'; 
  3. '2','2','456','asdjkl.png'; 
  4. '3','3','456','asad.gif'; 
  5. }
I want to get the customer.name, images.id, and images.imageURL from the two tables. But,

the subscription_id should be '456'

AND

Join on customer id.

The query I am using is,

SQL = "Select c.name,i.id,i.imageURL from customer c LEFT JOIN images i ON c.id=i.customer_id WHERE i.subscription_id='456'"

What I need is
Expand|Select|Wrap|Line Numbers
  1. (customer.name, images.id, imageURL) 
  2. {ABC,1,NULL},
  3. {DEF,2,'asdjkl.png'},
  4. {GHI,3,'asad.gif'}
But it is returnning the last 2 rows as the WHERE clause is filtering the first ROW. But, I need NULL for the first row.

It is because I want to get the images of all the customers when the subscription is '456' if the subscription is something else then NULL should be returned.

Should I use something else instead of LEFT JOIN or what ?

any help
Jun 4 '10 #1

✓ answered by Uncle Dickie

Use a CASE statement in the SELECT part rather than a WHERE condition.

Something like:
Expand|Select|Wrap|Line Numbers
  1. CASE subscription_id
  2.   WHEN 456 THEN imageURL
  3.   ELSE null
  4. END
Hope this helps!

Share this Question
Share on Google+
2 Replies

Uncle Dickie
P: 67
Use a CASE statement in the SELECT part rather than a WHERE condition.

Something like:
Expand|Select|Wrap|Line Numbers
  1. CASE subscription_id
  2.   WHEN 456 THEN imageURL
  3.   ELSE null
  4. END
Hope this helps!
Jun 7 '10 #2

ThatThatGuy
Expert 100+
P: 449
@Coldfire
Obviously, you will get the imageURL field as null as you've applied LEFT JOIN..

Instead you should use INNER JOIN which will yield even more better results
Jun 7 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.