mysql数据库匹配_匹配MySQL数据库中的对

I don't know of the title is any good, but I'm trying to figure out a SELECT statement for a MySQL database that gives me matching items. I have two tables.

Example:

Offers

------

FK_User_ID | FK_Skill_ID | (other columns that are not relevant)

1 1

2 2

Requests

--------

FK_User_iD | FK_Skill_ID | (other columns that are not relevant)

1 | 2

2 | 1

You see what I mean? User 1 has skill 1 and seeks skill 2 -- User 2 has skill 2 and seeks skill 1. We have a match!

Say I'm user 1. How can I get the users and skills that have a Offer/Request match with me?

As user 1, I would like to retrieve the following data:

FK_User_ID | FK_Skill_ID | Type

2 1 Request

2 2 Offer

I hope my problem is clear. Any help is greatly appreciated.

More details: A user can have more than one skill and can request more than one skill. A match does not mean every skill needs to be matched, but at least one. And I would like to know which one then. It's only about skills that match.

解决方案SELECT FK_User_ID, FK_Skill_ID, 'Offer' AS Type FROM offers WHERE FK_Skill_ID IN (SELECT FK_Skill_ID FROM requests WHERE FK_User_ID = 1)

UNION

SELECT FK_User_ID, FK_Skill_ID, 'Request' AS Type FROM requests WHERE FK_Skill_ID IN (SELECT FK_Skill_ID FROM offers WHERE FK_User_ID = 1)

This query will return a list of requests/offers for user with ID 1. It will return all the offers user 1 has requests for and all requests user 1 has offers for. If you can update question with more details about your data, as @Chris suggested then I might be of more help.

UPDATE:

If you need a match on both request and offer then it will be a bit more work.

SELECT matching_offers.*, 'Offer' as type FROM

(SELECT FK_User_ID, FK_Skill_ID FROM offers WHERE FK_Skill_ID IN (SELECT FK_Skill_ID FROM requests WHERE FK_User_ID = 1)) AS matching_offers INNER JOIN

(SELECT FK_User_ID, FK_Skill_ID FROM requests WHERE FK_Skill_ID IN (SELECT FK_Skill_ID FROM offers WHERE FK_User_ID = 1)) matching_requests ON matching_offers.FK_User_ID=matching_requests.FK_User_ID

UNION

SELECT matching_requests.*, 'Request' as type FROM

(SELECT FK_User_ID, FK_Skill_ID FROM offers WHERE FK_Skill_ID IN (SELECT FK_Skill_ID FROM requests WHERE FK_User_ID = 1)) AS matching_offers INNER JOIN

(SELECT FK_User_ID, FK_Skill_ID FROM requests WHERE FK_Skill_ID IN (SELECT FK_Skill_ID FROM offers WHERE FK_User_ID = 1)) matching_requests ON matching_offers.FK_User_ID=matching_requests.FK_User_ID

This will be very unefficient but should work. Maybe someone whos SQLfu is better than mine can improve it)