I have a query I am trying to do in SQL, but I don't know how to do it.

I have 4 tables (T1, T2, T3, T4) and I need to link them all togethr and get the max of a couple of fields.

Here basic descirption of what I need to do
T1 has the name of the data
T2 is used to link T1 to T3
T3 has 2 sets of dates, I need the min of one and the max of the other
T4 has a status, which is either 'RED', 'Green', 'Yellow'

Now I have T1, T2, and T3 linking properly so the min and max of the dates is getting displayed.

Sample output

Data MaxDate MinDate
D1 1/1/2007 1/1/2003

Now I need help linking T1 to T4 and having it pull the colour that I need. If any record has red I want red to show up, if any record has Yellow and not red then yellow is shown, if only green is there I want green to show up, and if no records I want it to be blank.

Actual I was just think that as long as I can get the right color being selected I can just join it to my other search.

So does any one know how to get the colours to get selected with this priority (Red - Yellow - Green). If I just do max Yellow gets a higher priority then Red, and with min Green gets the highest priority.

Thanks for any help.

I can't do blank paper SQL - post what you've used so far and I'll see if I can add the necessary bits.

Ok this is basically what I have

SELECT T_Release.name, MAX(Status.Colour) AS Colour FROM T_Release INNER JOIN T_Project ON T_Release.id = T_Project.ReleaseID INNER JOIN Status ON T_Project.id = Status.ProjectID GROUP BY T_Release.name


My problem is the fact that SQL does orders with strings alphabetically, which I don't what. I want this order
Red
Yellow
Green
NULL

I don't know how to do that.

Maybe set up another table with colors and the order you want them printed by and use the ORDER BY clause.

actually that makes sence, I never thought of that thanks