Solved

MS SQL: Do not include result if any order values are set to LIFETIME

Posted on 2014-03-10
2
291 Views
Last Modified: 2014-03-11
sqlOrders = "Select * From Orders Left Join Contacts On Orders.ContactID = Contacts.ContactID Where Not(Status = 'deleted') And Not(Orders.Product = 'LIFETIME') Order By Orders.ContactID Desc, Orders.OrderDate Desc"

Open in new window


I do not want any results to be returned if the value of any orders for the Contacts.ContactID are set to 'LIFETIME'.

The problem with the above query is that if a contact has 3 orders and one of them is set to 'LIFETIME' then it still returns the contact information for that user.

I use MS SQL 2008.
0
Comment
Question by:hankknight
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 11

Accepted Solution

by:
Simone B earned 500 total points
ID: 39918767
You can use a subquery:

Select * From Orders 
Left Join Contacts On Orders.ContactID = Contacts.ContactID 
Where Not(Status = 'deleted') And Contacts.ContactID NOT IN 
	(SELECT ContactID From Orders 
	WHERE Orders.Product = 'LIFETIME') 
Order By Orders.ContactID Desc, Orders.OrderDate Desc

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39919447
if the value of any orders for the Contacts.ContactID are set to 'LIFETIME'.
Don't you mean Orders.Product.are set to 'LIFETIME'?
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question