Solved

Help with SELECT statement, need only one of each item, witch is representet in a table

Posted on 2011-03-10
6
334 Views
Last Modified: 2012-06-21
I need to list of countries, from where, a customer has active orders, but only need the value once.

The SQL select i use:
SELECT DISTINCT
SupplierCountry.cCode,
Orders.SupID,
OrdersProducts.tn
FROM (((OrdersProducts INNER JOIN Orders ON OrdersProducts.orderID = Orders.orderID)
INNER JOIN suppliers ON Orders.SupID = suppliers.SupID)
INNER JOIN  SupplierCountry  on suppliers.SupCountry =  SupplierCountry.SupCountry)  
WHERE transportNo <> '' AND Orders. Customer = ‘" & request.form("Customer ") & "' ORDER BY SupplierCountry.cCode ASC

The Tables:

Orders
Customer      orderID      SupID
Aa      1000      1
Bb      1001      1
Cc      1002      1
Dd      1003      2
Ee      1004      3

OrdersProducts
tn      ItemID      orderID
100      A      1000
100      B      1000
100      C      1000
101      D      1002
102      E      1003

Suppliers
SupID      SupCountry
1      5
2      2
3      3
4      2
5      5

SupplierCountry
cCode      SupCountry
BE      1
DE      2
FR      3
UK      4
US      5

Result:
US
US
US

I was hoping for:
US

Naan_PLS
0
Comment
Question by:NaanPLS
  • 3
  • 2
6 Comments
 

Author Comment

by:NaanPLS
ID: 35094941
Ok, For some reason is the suplierID colum missing, but It could be any value.

NaanPLS.
0
 
LVL 7

Accepted Solution

by:
Ironhoofs earned 250 total points
ID: 35094945
Try to only select the country distinct:

SELECT DISTINCT
SupplierCountry.cCode,
FROM (((OrdersProducts INNER JOIN Orders ON OrdersProducts.orderID = Orders.orderID)
INNER JOIN suppliers ON Orders.SupID = suppliers.SupID)
INNER JOIN  SupplierCountry  on suppliers.SupCountry =  SupplierCountry.SupCountry)  
WHERE transportNo <> '' AND Orders. Customer = ‘" & request.form("Customer ") & "' ORDER BY SupplierCountry.cCode ASC

0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35094947
SELECT DISTINCT SupplierCountry.cCodE
FROM Orders
INNER JOIN SupplierCountry  
ON oRDERS.SupID =  SupplierCountry.SupCountry
ORDER BY SupplierCountry.cCode ASC
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:NaanPLS
ID: 35095139
I get Error 800a0cc1 with both.
0
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 250 total points
ID: 35095434
Mine wasn't accurate anyway. I skipped over a table. It should be like this:

SELECT DISTINCT s1.cCodE
FROM Orders o1
INNER JOIN Suppliers s2
ON o1.SupID=s2.SupID
INNER JOIN SupplierCountry s1
ON s2.SupCountry =  s1.SupCountry
ORDER BY SupplierCountry.cCode ASC

This works on Access and MS SQL
0
 

Author Comment

by:NaanPLS
ID: 35095599
Sorry, You are both right, and I'm a B..... I....
The 800a0cc1 Error wasn't related to the Select, but a recordset variable.

Thanks
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now