Write an SQL statement to list all addresses which are shared by multiple suppliers?

Question

A store has a database where details of various suppliers are managed along with the items that they stock. The tables used include

a) Suppliers: Has details on the suppliers (Name, email and phone number).

b) Item-suppliers: Has details on how the supplier supplies for various items. The same supplier would have varying delivery lead times, prices, discounts, minimum and maximum order quantity for various items. Likewise the table stores the number and value of items he has supplied to date.

c) Addresses: This has details of the various addresses for suppliers. This gets used in the supplier address table.

d) Supplier Address: Stores details of the various addresses stored about the supplier. The address types are picked from the Ref_Address Types table.

e) Ref_Address Types: This table contains details of various address types and their description- General, HeadQuarters, Warehouse and Unknown.

f) Inventory Items: This table has details on the various brands and the level at which reorder must be done as also the reorder quantity.

g) Brands: This table has information on the various brands mapped onto an item
Total quantity of items delivered by supplier to date= sum of total quantity_supplied_to _date for all items supplied by them.

Total value supplied across items delivered by supplier to date= sum of value_supplied_to_date for all items supplied by them.

A supplier would supply multiple items. Likewise the data base contains information on multiple addresses for the same supplier (for the various address types). It is also possible for multiple suppliers to have the same address.

By using the above tables,Write an SQL statement to list all addresses which are shared by multiple suppliers. Please note that this does not include cases where the same address corresponds to two different address types for the same supplier.List address_ID, line_1, line_2, city, state, zip_code.
Write an SQL statement to list all addresses which are shared by multiple suppliers. Please note that this does not include cases where the same address corresponds to two different address types for the same supplier.List address_ID, line_1, line_2, city, state, zip_code.

Note 1: The corresponding columns in the result must be called address_ID, line_1, line_2, city, state, zip_code

Note 2: While writing SQL query, you are required to write the table name as schemaname.tablename (For example Supplier.Supplier_Addresses where Supplier is schemaname and Supplier_Addresses is tablename).


my answer
select distinct a.address_ID,a.line_1,a.line_2,a.city,a.state,a.zip_code
from Supplier.Addresses a with(nolock),
Supplier.Supplier_Addresses s with(nolock)
where a.address_ID = s.address_ID
and (select count(address_id) from Supplier.Supplier_Addresses where address_id = s.address_id) > 1

Answer 1

i hope u have both supplier name and address details in the Supplier Address table...

if yes then check this out...





select a.address_ID,a.line_1,a.line_2,a.city,a.…

from supplier_address a

group by a.address_ID,a.line_1,a.line_2,a.city,a.…

having count(a.supplier_name)>=2;



NOTE: here supplier_address is the TABLE which you have mentioned in point

and supplier_name is the column name in TABLE SUPPLIER_ADDRESS...

Answer 2

Use GROUP BY and HAVING clauses.

Answer 3

i didnt get you exactly what you are asking??? do you want to get order_id s in the decreasing order of the time of delivery..???