CODEWITHSUNDEEP
sqljoinsqliteforeign-keys
Marcatectura
Marcatectura

Reputation: 1695

SELECT get values from one parent table given values from another parent table using junction table

I've got a DB with two parent tables which are referenced by foreign keys in a junction table, and I'm scratching my head trying to figure out how I can obtain all associated rows from one parent table given a value from another. For example, with the following tables and columns:

schoolA_courses:
id 
course_prefix
course_number 

schoolB_courses: 
id
course_prefix 
course_number

schoolB_equivalencies:
equivalency_id
schoolA_id
schoolB_id
FOREIGN KEY (schoolA_id) REFERENCES schoolA_courses (id)
FOREIGN KEY (schoolB_id) REFERENCES schoolB_courses (id)

And rows that look like this:

schoolA:
1, MATH, 101
2, SOCI, 100
3, HIST, 211

schoolB:
1, MATH, 100
2, MATH, 110
3, ENGL, 210

schoolB_equivalencies:
1, 1, 1 (school A's MATH 101 equivalent to school B's MATH 100)
2, 1, 2 (school A's MATH 101 equivalent to school B's MATH 110)

Given a schoolA course prefix and number (let's say MATH 101), how can I structure a SELECT query to find all schoolB course prefix/number pairs listed as equivalent in the schoolB_equivalencies table? For example, with my MATH 101 dummy course, how would I get 

MATH 100
MATH 110

Upvotes: 0

Views: 50

Answers (1)

CL.
CL.

Reputation: 180260

This query gets information about the original course:

SELECT *
FROM schoolA
WHERE course_prefix = 'MATH'
  AND course_number = 101;

This query gets information about the corresponding rows in the junction table:

SELECT *
FROM schoolB_ueqivalencies
WHERE schoolA_id = (SELECT id
                    FROM schoolA
                    WHERE course_prefix = 'MATH'
                      AND course_number = 101);

And this query gets information about the corresponding rows in schoolB:

SELECT *
FROM schoolB
WHERE id IN (SELECT schoolB_id
             FROM schoolB_ueqivalencies
             WHERE schoolA_id = (SELECT id
                                 FROM schoolA
                                 WHERE course_prefix = 'MATH'
                                   AND course_number = 101));

Upvotes: 1

Related Questions