LEFT JOIN problem

Hi,

1. pic TABLE ROWs
|id_pic |desc
|1 |pic1.jpg
|2 |pic2.jpg
|3 |pic3.jpg

2. pic_desc TABLE ROWs
|id_pic |lang |desc
|1 |hr |desc 1 HR
|2 |hr |desc 2 HR
|2 |en |desc 2 EN

My query (parametar 'lang'):
SELECT pic.id_pic, pic.pic, pic_desc.desc
FROM pic
LEFT JOIN pic_desc ON pic.id_pic = pic_desc.id_pic
WHERE pic.id_entry =1
AND (
pic_desc.lang = 'en'
OR pic_desc.lang IS NULL
)

QUERY RES lang='hr'
|id_pic |pic |desc
|1 |pic1.jpg |desc 2 HR
|2 |pic2.jpg |desc 2 HR
|3 |pic3.jpg |NULL
OK

QUERY RES lang='en':
|id_pic |pic |desc
|2 |pic2.jpg |desc 2 EN
|3 |pic3.jpg |NULL
!OK
missing: pic1.jpg (id=1) desc=NULL

If someone can explain what is happening or give me some solution

Thanx

Re: LEFT JOIN problem

!!!CORECTION !!!
> Hi,
>
ADD id_entry
> 1. pic TABLE ROWs
> |id_pic |pic |id_entry
> |1 |pic1.jpg |1
> |2 |pic2.jpg |1
> |3 |pic3.jpg |1
>
> 2. pic_desc TABLE ROWs (id_pic !PRIMARY)
> |id_pic |lang |desc
> |1 |hr |desc 1 HR
> |2 |hr |desc 2 HR
> |2 |en |desc 2 EN
>
> My query (parametar 'lang'):
> SELECT pic.id_pic, pic.pic, pic_desc.desc
> FROM pic
> LEFT JOIN pic_desc ON pic.id_pic = pic_desc.id_pic
> WHERE pic.id_entry =1
> AND (
> pic_desc.lang = 'en'
> OR pic_desc.lang IS NULL
> )
>
> QUERY RES lang='hr'
> |id_pic |pic |desc
> |1 |pic1.jpg |desc 2 HR
> |2 |pic2.jpg |desc 2 HR
> |3 |pic3.jpg |NULL
> OK
>
> QUERY RES lang='en':
> |id_pic |pic |desc
> |2 |pic2.jpg |desc 2 EN
> |3 |pic3.jpg |NULL
> !OK
> missing: pic1.jpg (id=1) desc=NULL
>
> If someone can explain what is happening or give me some solution
>
> Thanx

Re: LEFT JOIN problem

FatCoin wrote:
> Hi,
>
> 1. pic TABLE ROWs
> |id_pic |desc
> |1 |pic1.jpg
> |2 |pic2.jpg
> |3 |pic3.jpg
>
> 2. pic_desc TABLE ROWs
> |id_pic |lang |desc
> |1 |hr |desc 1 HR
> |2 |hr |desc 2 HR
> |2 |en |desc 2 EN
>
> My query (parametar 'lang'):
> SELECT pic.id_pic, pic.pic, pic_desc.desc
> FROM pic
> LEFT JOIN pic_desc ON pic.id_pic = pic_desc.id_pic
> WHERE pic.id_entry =1
> AND (
> pic_desc.lang = 'en'
> OR pic_desc.lang IS NULL
> )

Here you're filtering out your missing picture. See what happens

pic LEFT JOIN pic_desc ON ... gives you

|1 |pic1.jpg| hr
|2 |pic2.jpg| hr
|2 |pic2.jpg| en
|3 |pic3.jpg| null

Now you doing a where on this result (WHERE pic_desc.lang='en' OR pic_desc.lang IS NULL)
So the first row disappears in this filter.

What you have to do is to add the language filter to the ON clause.

SELECT pic.id_pic, pic.pic, pic_desc.desc
FROM pic
LEFT JOIN pic_desc ON pic.id_pic = pic_desc.id_pic AND pic_desc.lang='en'
WHERE pic.id_entry =1


Regards
Stefan

>...

> Thanx

Re: LEFT JOIN problem

Stefan Rybacki wrote:
>...
> |1 |pic1.jpg| hr
> |2 |pic2.jpg| hr
> |2 |pic2.jpg| en
> |3 |pic3.jpg| null
>
> Now you doing a where on this result (WHERE pic_desc.lang='en' OR
> pic_desc.lang IS NULL)
> So the first row disappears in this filter.

As well as the second. Just to mention.

>...

Re: LEFT JOIN problem

Stefan Rybacki wrote:
> Stefan Rybacki wrote:
>
>> ...
>> |1 |pic1.jpg| hr
>> |2 |pic2.jpg| hr
>> |2 |pic2.jpg| en
>> |3 |pic3.jpg| null
>>
>> Now you doing a where on this result (WHERE pic_desc.lang='en' OR
>> pic_desc.lang IS NULL)
>> So the first row disappears in this filter.
>
>
> As well as the second. Just to mention.
>
> >...
I learn something new... thank you
Didn't know filtering in ON clause