By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

query hheeelp

P: n/a
Hi

need help
My english is not good so ill be short.
This is my database
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 Ivan
Apr 25 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Hi,

try this one here:
Select Cust.name,b.author,Cat.catname,oi.item_price,b.des cription
From Customers Cust

INNER JOIN Orders O
ON O.CUstomerId = Cust.CustomerId
INNER JOIN Order_Items oi
ON oi.OrderId = O.OrderId
INNER JOIN books b
ON b.Isbn = oi.Isbn
INNER JOIN Categories Cat
ON c.Catid = B.catid

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Apr 25 '06 #2

P: n/a
Thanks Jens
"Jens" <Je**@sqlserver2005.de> wrote in message
news:11**********************@y43g2000cwc.googlegr oups.com...
Hi,

try this one here:
Select Cust.name,b.author,Cat.catname,oi.item_price,b.des cription
From Customers Cust

INNER JOIN Orders O
ON O.CUstomerId = Cust.CustomerId
INNER JOIN Order_Items oi
ON oi.OrderId = O.OrderId
INNER JOIN books b
ON b.Isbn = oi.Isbn
INNER JOIN Categories Cat
ON c.Catid = B.catid

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Apr 26 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.