Mandatory records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Mandatory records

Author

Topic

sridhar3004
Starting Member

34 Posts

Posted - 2012-07-17 : 07:04:24

This is probably the most easiest of queries for many of you'll. but am stuck and hence need help

I've a customer master for whom I've 2 address types (shipping and billing) to be entered compulsory

there is a customer table and there is the address table with the customer id being the foreign key

I need a query which returns to me the customers for whom even 1 of the 2 address type is missing.

Warm Regards
Sridhar

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-17 : 07:58:15

can tou give an example?

sridhar3004
Starting Member

34 Posts

Posted - 2012-07-17 : 08:07:32

Customer Master table
---------------------
ID Name
1 ABC
2 EFG

Address Table
-------------
Line1 Line2 CustomerID AddressType
blah blah 1 Shipping
blah blah 1 Billing
hdshf dfjsdf 2 Shipping

I want the query to return customer 2 since 1 addresstype .i.e. billing is missing from the address table for 2

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-17 : 08:10:52

give an example of wath you need in your output

sridhar3004
Starting Member

34 Posts

Posted - 2012-07-17 : 08:13:53

The output should return the customer id
in this case 2

CustomerID
----------
2

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-17 : 09:29:08

SELECT
customerID, COUNT(1) nr_records
FROM adress_table A
inner join customer_table C
ON a.CustomerID = C.ID
group by customerID
having COUNT(1) < 2