By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,369 Members | 966 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,369 IT Pros & Developers. It's quick & easy.

sql help, please =)

P: 9
Hey everyone,

I am currently working with sql and I am struggling to find the right coding for this question:

Show an address list (CompanyName, Address, City, Country), combining Customers and Suppliers, sorted on country and city. The result table should have an extra field in which a Customer is registered with a C (apostrophes!) and a Supplier with S. The column heading should be (AS) Cust_Supp.

so far I have this (a bit long winded but it works and shows all the correct fields)

SELECT Suppliers.SupplierID, Suppliers.CompanyName AS Suppliers_CompanyName, Suppliers.Address, Suppliers.City, Suppliers.Country, Customers.CustomerID, Customers.CompanyName AS Customers_CompanyName
FROM Suppliers INNER JOIN (Products INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Suppliers.SupplierID = Products.SupplierID;

How would I sort by country and city as well as creating an extra field AS. Any help welcome!
Oct 17 '06 #1
Share this Question
Share on Google+
10 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT Suppliers.SupplierID, Suppliers.CompanyName AS Suppliers_CompanyName, Suppliers.Address, Suppliers.City, Suppliers.Country, Customers.CustomerID, Customers.CompanyName AS Customers_CompanyName
FROM Suppliers INNER JOIN (Products INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Suppliers.SupplierID = Products.SupplierID
ORDER BY Suppliers.Country, Suppliers.City;

>as well as creating an extra field AS.

I don't know what you mean.

Hey everyone,

I am currently working with sql and I am struggling to find the right coding for this question:

Show an address list (CompanyName, Address, City, Country), combining Customers and Suppliers, sorted on country and city. The result table should have an extra field in which a Customer is registered with a C (apostrophes!) and a Supplier with S. The column heading should be (AS) Cust_Supp.

so far I have this (a bit long winded but it works and shows all the correct fields)

SELECT Suppliers.SupplierID, Suppliers.CompanyName AS Suppliers_CompanyName, Suppliers.Address, Suppliers.City, Suppliers.Country, Customers.CustomerID, Customers.CompanyName AS Customers_CompanyName
FROM Suppliers INNER JOIN (Products INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Suppliers.SupplierID = Products.SupplierID;

How would I sort by country and city as well as creating an extra field AS. Any help welcome!
Oct 17 '06 #2

P: 9
The sql I posted brings up the fieldnames needed. I am just stuck on how to create the last part of the question: The result table should have an extra field in which a Customer is registered with a C and a Supplier with S. The column heading should be (AS) Cust_Supp.

I can easily do the rest, yet this part is annoying me!
Oct 17 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT Suppliers.SupplierID, Suppliers.CompanyName AS Suppliers_CompanyName, Suppliers.Address, Suppliers.City, Suppliers.Country, Customers.CustomerID, Customers.CompanyName AS Customers_CompanyName
FROM Suppliers INNER JOIN (Products INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Suppliers.SupplierID = Products.SupplierID
ORDER BY Suppliers.Country, Suppliers.City;

You have set up a query to return a list of customers and their suppliers. Each record will have both so how do you want to distinguish between them. When do you want C and when S.

The sql I posted brings up the fieldnames needed. I am just stuck on how to create the last part of the question: The result table should have an extra field in which a Customer is registered with a C and a Supplier with S. The column heading should be (AS) Cust_Supp.

I can easily do the rest, yet this part is annoying me!
Oct 17 '06 #4

P: 9
The C has to be displayed if the field is referring to a customer and an S if referring to a supplier, for example:

ContactName Cust_supp

Bob s

Sorry for my lack of a better explanation, but if this helps this is the whole question:

Show an address list (CompanyName, Address, City, Country), combining Customers and Suppliers, sorted on country and city. The result table should have an extra field in which a Customer is registered with a C and a Supplier with S. The column heading should be (AS) Cust_Supp.
Oct 17 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Are you just trying to create one list with customers and suppliers if so your query is all wrong. You need a union query.

SELECT Suppliers.SupplierID As ID, Suppliers.CompanyName AS CompanyName, Suppliers.Address, Suppliers.City, Suppliers.Country, "S" As Cus_Supp
FROM Suppliers
UNION
SELECT Customers.CustomerID As ID, Customers.CompanyName AS CompanyName, "C" As Cus_Supp
FROM Customers;



SELECT Suppliers.SupplierID, Suppliers.CompanyName AS Suppliers_CompanyName, Suppliers.Address, Suppliers.City, Suppliers.Country, Customers.CustomerID, Customers.CompanyName AS Customers_CompanyName
FROM Suppliers INNER JOIN (Products INNER JOIN ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Suppliers.SupplierID = Products.SupplierID
ORDER BY Suppliers.Country, Suppliers.City;

You have set up a query to return a list of customers and their suppliers. Each record will have both so how do you want to distinguish between them. When do you want C and when S.
Oct 17 '06 #6

P: 9
I am trying basically to create a query which will show the customers details i.e. name, address and also showing the suppliers details name, address. Then an extra field should be created showing which is a supplier/customer represented by a C or an S. I know it is a really annoying question and even harder to explain it!
Oct 17 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Did you try the union query I gave you?

By the way you have no address field in for customer.

I am trying basically to create a query which will show the customers details i.e. name, address and also showing the suppliers details name, address. Then an extra field should be created showing which is a supplier/customer represented by a C or an S. I know it is a really annoying question and even harder to explain it!
Oct 17 '06 #8

P: 9
Yes I tried the one you gave me, I think it just needs tweeking a bit. As an error message of 'no matching fields in the customer table' or something (I am using a Dutch version so my translation is a bit bad!).
Oct 17 '06 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
SELECT Suppliers.SupplierID As ID, Suppliers.CompanyName AS CompanyName, Suppliers.Address As Address, Suppliers.City As City, Suppliers.Country As Country, "S" As Cus_Supp
FROM Suppliers
UNION
SELECT Customers.CustomerID As ID, Customers.CompanyName AS CompanyName, Customers.Address As Address, Customers.City As City, Customers.Country As Country, "C" As Cus_Supp
FROM Customers;
Oct 17 '06 #10

P: 9
Thanks I managed to get it working using this:

(SELECT ContactName, Address, City, Country, "'C'" AS Cust_Supp
FROM Suppliers)

UNION

(SELECT CompanyName, Address, City, Country, "'S'" AS Cust_Supp
FROM Customers)

ORDER BY Country, City


and thanks for your help =)
Oct 17 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.