Looking for matches?

[Matt Dav]

Hi. I'm looking to build queries which compare the data in two tables to look for mutual ticks and secondly the none mutual ticks. It's for a speed dating results processing app.

I don't really know if the table design is the best way of going about this to start with, but I just need the idea for some pointers and maybe I could then have some improved ideas on table design.

I've gone with a separate table for men and women; I played with the idea of having one table with a 'Gender' identifier (or joining the user_id to Users table which contains 'gender' column)... anyway, I digress.

tb_female_ticks

id + event_id + user_id + assign_num + tick
--------------------------------------------------
1 | 42 | 2 | 3 | 3
2 | 42 | 2 | 3 | 4
3 | 42 | 2 | 3 | 3
4 | 42 | 2 | 3 | 4


tb_male_ticks

id + event_id + user_id + assign_num + tick
--------------------------------------------------
1 | 42 | 5 | 2 | 3
2 | 42 | 4 | 5 | 3
3 | 42 | 7 | 6 | 3
4 | 42 | 9 | 10 | 6


So the assign_num is the identifier number speed daters are assigned at the event on the door, and while speed dating the clients have their assigned number on their name badge and just tick on a numbered grid if they like them.

What I am looking to gather is mutal ticks (when the man and the woman both give each other a tick) AND another result which tells the dater who ticked them but they didn't give a mutual tick to!

So far with my current skills I can only think how to gather total ticks of who ticked individual speed daters, ie:

(processing total ticks for Female speed dater at speed dating event number (event_id) 42 who has the assigned number of 3

---------------------
SELECT *
FROM men_ticks
WHERE event_id = 42 AND tick = 3
---------------------

I'd really apprechiate some pointers on this, I've been pulling my hair out over it.

Many thanks