Help with multiple entries - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help with multiple entries

Author

Topic

techky24
Starting Member

3 Posts

Posted - 2006-06-12 : 22:22:58

Hi,

I need help desperately. The following is an example of the data that I have.

I have two tables one name Customers and the other named Orders. The tables have the following attributes

CUSTOMER
CustomerNum, CustomerName, OrderNum

ORDER
OrderNum, OrderDesc, CustomerNum

Bare in mind that the CustomerNum can have multiple customers attached to it, example a whole family (I know bad database design but it's too late to change) Let's use the following info for the tables respectively

CUSTOMER
0001 Sharon Bigbsy 1234
0001 Dale Bigbsy 1235
0001 Omar Bigbsy 1236

ORDERS
1234 Chips 0001
1235 Gatorade 0001
1236 Candy 0001

The query i'm using is select Customer.CustomerNum, Customer.CustomerName, Orders.OrderDesc from CUSTOMER, ORDERS where CUSTOMER.CustomerNum = ORDER.CustomerNum

The results are as follows

0001 Sharon Bigbsy Chips 1234
0001 Sharon Bigbsy Gatorade 1235
0001 Sharon Bigbsy Candy 1236
0001 Dale Bigbsy Chips 1234
0001 Dale Bigbsy Gatorade 1235
0001 Dale Bigbsy Candy 1236
0001 Omar Bigbsy Chips 1234
0001 Omar Bigbsy Gatorade 1235
0001 Omar Bigbsy Candy 1236

It's giving all the orders place to each customer name but I need only the following

0001 Sharon Bigbsy Chips 1234
0001 Dale Bigbsy Gatorade 1235
0001 Omar Bigbsy Candy 1236

I'm using SQL in MS Access 2003. Please disregard the abnormalities of the tables, it's someone else's work I inherited with over 6000 entried.

Any help will be greatly appreciated

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-12 : 22:28:39

your expected result is not clear, how did you come up with it?

provide the business logic, if you can't,use group by on the customernum, then query this resultset then use aggregate function like max or min on the other fields

--------------------
keeping it simple...

techky24
Starting Member

3 Posts

Posted - 2006-06-12 : 22:33:02

Hi,

The expected result is to show only the order that each customer placed with the customer number, name, order number and description. Even though it will be the same customer number. Right now the results i'm getting are all the order placed applied to each customer and I'm only looking for one.

Thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-12 : 22:42:44

select c.customernum,c.customername,o.orderdesc,c.ordernum
from customer c
join order o on c.customernum=o.customernum
and c.ordernum=o.ordernum

haven't tested it though

--------------------
keeping it simple...

techky24
Starting Member

3 Posts

Posted - 2006-06-12 : 23:00:50

Hi Jen,

it says syntax error in from clause and it highlights the join word. any idea why?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-12 : 23:03:37

because order is a keyword,

enclose it in square brackets --> [order]

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-13 : 01:35:04

Also refer point 2
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-13 : 01:51:23

it's not an ordering issue maddy,or is it? nope, i don't think so...

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-13 : 02:00:37

No it is not an ordering issue. It will just pick first N rows from each group

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources