Building the required SQL statement

[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

 

[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" ;

 

[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
..

 

[Marshall Barton]

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

 

[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.

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.

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