Building the required SQL statement

Q

Question Boy

Hello,

I'm having difficulty writing a query and was hoping someone could help me
out.

I have a table 'orders' which contains numerous fields, of which 3 are of
particular interest to me 'order_status', 'clientid1' and 'clientid2'. I also
have a table 'clients' which has a primary key 'client id'. What I am trying
to do is return all the data from the 'clients' table for the associated
clientid1 and clientid2 where the order_status="conf".

I get lost because of the need to get the info for clientid1 and clientid2.
If it were a simple Right Join ON I would be ok, but don't know how to handle
this one.

Thank you for the help,

QB
 
J

Jerry Whittle

Goes to show the problem of denormalization. Just think of the fun if you add
a clientid3 field.

There's a couple ways of doing it. Here's one:

SELECT clients.*, orders.clientid1
FROM clients, orders
WHERE clients.[client id] = orders.clientid1
AND orders.order_status="conf"
UNION
SELECT clients.*, orders.clientid2
FROM clients, orders
WHERE clients.[client id] = orders.clientid2
AND orders.order_status="conf" ;
 
J

John Spencer

Bad design here.

With your current structure you need to include the clients table TWICE in
your query

SELECT Clients.OrderStatus
, C1.*
, C2.*
FROM (ORDERS LEFT JOIN Clients as C1
ON Orders.ClientID1 = C1.ClientID)
LEFT JOIN Clients as C2
ON Orders.ClientID2 = C2.ClientID
WHERE Orders.Order_Status = "Conf"

OR you may need to use a UNION query if you want things to line up and you
only want one set of information from the clients table


SELECT Clients.*
FROM Clients INNER JOIN Orders
ON Clients.ClientID = Orders.ClientID1
WHERE Orders.OrderStatus = "Conf"
UNION
SELECT Clients.*
FROM Clients INNER JOIN Orders
ON Clients.ClientID = Orders.ClientID2
WHERE Orders.OrderStatus = "Conf"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
M

Marshall Barton

Question said:
I'm having difficulty writing a query and was hoping someone could help me
out.

I have a table 'orders' which contains numerous fields, of which 3 are of
particular interest to me 'order_status', 'clientid1' and 'clientid2'. I also
have a table 'clients' which has a primary key 'client id'. What I am trying
to do is return all the data from the 'clients' table for the associated
clientid1 and clientid2 where the order_status="conf".

I get lost because of the need to get the info for clientid1 and clientid2.
If it were a simple Right Join ON I would be ok, but don't know how to handle
this one.


You need to add the clients table to your query twice. Then
Join clientid1 to Clients and join Clientid2 to Clients_1
 
Q

Question Boy

To fix the normalization issue I'd need to create a table to house the
clientid1 and clientid2 with a foreign key orderid? But how to differentiate
btw the 2, there is an importance to which is 1 and 2.



Jerry Whittle said:
Goes to show the problem of denormalization. Just think of the fun if you add
a clientid3 field.

There's a couple ways of doing it. Here's one:

SELECT clients.*, orders.clientid1
FROM clients, orders
WHERE clients.[client id] = orders.clientid1
AND orders.order_status="conf"
UNION
SELECT clients.*, orders.clientid2
FROM clients, orders
WHERE clients.[client id] = orders.clientid2
AND orders.order_status="conf" ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Question Boy said:
Hello,

I'm having difficulty writing a query and was hoping someone could help me
out.

I have a table 'orders' which contains numerous fields, of which 3 are of
particular interest to me 'order_status', 'clientid1' and 'clientid2'. I also
have a table 'clients' which has a primary key 'client id'. What I am trying
to do is return all the data from the 'clients' table for the associated
clientid1 and clientid2 where the order_status="conf".

I get lost because of the need to get the info for clientid1 and clientid2.
If it were a simple Right Join ON I would be ok, but don't know how to handle
this one.

Thank you for the help,

QB
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top