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)