[RESOLVED] Filtering table?

mark_kccs

Hi all

Is is possible to query a table multiple times?

In this example I have 2 tables members and talents.

+----------+-----------+
| mem_id   | name      |
+----------+-----------+
| 1        | Fred      |
+----------+-----------+
| 2        | John      |
+----------+-----------+


+----------+-----------+-----------+-----------+
| tal_id   | mem_id    | Talent    | Value     |
+----------+-----------+-----------+-----------+
| 1        | 1         |Dancing    |Ballroom   | 
+----------+-----------+-----------+-----------+
| 2        | 1         |Music      |Piano      |
+----------+-----------+-----------+-----------+
| 3        | 1         |Singing    |Cabaret    |
+----------+-----------+-----------+-----------+
| 4        | 2         |Dancing    |Ballet     |
+----------+-----------+-----------+-----------+
| 5        | 2         |Singing    |Cabaret    |
+----------+-----------+-----------+-----------+

If I do a select query on (talent=dancing and value=Ballroom) or (talent=singing and value=Cabaret) i get both members. Is there a way to filter this query to return only mem_id 1?

Many thanks

NogDog

. . . WHERE talent='singing' and value='Cabaret' and mem_id=1

laserlight

hmm... but it looks like you should get both members, because they both fit the criteria. Unless you want to select on (talent=dancing and value=Ballroom) and (talent=singing and value=Cabaret).

Incidentally, read up on Database Normalization and Design Techniques.

mark_kccs

Thanks for the replys

When I try

(talent=dancing and value=Ballroom) and (talent=singing and value=Cabaret)

I get no results. I have been trying to think of how to normalize this db as each member could have many talents with many talent values but cannot get my head round it. All I can think of is 3 tables as in tbl.members, tbl.talents and tbl.values and querying 3 tables somehow. Is this the way to go?

Thanks

NogDog

Are the possible "talents" and "values" static (i.e.: you can only select them from a pre-defined set of possibilities) or are they dynamic (i.e.: you can specify any values you want for them)?

mark_kccs

Hi NogDog

sorry to be dumb but not sure. I have all the possible talents on the form and each has a possible value from a select combo box.

Thanks

NogDog

OK, we'll call that "static". Now, what exactly do you want to query for: all the talents/values for a given member, or all the members that have a particular combination of talents/values, or somethig else?

mark_kccs

All users that have a combination of talents/values as in select only users who have dancing=ballroom and singing=cabaret and music=piano and acting=stage...etc.

It could also be that the query will need to show dancing=ballroom and dancing=hip hop for the same user as each could have many talents.

NogDog

Using your current data schema:

SELECT mem_id, name
  FROM members AS m
    INNER JOIN talents AS t1 ON t1.mem_id = m.mem_id
    INNER JOIN talents AS t2 ON t2.mem_id = m.mem_id
  WHERE 
    t1.Talent = 'dancing' AND t1.Value = 'Ballroom' AND
    t2.Talent = 'dancing' AND t2.Value = 'Hip Hop'

mark_kccs

Thanks NogDog, you're a star!

Greatly appreciated.