Complex SQL query probably involving joins 1

[meeble]

I have the following tables.

table Order

OrderSequenceNumber
Order Number
ContactID


table OrderDetail

OrderSequenceNumber
sProductDescription
QuantityOrdered


table Person

ContactID
Name
Address Line 1
Address Line 2
Address Line 3
Address Line 4
Postal Code



Where more than one table has a heading with the same name, these are the same value inserted into the different tables.


I don't know if it is possible to do this with one SQL query. What I want to do is as follows.

With the results put into a separate table when the query is run, I want on each line, all the details from table Person (except contactID), the order number associated with that person from table order, and the product description(s) and quantity ordered for each product from table orderDetail.


For example, running the query would give me in one line

Fred Bloggs, 110 high street, Hackney Downs, hackney, London, E12 4rt, FRT36773, ProdDesc1, 1, ProdDesc2, 1, ProdDesc3, 2

Can this all be done in one SQL query?

Cheers

James

 

[meeble]

Thanks!! That works fine.

I would like to expand the query slightly.

I have now:

SELECT Name, ContactID, [Address Line 1], [Address Line 2], [Address Line 3], [Address Line 4], [Postal Code], [Order Number], sProductDescription, QuantityOrdered, Order.Status
FROM ([Order] INNER JOIN Person ON [Order].DeliverContactID=Person.ContactID) INNER JOIN OrderDetail ON [Order].[Order Sequence Number]=OrderDetail.OrderSequenceNumber where Order.Status !='N';


The is a syntax error with the WHERE clause. What is wrong with that?

Cheers

James

 

[amorous]

Try This:

SELECT Name, ContactID, [Address Line 1], [Address Line 2], [Address Line 3], [Address Line 4], [Postal Code], [Order Number], sProductDescription, QuantityOrdered, Order.Status
FROM ([Order] INNER JOIN Person ON [Order].DeliverContactID=Person.ContactID) INNER JOIN OrderDetail ON [Order].[Order Sequence Number]=OrderDetail.OrderSequenceNumber where Order.Status <> 'N';

Hope that works.
-VJ

 

[meeble]

Thanks for your help. You've helped a lot

 

[amorous]

No problem. I am glad that it worked for you.

Thanks for the star.

-VJ