CODEWITHSUNDEEP
mysqlsqldatabase
Nishs Blemm
Nishs Blemm

Reputation: 35

multiple variable, table and parameters into one query

I have three tables:

coach

equipment

sport

Here is a link to the SQL FIDDLE page so that you may see the prepared tables to better understand what i mean. SQL Fiddle prepared tables

Brief Explanation of each field c_id = coach id, cName = coach name, e_id = equipment id, eName = name of the equipment, termUsed = number of terms the equipment has been used for so far, sports = which sport they teach (some teach multiple)

The sport table is normalized where there are single fields for each single sport that they teach. Some coaches only teach one sport and some teach multiple sports. I was advised to normalize the data, according to other posts I've seen on StakOverFlow.

Primary Keys Coach(c_id), Equipment(e_id)
Foreign Keys
Equipment(c_id) REFERENCES Coach(c_id)
Sport(c_id) REFERENCES Coach(c_id)

What i require
(result set in columns)
[Coach A_id][Coach A_name][Equipment A_id][Equipment A_Name][Common Sport ][coach B_id][Coach B_name][Equipment B_id][Equipment B_name]

I need a result set which returns two coaches ID and names, the common sport that they teach, as well as the equipment (belonging to each respective coach) matched based on their term use (equal number of terms the equipment was used for).

*This past week all I was able to figure out was this: *

SELECT DISTINCT sa.c_id, sb.c_id, sb.sport, coach.name
FROM sportsTaught sa, sportsTaught sb
JOIN coach ON coach.c_id = sb.c_id
WHERE sa.sports = sb.sports
AND sa.c_id <> sb.c_id
ORDER BY sb.c_id;

So far this was all i was able to figure out; a query which allowed me to match and display the results of two coaches who share one or more common sport that they teach. This only solves one part of the problem, as it matches the coaches with a common sport that they teach, BUT it does not also match the equipment (which is owned by individual coaches) based on the number of terms they were used for.

the goal
the point of this project, is to match coaches (who bought their own equipment) within a community centre, to trade equipment (that has been worn out equally) based off a common sport. keep in mind this is in a low-income community, so we don't have much of a budget to work with. so i am creating this database as a solution to cut costs and be more resourceful. Also to help coaches gain equipment equally worn out... so that nobody gets older equipment than what they traded. we are poor..so we try to be resourceful. a volleyball can also be used as a soccer ball to teach the kids. this isn't a professional team, its a community center outreach program for kids in low income areas.

i have been warned for making low quality posts before. i have read the necessary documents and have tried to follow all the rules and guidelines when posting to the best of my capabilities. i also have searched for a week before making this post to find answers.

Upvotes: 3

Views: 61

Answers (1)

Barmar
Barmar

Reputation: 781058

Get out of the habit of using cross products, and especially don't mix cross products with explicit JOIN. Just use JOIN consistently.

To solve your problem you need to join with the coach table twice, then join with the equipment table twice, once for each coach. Then require the termUsed to be the same, but the names different.

SELECT ca.c_id coachA_id, ca.cName coachA_name, ea.e_id equipmentA_id, ea.eName equipmentA_name, 
    sa.sports, 
    cb.c_id coachB_id, cb.cName coachB_name, eb.e_id equipmentB_id, eb.eName equipmentB_name
FROM sportTaught sa
JOIN sportTaught sb ON sa.sports = sb.sports AND sa.c_id < sb.c_id
JOIN coach ca ON ca.c_id = sa.c_id
JOIN coach cb ON cb.c_id = sb.c_id
JOIN equipment ea ON ea.c_id = ca.c_id
JOIN equipment eb ON eb.c_id = cb.c_id AND ea.termUsed = eb.termUsed AND ea.eName != eb.eName
ORDER BY ca.c_id

DEMO

Upvotes: 2

Related Questions