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

 

[amorous]

Try This:

SELECT Name, [Address Line 1],[Address Line 2],
[Address Line 3],[Address Line 4],[Postal Code]
Order Number,sProductDescription, QuantityOrdered
FROM Person Inner Join Order ON
Person.ContactID=Order.ContactID
Inner Join OrderDetail ON
Order.OrderSequenceNumber=OrderDetail.OrderSequenceNumber

I hope i dint miss anything


-VJ

 

[meeble]

Thanks for replying.

The query I have now is:


SELECT Name, [Address Line 1],[Address Line 2],[Address Line 3],[Address Line 4],[Postal Code],[Order Number],sProductDescription, QuantityOrdered
FROM Person Inner Join Order ON
Person.ContactID=Order.DeliverContactID
Inner Join OrderDetail ON
Order.[Order Sequence Number]=OrderDetail.OrderSequenceNumber


It comes up with the error message:

syntax error in 'From' Clause and highlights the word order.

Any ideas?

Cheers

James

 

[amorous]

Try This:

SELECT Name, [Address Line 1],[Address Line 2],[Address Line 3],[Address Line 4],[Postal Code],[Order Number],sProductDescription, QuantityOrdered
FROM Order Inner Join Order ON
Order.ContactID=Person.DeliverContactID
Inner Join OrderDetail ON
Order.OrderSequenceNumber=OrderDetail.OrderSequenceNumber

-VJ

 

[meeble]

Hello.

This gives the error message:

Syntax Error (Missing operator)in query expression Order.ContactID=Person.DeliverContactID
Inner Join OrderDetail ON
Order.OrderSequenceNumber=OrderDetail.OrderSequenceNumber



Thanks

James

 

[amorous]

Oops Sorry:

There was a typo:

Try this:

SELECT Name, [Address Line 1],[Address Line 2],[Address Line 3],[Address Line 4],[Postal Code],[Order Number],sProductDescription, QuantityOrdered
FROM Order Inner Join Person ON
Order.ContactID=Person.DeliverContactID
Inner Join OrderDetail ON
Order.OrderSequenceNumber=OrderDetail.OrderSequenceNumber

-VJ

 

[meeble]

Hi,

I now have the expression:

SELECT Name, [Address Line 1],[Address Line 2],[Address Line 3],[Address Line 4],[Postal Code],[Order Number],sProductDescription, QuantityOrdered
FROM [Order] Inner Join Person ON
Order.DeliverContactID=Person.ContactID
Inner Join OrderDetail ON
Order.[Order Sequence Number]=OrderDetail.OrderSequenceNumber;

but this still gives the error message:

Order.DeliverContactID=Person.ContactID
Inner Join OrderDetail ON
Order.[Order Sequence Number]=OrderDetail.OrderSequenceNumber


Any idea?

Cheers

James

 

[lespaul]

Probably because you don't have all of Access's parens in your join. This might run:

SELECT Name, [Address Line 1],[Address Line 2],[Address Line 3],[Address Line 4],[Postal Code],[Order Number],sProductDescription, QuantityOrdered
FROM Order (Inner Join Person ON
Order.DeliverContactID=Person.ContactID
(Inner Join OrderDetail ON
Order.[Order Sequence Number]=OrderDetail.OrderSequenceNumber));


Leslie

 

[amorous]

Whats the error message you are getting?

I hope DeliverContactID field in Order table is same as the ContactID field in Person table

AND

[Order Sequence Number] field in Order table is same as the OrderSequenceNumber in OrderDetail table.


-VJ

 

[meeble]

That just gives 'syntax error in 'from' clause' I'm afraid...

 

[amorous]

Hmm, interesting, I dont find anything wrong with the query. Did you check all the names of your tables?

Thanks

VJ

 

[meeble]

VJ,

Are you saying that this works?

SELECT Name,[Address Line 1],[Address Line 2],[Address Line 3],[Address Line 4],[Postal Code],[Order Number],sProductDescription, QuantityOrdered
FROM [Order] Inner Join Person ON
Order.DeliverContactID=Person.ContactID
Inner Join OrderDetail ON
Order.[Order Sequence Number]=OrderDetail.OrderSequenceNumber;


It is coming up with a syntax error still.


Cheers

James

 

[amorous]

Doesnt make any difference but you dont need brackets around table name Order.

SELECT Name,[Address Line 1],[Address Line 2],[Address Line 3],[Address Line 4],[Postal Code],[Order Number],sProductDescription, QuantityOrdered
FROM Order Inner Join Person ON
Order.DeliverContactID=Person.ContactID
Inner Join OrderDetail ON
Order.[Order Sequence Number]=OrderDetail.OrderSequenceNumber;

And one more thing check the field types in your table designs. The fields which we are equating should be of same field type.

Thanks

VJ

 

[meeble]

VJ, you do need the square brackets because order is a reserved word in access.

Even if they weren't the same field type would it give a SYNTAX error?

Cheers

James

 

[amorous]

Thanks for reminding me the Order is a reserve word.

Yes, i guess it would give the error if two fields which we are equating are not of the same type.

I mean if DeliverContactID in the Order table is a string and ContactID in the Person table is an Integer, then we would get error if we equate both these fields.

Thanks

VJ

 

[meeble]

Yes, even though they have the same name, in one table it is an autonumber and in the other table it's a normal number. It has to be this way. It's the primary key in one table and this is then inserted into the other table.

What can I do?

 

[amorous]

It does not matter whether one field is auto number and the other field is a normal number.

They should be of the same type. They should be either both of type INT or they should be both of type Numeric and so on..


VJ

 

[lespaul]

'Normal number' is not a selection of data types in Access. I believe the proper choice to relate to an autonumber in one field is LONG INTEGER in the child table.

leslie

 

[meeble]

No, the types are the same. I still think there is something wrong with the syntax.

The query I have again is:

SELECT Name,[Address Line 1],[Address Line 2],[Address Line 3],[Address Line 4],[Postal Code],[Order Number],sProductDescription, QuantityOrdered
FROM [Order] Inner Join Person ON
Order.DeliverContactID=Person.ContactID
Inner Join OrderDetail ON
Order.[Order Sequence Number]=OrderDetail.OrderSequenceNumber;


This brings up the error message:


Syntax Error (Missing operator)in query expression Order.DeliverContactID=Person.ContactID
Inner Join OrderDetail ON
Order.[Order Sequence Number]=OrderDetail.OrderSequenceNumber;


It's talking about a missing operator. What can be wrong?

Cheers

James

 

[amorous]

Try This:

SELECT Name,[Address Line 1],[Address Line 2],[Address Line 3],[Address Line 4],[Postal Code],[Order Number],sProductDescription, QuantityOrdered
FROM ( [Order] Inner Join Person ON
[Order].DeliverContactID=Person.ContactID )
Inner Join OrderDetail ON
[Order].[Order Sequence Number]=OrderDetail.OrderSequenceNumber;

-VJ