Page 1 of 1

Needing help with subselects.

Posted: Mon Aug 17, 2009 1:19 am
by gogelpot
Hi hope somebody can help me. I have been trying to get this to work for the passed week now. (I must not be to bright).

I have two tabels "clients" and "orders". In "clients" are the client's info. In orders are the clients id and product (books) and language of the book.

CLIENTS:
id
Name
Address
Email

ORDERS:
Client_id (same as id in "clients")
Order
Language

The "orders" table holds multiple orders per client:
Client_id = 10
Order = book1
Language = Germ

Client_id = 10
Order = book2
Language = Germ

Client_id = 15
Order = book1
Language = Germ

Client_id = 15
Order = book3
Language = Eng

Now what I need is a select query that will find all the clients (just id) that has placed an order for just book1 and book2 and no other books and the language must be Germ.

Can someone plea.......se help?
Thanks

Re: Needing help with subselects.

Posted: Mon Aug 17, 2009 1:27 am
by susrisha

Code: Select all

 
SELECT CLIENTS.id from CLIENTS, ORDERS WHERE CLIENTS.id=ORDERS.Client_id AND ORDERS.LANGUAGE='Germ' AND (ORDERS.Order='book1' AND ORDERS.Order='book2');
 
This query returns client ids who have ordered for book1 and book2 but it will also return multiple entries of client ids.
This is not the exact query but u can start with this.

Re: Needing help with subselects.

Posted: Mon Aug 17, 2009 1:36 am
by VladSun
While susrisha's query is almost OK, I would advice you to use the JOIN keyword instead of comma operator:
[sql]SELECT     DISTINCT CLIENTS.id FROM     CLIENTSINNER JOIN    ORDERS ON CLIENTS.id=ORDERS.Client_id WHERE    ORDERS.LANGUAGE='Germ'     AND     ORDERS.ORDER='book1'    AND     ORDERS.ORDER='book2';[/sql]

Also, there is no need for using brackets.

Re: Needing help with subselects.

Posted: Mon Aug 17, 2009 1:39 am
by susrisha
@Vlad..
:offtopic: How do i make the sql query appear with code highlighting? is it the code tag ? or the sql tag?
i mean if i put php tags, its php code.. Whats for mysql queries tag?? I mean while posting in the forum.

Re: Needing help with subselects.

Posted: Mon Aug 17, 2009 2:55 am
by VladSun
instead of [ code ] use [ sql ] BB code tags (without spaces of course :) )
[ js ] for JavaScript
[ php ] for PHP code
;)

Re: Needing help with subselects.

Posted: Mon Aug 17, 2009 5:03 am
by susrisha
[sql] SELECT thanks FROM codeTable WHERE user= Vladsun[/sql] :P