sql help, please =)

[lafrion]

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!

[MMcCarthy]

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!

[lafrion]

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!

[MMcCarthy]

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!

[lafrion]

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.

[MMcCarthy]

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.

[lafrion]

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!

[MMcCarthy]

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!

[lafrion]

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!).

[MMcCarthy]

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;

[lafrion]

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 =)