Subquery/JOIN nightmare

The point of this query is to display the full title of an academic program, which includes Level, Degree(s), College, Department, Major and Concentration. An example would be: Undergraduate, BSET, College of Engineering and Technology, Department of Engineering Technology, General Engineering Technology major, Electromechanical Systems concentration.

If it were as simple and consistent as it should be, then of course it would be no problem. Things can never be consistent though, right? I've got a table for each of the above-mentioned elements (besides concentrations which are always different), and they all come together in a lookup table called "programs":

id(int) Ś program_levels_id(int) Ś degrees_id(int) Ś colleges_id(int) Ś departments_id(int) Ś majors_id(int) Ś concentration(varchar)

The major headache is that for display purposes, some programs need to be combined by degree because the coursework is so similar. As an example, the graduate ME in Engineering Management is similar enough to the graduate MS in Engineering Management for them to list it as a MS/ME in Engineering Management. There are quite a few cases where this happens.

Each program is technically different, so that's one of a few reasons that they each need their own row in the programs table. I have decided to create another table that will serve to combine degrees if necessary, and store the data about each program (or combination of programs) such as a description. Here is what the program_data table looks like right now:

id(int) Ś programs_id(int) Ś programs_id2(int) Ś description, etc.

So here's the (rather messy) plan for generating a list of programs:

