SELECT from one table while two conditions are NOT TRUE in another table. Possible?

Question

I'm stuck on my matchmaker site. It's like eHarmony for married couples to find other couples to socialize with.

I'm trying to be a good rdba and am using a million tables then using a number of complicated joins to pull the info out I want. But, I'm stuck on one of the last ones. I can do it in the code, but I should be able to do it in the SQL. Here's the deal:

I'm showing a member profile page with their list of matches. (all the matchmaking algorithms are in and working). You can then, give someone thumbs up or down. and it marks that in a "verdict" database. I want to then refresh the member profile page and eliminate the voted on folks.

Now, I'm only showing 4 matches, so I want you to be able to thumbs up someone or thumbs down them and then they disappear and are replaced by someone else.

The problem is generating a sql statement that also checks the verdicts table.

The hard part is that your id might be in one of two columns: the voter or the votee.

SO, 

    I have these tables and I will list the columns that matter right now:

        couples 
        couples_id

        When a new person signs up, I recalculate the matches table,
    comparing every person with every other person and entering
    a compatibility quotient for each set of couples.:

        matches_couples
        matches_couples_couplea
        matches_couples_coupleb
        matches_couples_matchfactor
        (their compatibility number, I sort by this)

        When a person votes up or down on someone, 
    I enter a row for that vote.
 Who voted, about whom, and (a)ccepted or (r)ejected.:
        verdict_couples
        verdict_c_couplea (the person voting)
        verdict_c_coupleb (the person they're voting about)
        verdict_c_verdict (either 'r' for rejected or 'a' for accepted)

So, this is my current, working SQL:

SELECT
*

FROM
match_couples

WHERE
(match_couples_couple_a = '$couples_id'
or
match_couples_couple_b = '$couples_id')

ORDER BY
match_couples_matchfactor desc

LIMIT 4

But, it doesn't taken into account the voting, and will still show someone you rejected, or who has already rejected you or you approved. I want to strip out anyone who who has rejected you, or you rejected, or whom you approved.

So basically, if you're EVER the verdict_c_couplea, I don't want to include the person who was the coupleb, since you've already made a decision about them.

And if you're verdict_c_coubleb, and it's a 'r' for reject in verdict_c_verdict, I don't want to show that person either.

SO, I want some super complicated JOIN or nested EXISTS clause or something that strips those people out (that way, my LIMIT 4 still works.

IF NOT, the brute force method is to take off the limit, then for each of those people above, do a second SQL call to check the verdict table before letting them be part of the final list. But that's a major drag that I'm hoping to avoid.

I was able to get a COUNT on the number of times you approved a couple and they also approved you- a complete match. The answer to the above question, I think, is hiding in this working match count SQL but I can't even believe I got it to work:

SELECT COUNT( * ) AS matches
FROM (
verdict_couples t1
)
JOIN (
verdict_couples same
) ON ( (
t1.verdict_c_couplea = same.verdict_c_coupleb
)
AND (
same.verdict_c_verdict = 'a'
)
AND (
t1.verdict_c_verdict = 'a'
) ) 
WHERE
    same.verdict_c_couplea = '$couples_id' 
    and 
   t1.verdict_c_coupleb = '$couples_id'

Basically the ON clause criss-crosses the WHERE clause, because you're looking for:

id couplea coupleb verdict 54 US YOU accept 78 YOU US accept

That means we approved YOU and you approved US. and amazingly that works. Somewhere in there is the guts to limit my matches list to just people I haven't voted on yet and who haven't rejected ME.

Once I figure this out, I'll replicate it for individual matches, as well.

Any help on the joins?

K

Answer 1

SELECT *
FROM match_couples m
WHERE 
(m.match_couples_couple_a = '$couples_id'   # we are couple a
  AND m.matches_couples_coupleb NOT IN (    # couple b not in the list of couples which:
                                            # A. we have voted on before
                                        select verdict_c_coupleb 
                                        from verdict_couples
                                        where (verdict_c_couplea = $couples_id) 
                                        UNION
                                            # or B. have rejected us
                                        select verdict_c_couplea 
                                        from verdict_couples
                                        where (verdict_c_coupleb = $couples_id 
                                          AND verdict_c_verdict = 'r'))
OR
(m.match_couples_couple_b = '$couples_id'
  AND m.matches_couples_couplea NOT IN (select verdict_c_coupleb 
                                        from verdict_couples
                                        where (verdict_c_couplea = $couples_id) 
                                        UNION
                                        select verdict_c_couplea 
                                        from verdict_couples
                                        where (verdict_c_coupleb = $couples_id 
                                          AND verdict_c_verdict = 'r')
ORDER BY match_couples_matchfactor desc
LIMIT 4