Join 3 mySQL tables in one query

I've spent last hours trying to find out how to generate an appropiate MySQL query to access my data. This is my sql table structure:

CREATE TABLE IF NOT EXISTS `record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
)

CREATE TABLE IF NOT EXISTS `clip` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `record` int(11) NOT NULL,
  `data` int(11) NOT NULL,
)

CREATE TABLE IF NOT EXISTS `data` (
  `clip` int(11) NOT NULL,
  `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `value` text COLLATE utf8_unicode_ci NOT NULL,
)

I have minimized tables as much as possible. record table contains client data, then clip is used to join this client with som开发者_开发问答e client parameters that are stored in data. Each client may have as much record's as he wants and each record is composed by an undetermined number of *clip*s. Then, each clip references to some data values.

Ok, the query I'm looking for is one that given a sheet, a data *name* and a value it gets the last (date) *client*s that accomplishes the condition name=*given_name* AND value=*given_value*. It is, I'm looking for the clients that accomplishes that condition in the LAST record stored in the database.

I've arrived to this sentence:

SELECT client
FROM clip, record 
WHERE clip.id IN (SELECT clip FROM `data` WHERE name='level' AND value='2')

But it returns all clients, not only the last ones and all my tests including date have been unsuccessful.

EDIT: After some try-and-error and thanks to the ideas you gave me, I've arrived to a query that seems to work, but it is really complex and I'm quite sure you can help me to simplify:

SELECT * FROM clip, data
WHERE data.clip=clip.id 
 AND record IN (SELECT id
  FROM record a
  WHERE date = (
    SELECT MAX(date)
    FROM record b
    WHERE a.client = b.client
    )
 )
 AND data.name='NAME_HERE' AND data.value='VALUE_HERE'

Well, that's not exactly what I want, I need the client ID, but it gives me the last record for this client that is enough for me.

---

If I have understood your quesiton correctly then see if this works for you:

SELECT client
FROM record, clip, data
WHERE name = "given_name" AND value = "given_value"
AND data.clip = clip.id
AND clip.record = record.id
ORDER BY date DESC
LIMIT 0,1

Updated query based on your comments:

SELECT client, MAX(date)
FROM record, clip, data
WHERE name = "given_name" AND value = "given_value"
AND data.clip = clip.id
AND clip.record = record.id
GROUP BY client

It was helpful that you have posted some data and the output required from it. I have updated the query below:

SELECT record.client
FROM clip JOIN data ON (data.clip = clip.id)
JOIN record ON (clip.record = record.id)
JOIN (
   SELECT client, MAX(date) as date
   FROM record
   GROUP BY client
) AS r ON (r.client = record.client AND r.date = record.date)
WHERE name = "given_name" AND value = "given_value"

---

 SELECT data.name,data.value,record.client FROM data
 JOIN clip on clip.id = data.clip
 JOIN record ON record.id = clip.record WHERE data.name = 'name' AND data.value = 'value' ORDER BY record.date DESC LIMIT 0,1

---

This is the solution I've found, perhaps it could be optimized, but this needs other question:

SELECT * FROM clip, data
WHERE data.clip=clip.id 
 AND record IN (SELECT id
  FROM record a
  WHERE date = (
    SELECT MAX(date)
    FROM record b
    WHERE a.client = b.client
    )
 )
 AND data.name='NAME_HERE' AND data.value='VALUE_HERE'