multiple variable, table and parameters into one query

Question

I have three tables:

coach

c_id
cName

equipment

e_id
eName
c_id
termUsed

sport

c_id
sports

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.

What is the sportsTaught table? It's not shown at the top. Should that be sport? — Barmar
– Barmar, Commented Jan 26, 2018 at 17:42
So coaches are only allowed to share their equipment, if it was used an equally often? — M.Wagner
– M.Wagner, Commented Jan 26, 2018 at 17:45
@M.Wagner yes they are only allowed to share the equipment if it is equally used....so same amount of terms — Nishs Blemm
– Nishs Blemm, Commented Jan 26, 2018 at 17:53

Barmar · Accepted Answer · 2018-01-26 17:52:46Z

2

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

answered Jan 26, 2018 at 17:52

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nishs Blemm Over a year ago

wow thank you, ive been trying to figure this out for a week and a half now. i kept trying to create two variable for the coach table and then tried joining them with the sportsTaught table. i see where i went wrong. so i understand everything in your answer.... i just dont understand one line : "JOIN sportTaught sb ON sa.sports = sb.sports AND sa.c_id < sb.c_id" why is sa.c_id < sb.c_id

Barmar Over a year ago

That's to avoid joining the same pair of coaches twice in opposite directions.

Collectives™ on Stack Overflow

multiple variable, table and parameters into one query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related