SQL question, joining?

ryanbooz

Hey all...

Here's the deal. I'm trying to "extend" something I created for work, an evaluation form, and I'm not sure if what I want to do is possible. I tried to create the database to be as 'extendable' as possible, but I may have shot myself in the foot here.

I have tables for our course instructors, course floaters (students that help the class) and a seminar table that puts all the info together. Some seminars have more than one floater, so I have a field for up to 3 floaters (floaterID_1, floaterID_2, etc.). Now, the floaterID's reference the id of the floater table that has each floater name (and other information).

Here's the problem. I'd like to write a query that makes one row with seminar name, ID, instructor name, and each floater name. What's happening now (and I understand why) is that if more than one floater exists for that seminar, I get two rows returned. All information except for the floater name is the same. I understand I'm asking to select "floaterName", but I'm wondering if there's a way to reference multiple floaters in the same row, maybe changing their reference appropriately (floaterName_1, floaterName_2, etc.)

Does this make sense? Is it even possible. IF not, I'll write the loop to get the info as I need it, but if I can do it on the SELECT, that would be great.

Thanks all
blessings..
ryan

tomhath

Bad design. You have an array inside a row, that almost always causes problems.

If you want to keep what you have you could probably get what you want with three left joins to the floater name table, something like this should work:

select a.seminarname, b.instructorname,
c.floatername, d.floatername, e.floatername
from seminar a
inner join instructor b
on a.instructorid = b.instructorid
left join floatername c
on a.floaterID_1 = c.floaterID
left join floatername d
on a.floaterID_2 = d.floaterID
left join floatername e
on a.floaterID_3 = e.floaterID

ryanbooz

Tom...

Thanks for the initial help. I'm still a little confused (and showing my SQL learning curve still...). By your example I see five tables (a,b,c,d,e). However, I'm basically talking about three tables, (seminar, instructor, floater) and want to select multiple floaters into one result, not multiple lines. I understand that this would produce an Array of sorts, but I'd like them to come out named differently in the resulting row.

I'm not sure I understand how you're referencing the same table three times in one SELECT statement. (floatername a, floatername b, floatername c). Doesn't each join have to be performed on a unique table? Using my tables and names, I'm assuming you're saying that I need something like this (although again, I a little confused on where c,d,and e come from):

select seminar.seminarID, instructor.instructorNameFirst, c.floaterNameFirst, d.floaterNameFirst, e.floaterNameFirst
from seminar inner join instructor
on seminar.instructorID = instructor.instructorID
left join floater on seminar.floaterID_1 = c.floaterID
left join floater on seminar.floaterID_2 = d.floaterID
left join floater on seminar.floaterID_3 = e.floaterID

Any more help greatly appreciated!

Ryan

Anon

a, b, c, d, and e are all aliases for the respective tables (look at the query I posted).

The one seminar row joins with one instructor row and three floater rows (a five way join); the aliases let you pretend there are three different floater tables to join with. That's just the way this problem is solved...