Left Join Query Problem

[Coldfire]

Two tables

customer (id,name) = {
'1','ABC'; 
'2','DEF';
'3','GHI'
}

images (id,customer_id,subscription_id,imageURL) = {
'1','1','123','abc123.jpg'; 
'2','2','456','asdjkl.png'; 
'3','3','456','asad.gif'; 
}

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.i mageURL from customer c LEFT JOIN images i ON c.id=i.customer _id WHERE i.subscription_ id='456'"

What I need is

(customer.name, images.id, imageURL) 
{ABC,1,NULL},
{DEF,2,'asdjkl.png'},
{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

[Uncle Dickie]

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

Something like:

CASE subscription_id
  WHEN 456 THEN imageURL
  ELSE null
END

Hope this helps!

[ThatThatGuy]

Two tables

customer (id,name) = {
'1','ABC'; 
'2','DEF';
'3','GHI'
}

images (id,customer_id,subscription_id,imageURL) = {
'1','1','123','abc123.jpg'; 
'2','2','456','asdjkl.png'; 
'3','3','456','asad.gif'; 
}

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.i mageURL from customer c LEFT JOIN images i ON c.id=i.customer _id WHERE i.subscription_ id='456'"

What I need is

(customer.name, images.id, imageURL) 
{ABC,1,NULL},
{DEF,2,'asdjkl.png'},
{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

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