How to do a Select on my problem? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

How to do a Select on my problem?

Author

Topic

matkwan
Starting Member

36 Posts

Posted - 2003-06-24 : 03:24:19

Hi, I have 2 tables:

* tbl_Customers
cust_id
cust_name

* tbl_Orders
ord_id
ord_CustA_id
ord_CustB_id
ord_CustC_id

How do I do a select to show 3 Customer name where ord_id = 3 ?

Matthew

Andy Verity
Starting Member

12 Posts

Posted - 2003-06-24 : 03:44:23

Okay we have a possible design flaw here.

If you really have a situation where a Customer can have many orders and an Order can have many customers you really need to normalise the tables by setting up a reference table between the tbl_Customers and tbl_Orders.

i.e.

* tbl_Customers
cust_id
cust_name

tbl_CustomerOrders(for want of a better name)
cust_id
Order_id

* tbl_Orders
ord_id

This should cope with any number of Customers to Order.
I created this in query builder to give you an idea of what the Select statement would like.

SELECT tblCustomer.CustomerID
FROM tblOrder INNER JOIN (tblCustomer INNER JOIN tblOrderCustomer ON tblCustomer.CustomerID = tblOrderCustomer.CustomerID) ON tblOrder.OrderId = tblOrderCustomer.OrderID
WHERE (((tblOrder.OrderId)=3));

Will this sort out your problem?

matkwan
Starting Member

36 Posts

Posted - 2003-06-24 : 09:35:27

Thank Andy, I agree with you that there is a design flaw :)
I have fixed it.

I have a question, by using your Select Statement, I will return me 3 Customer Records if there are 3 customers associated to the Order.

Is it possible to return a record containing 3 Customer Name ?

Matthew

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-24 : 11:24:02

Just the "Customers" table to the query designer 3 times, the duplicate ones with have "_1" and "_2" added to the table names.

then just join CustA to one copy of the customers table, CustB to the other, etc.

Use a left outer join if some orders don't have all 3 customers.

- Jeff

Andy Verity
Starting Member

12 Posts

Posted - 2003-06-25 : 03:27:38

Sorry I am not very quick on the uptake at the moment. Are you asking how to return the third customer. If so what is special about the third customer, why do specifically need to return this one?

Andy

quote:

Thank Andy, I agree with you that there is a design flaw :)
I have fixed it.

I have a question, by using your Select Statement, I will return me 3 Customer Records if there are 3 customers associated to the Order.

Is it possible to return a record containing 3 Customer Name ?

Matthew

Lee-Z
Starting Member

7 Posts

Posted - 2003-06-27 : 07:30:04

I think this is what you want:

the query returns
order - customername
3 - CustomerA
3 - CustomerB
3 - CustomerC

and you want it to show as:
3 - CustomerA, CustomerB, CustomerC
?

I don't think you can do that in the query itself, you would have to do that on your output, for instance report...

Lee-Z

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources