Needing help with subselects.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
gogelpot
Forum Newbie
Posts: 2
Joined: Mon Aug 17, 2009 1:16 am

Needing help with subselects.

Post 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
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

Re: Needing help with subselects.

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Needing help with subselects.

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

Re: Needing help with subselects.

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Needing help with subselects.

Post by VladSun »

instead of [ code ] use [ sql ] BB code tags (without spaces of course :) )
[ js ] for JavaScript
[ php ] for PHP code
;)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
susrisha
Forum Contributor
Posts: 439
Joined: Thu Aug 07, 2008 11:43 pm
Location: Hyderabad India

Re: Needing help with subselects.

Post by susrisha »

[sql] SELECT thanks FROM codeTable WHERE user= Vladsun[/sql] :P
Post Reply