First it will have to tap into the program_data table. It reads the programs_id and can then use that to extract the level, degree (we'll come back to that), college, department, major and concentration for the particular program.

Now, if programs_id2 is set, that indicates that this row needs to to tie in a second program and get the degree. E.g. - When it comes across the programs_id for MS in Engineering Management it is going find that programs_id2 is set. It looks up the value in programs_id2 in the programs table and finds the ME in Engineering Management. All it really needs to know is the "ME" part, and the final result should return as "MS/ME in Engineering Management).

Somehow I need to tap into the degree abbreviation from the degrees table *through* the programs table, *through* the program_data table.

Here is a query that ignores the programs_id2 column, so it returns all of the data except that:

SELECT program_data.id,
program_levels.name AS program_level,
degrees.abbr AS degree,
colleges.name AS college,
departments.name AS department,
majors.name AS major,
programs.concentration
FROM programs LEFT OUTER JOIN departments ON programs.departments_id = departments.id
LEFT OUTER JOIN degrees ON programs.degrees_id = degrees.id,
program_data,
program_levels,
colleges,
majors
WHERE programs.program_levels_id = program_levels.id AND
programs.colleges_id = colleges.id AND
programs.majors_id = majors.id AND
program_data.programs_id = programs.id

It seems to me that the only way to do this would be to use a subquery where degree.abbr is right now. I tried that, but could not get the subquery down to the necessary single row!

+----+-----------+-------+ Ś id Ś colors_id Ś name .Ś +----+-----------+-------+ Ś 1 .Ś 2 . . . . Ś grass Ś Ś 2 .Ś 1 . . . . Ś apple Ś Ś 3 .Ś 2 . . . . Ś apple Ś Ś 4 .Ś 3 . . . . Ś sky . Ś Ś 5 .Ś . . . . . Ś glass Ś +----+-----------+-------+

+----+---------+----------+-----------------------------------+ Ś id Ś item_id Ś item_id2 Ś description . . . . . . . . . . . Ś +----+---------+----------+-----------------------------------+ Ś 1 .Ś 1 . . . Ś . . . . .Ś The grass is green . . . . . . . .Ś Ś 2 .Ś 2 . . . Ś 3 . . . .Ś Both apples, but different colors Ś Ś 3 .Ś 4 . . . Ś . . . . .Ś Sky is blue during the day . . . .Ś Ś 4 .Ś 5 . . . Ś . . . . .Ś We'll say the glass is colorless! Ś +----+---------+----------+-----------------------------------+

+----+--------+--------+-------+-----------------------------------+ Ś id Ś color1 Ś color2 Ś name .Ś description . . . . . . . . . . . Ś +----+--------+--------+-------+-----------------------------------+ Ś 1 .Ś green .Ś . . . .Ś grass Ś The grass is green . . . . . . . .Ś Ś 2 .Ś red . .Ś green .Ś apple Ś Both apples, but different colors Ś Ś 3 .Ś blue . Ś . . . .Ś sky . Ś Sky is blue during the day . . . .Ś Ś 4 .Ś . . . .Ś . . . .Ś glass Ś We'll say the glass is colorless! Ś +----+--------+--------+-------+-----------------------------------+

SELECT item_display.id, . .. . colors.name AS color1, . .. . items.name, . .. . item_display.description FROM item_display, . .. items LEFT OUTER JOIN colors ON items.colors_id = colors.id WHERE item_display.item_id = items.id; +----+--------+-------+-----------------------------------+ Ś id Ś color1 Ś name .Ś description . . . . . . . . . . . Ś +----+--------+-------+-----------------------------------+ Ś 1 .Ś green .Ś grass Ś The grass is green . . . . . . . .Ś Ś 2 .Ś red . .Ś apple Ś Both apples, but different colors Ś Ś 3 .Ś blue . Ś sky . Ś Sky is blue during the day . . . .Ś Ś 4 .Ś . . . .Ś glass Ś We'll say the glass is colorless! Ś +----+--------+-------+-----------------------------------+

SELECT item_display.id, . .. . colors.name AS color1, . . . (SELECT colors.name . .. . FROM colors, . . . . . . items, . . . . . . item_display . .. . WHERE items.colors_id = colors.id AND . .. . . . . item_display.item_id = items.id) AS color2, . .. . items.name, . .. . item_display.description FROM item_display, . .. items LEFT OUTER JOIN colors ON items.colors_id = colors.id WHERE item_display.item_id = items.id;

+----+--------+--------+-------+-----------------------------------+ Ś id Ś color1 Ś color2 Ś name .Ś description . . . . . . . . . . . Ś +----+--------+--------+-------+-----------------------------------+ Ś 1 .Ś green .Ś red . .Ś grass Ś The grass is green . . . . . . . .Ś Ś 2 .Ś red . .Ś red . .Ś apple Ś Both apples, but different colors Ś Ś 3 .Ś blue . Ś red . .Ś sky . Ś Sky is blue during the day . . . .Ś Ś 4 .Ś . . . .Ś red . .Ś glass Ś We'll say the glass is colorless! Ś +----+--------+--------+-------+-----------------------------------+

select i_d.id , c1.name as color1 , c2.name as color2 , i1.name , i_d.description from item_display as i_d left outer join items as i1 on i1.id = i_d.item_id left outer join colors as c1 on c1.id = i1.colors_id left outer join items as i2 on i2.id = i_d.item_id2 left outer join colors as c2 on c2.id = i2.colors_id

SELECT pd.id, pl.name AS program_level, CONCAT_WS('/', d.abbr, d2.abbr) AS degree, c.name AS college, dept.name AS department, m.name AS major, p.concentration FROM program_data as pd LEFT OUTER JOIN programs AS p ON p.id = pd.programs_id AND pd.type = 3 LEFT OUTER JOIN degrees AS d ON d.id = p.degrees_id LEFT OUTER JOIN programs AS p2 ON p2.id = pd.programs_id2 LEFT OUTER JOIN degrees AS d2 ON d2.id = p2.degrees_id LEFT OUTER JOIN departments dept ON p.departments_id = dept.id INNER JOIN program_levels AS pl ON p.program_levels_id = pl.id INNER JOIN colleges AS c ON p.colleges_id = c.id INNER JOIN majors AS m ON p.majors_id = m.id

Subquery/JOIN nightmare

Need to get through two levels of tables

groovenectar

aspdaddy

john_k

groovenectar

groovenectar

syber

groovenectar

john_k

syber

aspdaddy

groovenectar

groovenectar

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week