Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-03-10
6
Medium Priority
?
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

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

Assisted Solution

by:Cluskitt
Cluskitt earned 1000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

721 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