Hi!

I have two tables with the following data:

Table1 (places)
CODE    NAME
1            Place1
2            Place2
3            Place 3
4            Place 4

Table2 (quantity)
PLACE    PRODUCT    QUANTITY
1            A                    3
2            A                    2
1            B                    5
3            B                    10

I need to show quantity by place for ONE product (for example A), showing
ALL places, not only the ones where product A has quantity.
I mean, I want to show this:

PLACENAME    QUANTITY
Place1                3
Place2                2
Place3
Place4

....even if there is no quantity on Place3 and 4 I need to show them.

I have been trying different JOIN clauses with no success. Is this possible?

Thanks!

Luis C.

What about something like this:

select * from Table1 LEFT OUTER JOIN Table2
on Table1.CODE=Table2.Place
Where Table2.PRODUCT=1 OR Table2.PRODUCT is NULL

Raul

On 7/25/2013 8:34 AM, Luis Conception Gonzalez wrote:
> Hi!
>
> I have two tables with the following data:
>
> Table1 (places)
> CODE    NAME
> 1            Place1
> 2            Place2
> 3            Place 3
> 4            Place 4
>
> Table2 (quantity)
> PLACE    PRODUCT    QUANTITY
> 1            A                    3
> 2            A                    2
> 1            B                    5
> 3            B                    10
>
> I need to show quantity by place for ONE product (for example A),
> showing ALL places, not only the ones where product A has quantity.
> I mean, I want to show this:
>
> PLACENAME    QUANTITY
> Place1                3
> Place2                2
> Place3
> Place4
>
> ...even if there is no quantity on Place3 and 4 I need to show them.
>
> I have been trying different JOIN clauses with no success. Is this
> possible?
>
> Thanks!
>
> Luis C.

I meant this (.PRODUCT='A' in where clause)

select * from Table1 LEFT OUTER JOIN Table2
on Table1.CODE=Table2.Place
Where Table2.PRODUCT='A' OR Table2.PRODUCT is NULL

Raul

I already tried this. No success.
I'm getting this result:

PLACENAME    QUANTITY
Place1                3
Place2                2
Place3

"Place4" is not shown. Maybe because there is no Place4 at all on Table2



Luis C.


"Raul"  escribió en el mensaje de
noticias:C6122010-3E3B-4A75-BDA8-84BBA5DFBB99@news.elevatesoft.com...

I meant this (.PRODUCT='A' in where clause)

select * from Table1 LEFT OUTER JOIN Table2
on Table1.CODE=Table2.Place
Where Table2.PRODUCT='A' OR Table2.PRODUCT is NULL

Raul

After checking your need...

select code,name,if(product='A','A',null),if(product='A',qty,0) from Table1 left JOIN Table2
on Table1.CODE=Table2.Place or table2.product is null
group by code,name

Eduardo

Luis

How about

select * from places LEFT OUTER JOIN quantity
on places.CODE=quantity.Place and quantity.PRODUCT='A'

John