[SOLVED] Filtering content using multiple tables

[sonnyb]

MySQL version: 5.0.41

 

I'm developing drop-down filters for a company's internal video library. The two variables to filter by are $client and $cat. They have tables as follows (simplified):

 

table - clips:
*-------------*--------*
| ID | Client | Title  |
*-------------*--------*
| 11 |   1    | clip_1 |
| 12 |   2    | clip_2 |
| 13 |   3    | clip_3 |
*-------------*--------*

table - clients:
*-------------*
| ID | Name   |
*-------------*
| 1  |client1 |
| 2  |client2 |
| 3  |client3 |
*-------------*

table - categories:
*-------------*
| ID | Name   |
*-------------*
| 3  |cat1    |
| 7  |cat2    |
| 6  |cat3    |
*-------------*

table - clips2cat:
*------------------*
| clipID | catID   |
*------------------*
|    11  |3        |
|    11  |6        |
|    11  |7        |
|    12  |7        |
|    13  |6        |
|    13  |3        |
*------------------*

 

So clips2cat has a row for each category assigned to a clip.

 

Say someone chooses 'client2' from the dropdown, and 'cat3' from the category dropdown (so $client=2 and $cat=6).

 

What kind of query do I need to run that is going to retrieve rows from the clips table, where clips are in category 6 (matched from clips2cat) and belong to clientid 2?

 

I've been trying to use Unions but I'm getting lost. Sorry if this doesn't make any sense at all.

[sonnyb]

Nevermind, I figured it out:

 

SELECT clips.*,
       clients.*
FROM   clips,
       clients,
       clips2cat
WHERE  clips.client = clients.id
       AND clips2cat.clipid = clips.id
       AND clips2cat.catid = $cat
       AND clients.id = $client 

[fenway]

That would be easier to read & maintain using JOINs, but yes.