SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Mandatory records		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sridhar3004
Starting Member

31 Posts
Posted - 07/17/2012 :  07:04:24  Show Profile  Reply with Quote
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
Yak Posting Veteran

Portugal
52 Posts
Posted - 07/17/2012 :  07:58:15  Show Profile  Reply with Quote
can tou give an example?
Go to Top of Page

sridhar3004
Starting Member

31 Posts
Posted - 07/17/2012 :  08:07:32  Show Profile  Reply with Quote
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
Go to Top of Page

jleitao
Yak Posting Veteran

Portugal
52 Posts
Posted - 07/17/2012 :  08:10:52  Show Profile  Reply with Quote
give an example of wath you need in your output
Go to Top of Page

sridhar3004
Starting Member

31 Posts
Posted - 07/17/2012 :  08:13:53  Show Profile  Reply with Quote
The output should return the customer id
in this case 2

CustomerID
----------
2
Go to Top of Page

jleitao
Yak Posting Veteran

Portugal
52 Posts
Posted - 07/17/2012 :  09:29:08  Show Profile  Reply with Quote
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
Go to Top of Page

sridhar3004
Starting Member

31 Posts
Posted - 07/17/2012 :  09:41:59  Show Profile  Reply with Quote
Thank you very much for your quick response. It worked great. Again thank you.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000