Board index » delphi » Silly SQL question (JOIN?)

Silly SQL question (JOIN?)

I thought this would be simple, but I got stuck...

2 Tables:

Products:
prod_id | shop_id
-----------------
1         2
2         2
3         1

Brnanchshop
branche_id | shop_id
--------------------
1           2
2           2

Now, I want to search for all products that have the branche_id = 1, but

select prod_id from products LEFT JOIN branchshop ON branchshop.shop_id
= products.shop_id;

returns
1
2
1
2
two times, instead of
1
2
So I am wondering how do I lookup some field.
DB is MySQL.
Solution with distinct would also do.

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

 

Re:Silly SQL question (JOIN?)


Quote
On Wed, 01 Sep 1999 23:10:08 GMT, ammar_agano...@my-deja.com wrote:
>I thought this would be simple, but I got stuck...

>2 Tables:

>Products:
>prod_id | shop_id
>-----------------
>1         2
>2         2
>3         1

>Brnanchshop
>branche_id | shop_id
>--------------------
>1           2
>2           2

>Now, I want to search for all products that have the branche_id = 1, but

>select prod_id from products LEFT JOIN branchshop ON branchshop.shop_id
>= products.shop_id;

>returns
>1
>2
>1
>2
>two times,

Naturally. You forgot "and branche_id=1" part.

Quote
>instead of
>1
>2
>So I am wondering how do I lookup some field.
>DB is MySQL.
>Solution with distinct would also do.

>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
To reply, remove 1st xyz, replace 2nd w/ ca.

Re:Silly SQL question (JOIN?)


Quote
ammar_agano...@my-deja.com wrote:
>I thought this would be simple, but I got stuck...
>...
>Now, I want to search for all products that have the branche_id = 1, but

>select prod_id from products LEFT JOIN branchshop ON branchshop.shop_id
>= products.shop_id;

You probably want DISTINCTROW in there, too.
--
- Tim Roberts, t...@probo.com
  Providenza & Boekelheide, Inc.

Other Threads