CODEWITHSUNDEEP
sql
dotsent12
dotsent12

Reputation: 137

SELECT query with combined conditions

SQL noobie here, tried my luck googling around, but came up empty or didn't know the proper keyword. Still, feeling quite awkward between all those advanced questions, however still hopeful to get a solution and learn.

Let's suppose we have a table representing participants for different teams for a children sports tournament.

Participant table:

enter image description here

Our goal is to select out participants that have chosen a WRONG team. Let's suppose that the conditions for the teams are as such:

team Yellow = boys with age 12;
team Red = girls with age 13;
team Blue = boys with age 11;

That would mean that the incorrect registrants are Sarah (incorrect gender, correct age), Jack (incorrect gender and age) and Mary who all should therefore be included in the result of the query.

However I'm struggling with creating a SQL query that would consider conditions from multiple fields (comparing team towards gender and age at the same time) + having more than one set of comparison done at the same time (looking for incorrect participants from 3 teams at the same time).

Help is much appreciated!

Upvotes: 1

Views: 145

Answers (5)

Disillusioned
Disillusioned

Reputation: 14842

There are a few things you haven't mentioned about your team restrictions:

  • Can you have different combinations of Age and Gender for the same teams?
  • Can any of the same Age and Gender combinations match multiple teams?
  • Would the valid teams cover all permutations of of participant ages and genders?

Since that's not stated, I'm going to provide a generic solution that makes it easy to extend the valid teams. And the query will return all participants that don't match at least one valid team. (NOTE: It may be that there is no valid team for a particular participant.)

Approach:

  • Put valid combinations in a temporary (or even persistent) table of some sorts (I'll use a CTE).
  • Select all participants where you cannot find a matching Age, Gender and Team in the "Valid Teams" table.

The CTE for ValidTeams below could easy be replaced with a table if your RDBMS doesn't support CTE's. NOTE: If there are many permutations of valid gender/age/team a separate table will be better.

;WITH ValidTeams AS (
    SELECT  'Yellow' AS Team, 'M' AS Gender, 12 AS Age
    UNION ALL SELECT 'Red', 'F', 13
    UNION ALL SELECT 'Blue', 'M', 11
)
SELECT  Name, Gender, Age, Team AS InvalidTeam
FROM    Participants p
WHERE   NOT EXISTS (
    SELECT  *
    FROM    ValidTeams v
    WHERE   v.Gender = p.Gender
        AND v.Age = p.Age
        AND v.Team = p.Team
    )

Upvotes: 0

user330315
user330315

Reputation:

You didn't state your DBMS so this is ANSI SQL:

Just select all rows that do not comply with any of the rules:

select *
from participants
where (team, gender, age) not in (  ('Yellow', 'M', 12), 
                                    ('Red', 'F', 13), 
                                    ('Blue', 'M', 11) );

Online example: http://rextester.com/ZTEON26060

Upvotes: 4

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

You can use a combination of or and and to do this.

select * from yourtable
where (team ='Yellow' and not (gender = 'M' and age = 12))
or (team ='Red' and not (gender = 'F' and age = 13))
or (team ='Blue' and not (gender = 'M' and age = 11))

Upvotes: 0

roman
roman

Reputation: 117530

Main thing here is you have to convert your team rules into some kind of proper data structure. You can put it into the table, or use derived table, like this:

select *
from participants as p
where
    not exists (
        select *
        from (values
              ('Yellow', 'M', 12),
              ('Red', 'F', 13),
              ('Blue', 'M', 11)
        ) as t(Team, Gender, Age)
        where
            t.Team = p.Team and
            t.Gender = p.Gender and
            t.Age = p.Age
    )

Or you can check for correct team and then compare with current team:

select
    p.*, t.Team as Correct_Team
from participants as p
    left join (values
        ('Yellow', 'M', 12),
        ('Red', 'F', 13),
        ('Blue', 'M', 11)
    ) as t(Team, Gender, Age) on
        t.Gender = p.Gender and
        t.Age = p.Age

sql fiddle demo

Upvotes: 1

Aritra Bhattacharya
Aritra Bhattacharya

Reputation: 780

You can try this : 

Select 
Name,
Gender,
Age,
Team AS Chosen_team,
Case when Gender='M' and Age=12 Then 'Yellow'
     when Gender='F' and Age=13 then 'Red'
     when gender='M' and Age=11 then 'Blue'
End as Ideal_team,
Case when Chosen_team <> Ideal_team then 'FALSE' ELSE 'TRUE'
from your_table;

Now select the records with value false. You will get your list.

Upvotes: 0

Related Questions