Having two colums in same table reference a single column in another table

[KitCarl]

I have a table TrialClass with columns ClassID(autoincrement), ListingID, ClassID, ClassDate, PreamesureTimeID, ClosingTimeID, Judge1ID, Judge2ID

 

The PremeasureTimeID & ClosingTimeID are integer columns that both reference the ID column of the Close Table which has a CloseHour column with a list of times on the half hour.

 

The Judge1ID & Judge2ID both reference the ID column of the Person Table and I Concat columns to generate names

 

I need to write a query that gives me:

 

{ClassName, ClassDate(these first two I can handle generation the referenced field)}, PremeasureTime, ClosingTime, Judge1 (Name from Person Table), Judge2 (Name from person Table)

 

Where ListingID = 'variable'

 

I'm lost with the more than one reference to the same column more than once. Please lead me in the right direction or tell me if the table design will cause me trouble in the future. I'm hoping to learn as well as have it solved so please give an explanation and hopefully I will understand for next time. If I need to ad any more information just ask.

[jdavidbakr]

I think what you're wanting to do is use aliases

 

select Judge1.name, Judge2.name
from TrialClass, person Judge1, person Judge2
where Judge1.ID = TrialClass.Judge1ID and Judge2.ID = TrialClass.Judge2ID

 

Basically that gives you two copies of the "person" table named "Judge1" and "Judge2" that act independently of each other.

[ignace]

The Judge1ID & Judge2ID

 

Are signs of poor database design.

[Mchl]

The Judge1ID & Judge2ID

 

Are signs of poor database design.

 

Not if the number of judges is unlikely to ever change.

[KitCarl]

jdavidbakr, I was able to make it work and gain some understanding with that guidance, Thank You Very Much.

 

ignace & Mchl, thanks for the input. Actually this event does always requires two judges, but the note about poor database design has me thinking about reworking it in case I later want to include other events that have different requirements. I discovered this board a few days ago and it is obvious it contains many knowledgeable helpful people.