Home > Archive > PHP SQL > November 2005 > LEFT JOIN problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| FatCoin 2005-11-17, 7:56 am |
| 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
| |
| FatCoin 2005-11-17, 7:56 am |
| !!!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
| |
| Stefan Rybacki 2005-11-17, 7:56 am |
| 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
| |
| Stefan Rybacki 2005-11-17, 7:56 am |
| 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.
>...
| |
| FatCoin 2005-11-18, 7:56 am |
| Stefan Rybacki wrote:[color=darkred]
> Stefan Rybacki wrote:
>
>
>
> As well as the second. Just to mention.
>
I learn something new... thank you
Didn't know filtering in ON clause
|
|
|
|
|