mysql query

Hi

need help
My english is not good so ill be short.
This is my database in mysql

create table customers
(
customerid int unsigned not null auto_increment primary key,
name char(40) not null,
address char(40) not null,
city char(20) not null,
state char(20),
zip char(10),
country char(20) not null
);

create table orders
(
orderid int unsigned not null auto_increment primary key,
customerid int unsigned not null,
amount float(6,2),
date date not null,
order_status char(10),
ship_name char(40) not null,
ship_address char(40) not null,
ship_city char(20) not null,
ship_state char(20),
ship_zip char(10),
ship_country char(20) not null
);

create table books
(
isbn char(13) not null primary key,
author char(30),
title char(60),
catid int unsigned,
price float(4,2) not null,
description varchar(255)
);

create table categories
(
catid int unsigned not null auto_increment primary key,
catname char(40) not null
);

create table order_items
(
orderid int unsigned not null,
isbn char(13) not null,
item_price float(4,2) not null,
quantity tinyint unsigned not null,
primary key (orderid, isbn)
);

The question is how to make a query to get order list of books from specific
customer.
example: John ordered LoR1 j.r.r.tolkien 20 $ The best book ever
LoR2 j.rr Tolkirn 20$ The second best
book
Php mySql Welling 25$ The good book of
php

Please help
Thanks

Re: mysql query

SELECT title
FROM orders, customers, order_items
LEFT JOIN books ON books.isbn = order_items.isbn
WHERE orders.customerid = customers.customerid
AND order_items.orderid = orders.orderid
AND customers.name = 'John'
LIMIT 0 , 30

Re: mysql query

Thanks strawberry
"strawberry" wrote in message
news:1145980780.415994.238000@y43g2000cwc.googlegroups.com.. .
> SELECT title
> FROM orders, customers, order_items
> LEFT JOIN books ON books.isbn = order_items.isbn
> WHERE orders.customerid = customers.customerid
> AND order_items.orderid = orders.orderid
> AND customers.name = 'John'
> LIMIT 0 , 30
>

Re: mysql query

I think it's right, however, if someone buys the same book twice, the
title will show up twice in this result. You would need to modify the
query like this...

SELECT title
FROM orders, customers, order_items
LEFT JOIN books ON books.isbn = order_items.isbn
WHERE orders.customerid = customers.customerid
AND order_items.orderid = orders.orderid
AND customers.name = 'John'
GROUP BY title
LIMIT 0 , 30

Incidentally, to solve this kind of thing yourself, just build up the
query slowly, bit by bit. Begin with something like;

SELECT * FROM orders,customers;

and see where it gets you

Re: mysql query

Thanks for tip.:-)
See ya
"strawberry" wrote in message
news:1145997588.139014.173090@g10g2000cwb.googlegroups.com.. .
>I think it's right, however, if someone buys the same book twice, the
> title will show up twice in this result. You would need to modify the
> query like this...
>
> SELECT title
> FROM orders, customers, order_items
> LEFT JOIN books ON books.isbn = order_items.isbn
> WHERE orders.customerid = customers.customerid
> AND order_items.orderid = orders.orderid
> AND customers.name = 'John'
> GROUP BY title
> LIMIT 0 , 30
>
> Incidentally, to solve this kind of thing yourself, just build up the
> query slowly, bit by bit. Begin with something like;
>
> SELECT * FROM orders,customers;
>
> and see where it gets you
>