Exclude Records Based On Values in Multiple Rows

[DarkAngel]

How do you exclude records based on values in multiple rows? If a customer has purchased "BEH" in the past, exclude customer from results...

CustID	Order#	ProdDesc
13	    0001	  BEH
13	    0002	  CS
15	    0003	  CS
12	    0004	  BEH
14	    0005	  VR
15	    0006	  PS
12	    0007	  BEH

If I exclude only BEH, then I will still get CustID 13.

Desired results:

CustID	Order#	ProdDesc
15	    0003	  CS
14	    0005	  VR
15	    0006	  PS

BTW...My SQL experience - I cheat by writing the basic query in Access then convert to them to SQL commands for use in Crystal Reports and doctor the SQL further, if needed.

Thanks,
DA
___________________________________________
Crystal Reports 2008
BusinessObjects Enterprise XI 3.1
MS SQL Server 2005 (SQL Server 9.0.4035)
MS Access 2007

 

[Andrzejek]

The way I usually go about it is:

1. Get the CustIDof records that do have ProdDesc = BEH

[blue]SELECT DISTINCT CustID FROM MyTable
WHERE ProdDesc = 'BEH'[/blue]

That would give me:

CustID
12
13

2. Ask for what you want EXCUDING what you've got in step no. 1

SELECT * FROM MyTable
WHERE CustId NOT IN ([blue]SELECT DISTINCT CustID FROM MyTable
WHERE ProdDesc = 'BEH'[/blue])

Code not tested.

Have fun.

---- Andy

 

[DarkAngel]

As usual, I was trying to over complicate the solution. Thank you!

 

[DarkAngel]

Similar but different...

If a customer's primary insurance for a given program is Medic and the secondary insurance is State, can both rows be excluded from results (same for secondary/tertiary insurance)?

CustInsID	CustID	ProgID	InsDesc	PST
1	        10	    444	    State	 1
2	        10	    555	    State	 1
3	        11	    444	    BCBS	  1
4	        11	    555	    BCBS	  1
5	        12	    444	    BCBS	  1
6	        12	    555	    BCBS	  1
7	        12	    555	    Parks	 2
8	        13	    333	    Medic	 1
9	        13	    333	    State	 2
10	       13	    444	    State	 1
11	       13	    444	    BCBS	  2
12	       14	    555	    Medic	 1
13	       14	    555	    BCBS	  2
14	       14	    555	    Parks	 3
15	       15	    333	    Medic	 1
16	       16	    444	    Parks	 1
17	       16	    444	    Medic	 2
18	       16	    444	    State	 3

Desired result is to exclude CustInsID when ((PST=1 AND InsDesc="Medic") AND (PST=2 AND InsDesc="State")) or when ((PST=2 AND InsDesc="Medic") AND (PST=3 AND InsDesc="State"))

Desired result:

CustInsID	CustID	ProgID	InsDesc	PST
1	        10	    444	    State	 1
2	        10	    555	    State	 1
3	        11	    444	    BCBS	  1
4	        11	    555	    BCBS	  1
5	        12	    444	    BCBS	  1
6	        12	    555	    BCBS	  1
7	        12	    555	    Parks	 2
10	       13	    444	    State	 1
11	       13	    444	    BCBS	  2
12	       14	    555	    Medic	 1
13	       14	    555	    BCBS	  2
14	       14	    555	    Parks	 3
15	       15	    333	    Medic	 1
16	       16	    444	    Parks	 1

I am able to do this if I use derived tables and create a row for each insurance. But (there is always one of those LOL)…then I am not able to add parameter for InsDesc.

p.s. Am I using the code tag incorrectly? I thought this would keep everything lined up in the columns without having to add spaces - kind of like using Courier Font?

 

[Andrzejek]

Try my 2 step program ( ) to do what you need (again):

1. Get the CustID of records that do have your criteria
(you pretty much have it here already)
[tt]CustInsID when
((PST=1 AND InsDesc="Medic") AND (PST=2 AND InsDesc="State"))
or when ((PST=2 AND InsDesc="Medic")
AND (PST=3 AND InsDesc="State")) [/tt]

2. Ask for what you want EXCUDING what you've got in step no. 1


Have fun.

---- Andy

 

[iuianj07]

this is air SQL... but try:

SELECT 
  CustInsID,
  CustID,
  ProgID,
  InsDesc,
  PST
FROM
  MyTable
WHERE
  CustInsID Not In (SELECT DISTINCT 
                      CustInsID
                    FROM
                      MyTable
                    WHERE
                      ((PST=1 AND InsDesc="Medic")
                    AND
                      (PST=2 AND InsDesc="State")) 
                    OR
                      ((PST=2 AND InsDesc="Medic") 
                    AND
                      (PST=3 AND InsDesc="State"));

 

[DarkAngel]

Thank you Andy and iuianj07 for your quick responses! I got pulled to another project and just had a chance to try this. Adding the where clause to the query returns no records *sigh*.

 

[Andrzejek]

Your inside Select statement should give you back the CustInsID that you DO NOT want as the outcome of your entire statement. So just work on this first. You need to modify this Select:

SELECT DISTINCT 
   CustInsID
FROM
   MyTable
WHERE
  ((PST=1 AND InsDesc="Medic")
  AND
  (PST=2 AND InsDesc="State")) 
  OR
  ((PST=2 AND InsDesc="Medic") 
  AND
  (PST=3 AND InsDesc="State")

Have fun.

---- Andy