CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
chevchelios
chevchelios

Reputation: 13

SQL - When a column has a value from a list and a value not in that same list

Not sure the best way to word this but I'm looking for a way to specify a condition when a value in a column has at least one value in a given list AND avalue not in the same list, then that column's value should show up. An example table:

email           program
[email protected]   program1
[email protected]   program2
[email protected]   program3
[email protected]   program3
[email protected]   program4
[email protected] program1
[email protected] program2

If I have this table and a list of (program1, program2), I would like the corresponding email to show up if the programs associated with a given email match at least one in the given list AND if the given email has a program NOT in the given list

So for the table above and the given list above all we would have show up with the correct query would be:

email
[email protected]

Any help on this would be greatly appreciated. Note: this would be in Redshift/PostgreSQL

Upvotes: 1

Views: 288

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270693

I like doing this with group by and having. Here is a pretty general approach:

select email
from t
group by email
having sum( (program = 'program1')::int ) > 0 and
       sum( (program = 'program2')::int ) = 0;

In this case, "program1" is required and "program2" is not. And, you can keep adding conditions -- as many as you like.

I forget if Redshift supports the :: syntax. You can always express this using standard SQL:

having sum( case when program = 'program1' then 1 else 0 end ) > 0 and
       sum( case when program = 'program2' then 1 else 0 end ) = 0;

EDIT:

I think @dnswit is right on the parsing of the OP's question. The logic would be:

having sum( (program in ('program1', 'program2'))::int ) > 0 and
       sum( (program not in ('program1', 'program2'))::int ) > 0;

Upvotes: 2

shawn
shawn

Reputation: 113

First your Data Table is constructed wrong, you should use an unique Identifier so you can retrieve the program version you are specifying.

so your database should look like this:

> email           program1      program2       program3 
  [email protected]   ProgVersion1  ProgVersion2   ProgVersion3 
  [email protected] ProgVersion1  ProgVersion2   ProgVersion3

If you notice of the table above you can now query to get the program value you need for the specified Email. Use SQL Query, your Data Fields for your table are email, Program 1 Program 2 Program 3, when retrieving the value of the fields to be displayed, you are using redundancy you do not need to repeat the email address multiple times for each version of the program. This would not be expectable methodology.

SQL Query you can use:

instructions: you will create a parameter to use as a variable to query the data table from the list.

> CREATE PROCEDURE spLoadMyProgramVersion
> 
> @email nvarchar(50),
> 
> AS 
>
>BEGIN 
>SELECT program1,program2,program3
>FROM  MyTableName 
>WHERE (email LIKE @email) RETURN

This will allow you to load all your program version in a list by just specify the email address you want to load, this is a loading stored procedure just use it when you make a SQLCommand Object you can call your stored procedure.

Upvotes: -1

Jon Cluff
Jon Cluff

Reputation: 109

if you just want a single list of emails no matter how many times they are on the list by having multiple programs it is just select distinct email from tablename

Upvotes: 0

Related Questions