CODEWITHSUNDEEP
mysqlsqlselect
user2573020
user2573020

Reputation: 21

Need Help Thinking Through Semi-Complex Query on Two Tables

I have two tables, and I want to create a SELECT pulling a single record from one table based on multiple records from another table.

Perhaps it will be clearer if I just give a sort of example.

Table: dates. Fields: dosID, personID, name, dateIn

1 | 10 | john smith | 2013-09-05
2 | 10 | john smith | 2013-01-25

Table: cards. Fields: cardID, personID, cardColor, cardDate

1 | 10 | red    | 2013-09-05
2 | 10 | orange | 2013-09-05
3 | 10 | black  | 2013-09-05
4 | 10 | green  | 2013-01-25
5 | 10 | orange | 2013-01-25

So what I want is to only select a record from the dates table if a person did not receive a "red" card. The closest I have come is something like:

SELECT name, dateIn FROM dates, cards 
WHERE dates.personID = cards.personID AND cardColor != 'red' AND dateIn = cardDate;

But for this query the 2013-09-05 date-of-service would still be pulled out because of the "orange" and "black" cards given on the same day.

I have tried searching but I am not even sure how to properly describe this issue and so my Google-fu has failed me. Any help or suggestions would be very much appreciated.

Upvotes: 0

Views: 86

Answers (2)

Andrew
Andrew

Reputation: 8758

I'm a bit confused, you're getting the non-red 2013-09-05 rows back.

http://sqlfiddle.com/#!2/89260

I tried it using an inner join (as you wrote it), and an outer join. Same results.

EDIT: Sorry, misunderstood your post. eggyal's answer looks like a winner to me.

Upvotes: 0

eggyal
eggyal

Reputation: 125975

The easiest to understand version would filter using NOT EXISTS:

SELECT name, dateIn
FROM   dates
WHERE  NOT EXISTS(
         SELECT *
         FROM   cards
         WHERE  cards.personID = dates.personID
            AND cards.cardDate = dates.dateIn
            AND cards.cardColor = 'red'
       )

See it on sqlfiddle.

Upvotes: 2

Related Questions