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 2008 Forums
 Transact-SQL (2008)
 query help		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scottdg
Starting Member

18 Posts
Posted - 01/09/2013 :  12:21:56  Show Profile  Reply with Quote
Sorry if this is the wrong place for this but here goes anyway.

I have to write a query that give us all records in our database that are not current members. The relevant tables are Customer, Orders and MBR_Product. I need one column in MBR_Product that determines that the order line in Order is for a membership.

So I need all the records from Customer but I then need to remove anyone who has a current record in Orders. So if I have 3 members in my customer table - john smith, bob jones and bill lee and John smith has never been a member (no records in Orders), bob jones was a member but is not currently and bill lee is a current member - I would want my query to return only John Smith and Bob Jones.

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 01/09/2013 :  13:02:13  Show Profile  Reply with Quote
Reading your description, I am not able to figure out the relationships and the logic - for example, answers to questions such: a) how to determine if a record in Orders is current, or b) how to determine if a member is a current member etc.

If you can post the DDL for the tables and sample data to go along with it, people on the forum would be able to offer suggestions or even write queriest. Take a look at this page for help with posting: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

scottdg
Starting Member

18 Posts
Posted - 01/09/2013 :  19:27:26  Show Profile  Reply with Quote
the relevant columns in the order table are ID, begin_date, end_date, line_status and product_ID. In the mbr_product table it is product_ID and level1. Since we have different types of member products level1 could have 1 of 4 different value, I am interested in level1 = 'dues'. So I know that if I want to join all of these tables together to query on active membership I can with the following query:

select ID from orders O join mbr_product mp on o.product_id = mp.product_id

If I was then querying on current membership I would include the following:

where o.begin_date <= '1/9/13' and o.end_date >= '1/9/13' and line_status = 'A' and mp.level1 = 'dues'

What I am trying to accomplish is a query in which I get all records in the customer table that have an admit_year. Admit_year is in the customer table so admit_year is not null is all I know to accomplish that part. What I don't know is how do I remove anyone from customer that is a current member based on the above query. This way I would be left with every one in the customer table that has never been a member or was once a member but not currently.

I hope that helps. Thanks in advance.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47952 Posts
Posted - 01/09/2013 :  22:37:43  Show Profile  Reply with Quote
still not clear on how customer is related to orders table. If they've a related column you could just use

SELECT *
FROM Customers c
WHERE NOT EXISTS(select 1 from orders O join mbr_product mp on o.product_id = mp.product_id
                 where o.begin_date <= GETDATE() and o.end_date >= GETDATE() and line_status = 'A' and mp.level1 = 'dues'
                 and o.relatedcolumn = c.relatedcolumn)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
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.06 seconds. Powered By: Snitz Forums 2000