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.
| Author |
Topic |
|
scottdg
Starting Member
20 Posts |
 Posted - 2013-01-09 : 12:21:56
|
| 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2013-01-09 : 13:02:13
|
| 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 |
 |
|
|
scottdg
Starting Member
20 Posts |
Posted - 2013-01-09 : 19:27:26
|
| 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_idIf 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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-09 : 22:37:43
|
still not clear on how customer is related to orders table. If they've a related column you could just useSELECT *FROM Customers cWHERE 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
query help