How can I use FIND_IN_SET within a JOINED table?

问题:

I have 2 tables:

• user: names of registered blood donors with their blood type.
• blood: contains blood types and blood compatibilities. e.g.: Blood id 5 (A-) can receive blood donations from 5 (A-) and 6 (O-).

I have to build a search form that retreives potential blood donors. The form allows the user to search for specific blood type donors. If I search for blood type 5 (A-), the query should return all users that have 5 (A-) and 6 (O-) blood types, and the resulting list should display both the name and the blood type of each user.

I've tried the following:

SELECT user.name, blood.name AS blood FROM user JOIN blood ON blood.id_blood = user.id_blood WHERE user.id_blood = 5

This only brings other users that have the exact blood type as searched, but not all the compatible blood types.

Then, I tried this other query. This will search all compatible blood types but the results are also filtered by the blood type (blood type 6 records should also be displayed because the receive column of blood id 5 contains 5,6.)

SELECT user.name AS name, blood.name AS blood FROM user JOIN blood ON blood.id_blood = user.id_blood WHERE FIND_IN_SET(5, blood.receive)

...but for some reason it's not displaying the correct rows. I'm seeing some A+, AB+, A- records, and there should only be A- and O- records.

Here are tables' structures in jsfiddle.

回答1:

You need to put the find_in_set() in the ON clause:

SELECT u.name AS name, b.name AS blood
FROM user u JOIN
     blood b
     ON FIND_IN_SET(u.id_blood, b.receive) > 0;

However, you should really have a separate table with one row per blood type and each type that can be received. This is called a junction table. Storing lists in comma-separated strings is not the SQL way of doing things: tables are.

EDIT:

Like this:

SELECT u.name AS name, b.name AS blood
FROM user u JOIN
     blood b
     ON FIND_IN_SET(u.id_blood, b.receive) > 0
WHERE u.id_blood = 5;