MySQL - Two tables and ordering

MySQL: Ordering data from one table by information on other

[deMorte]

On the application I'm working on I have two tables, one containing data about a subjects and other containing dates and times relating to these subjects.

From these tables I'd like to query the subjects from subject-table and the date that is the nearest in the future from this day from the date-table. The subjects should be ordered by date (Note: This was posted 19.2.2008):

Subject 1 Date 01.03.2008, Time 12.00. (Nearest to the future) 
Subject 3 Date 01.03.2008, Time 13.00. (Next to the future) 
Subject 2 Date 01.01.2008, Time 10.00. (Date in the past) 
Subject 4 Date NOT SET 

This is the MySQL statement I'm currently using (somewhat abstracted)

SELECT DISTINCT s.id, s.type, s.name, s.status 
FROM subject s, date d 
WHERE s.type='".$sType."' AND s.status=1 
ORDER BY d.date, d.time 

I also want to print out only the subjects of certain type and only active ones. The problem is that the Subjects can have multiple dates (Subject 2 has dates 01.01.2008, 01.03.2008, 02.03.2008) but the one printed is the first inserted (ie. has the lowest automatic id in date table).

If anyone has any ideas, I'd be grateful.

[phparion]

SELECT DISTINCT s.id, s.type, s.name, s.status
FROM subject s, date d
WHERE s.type='".$sType."' AND s.status=1
ORDER BY d.date, d.time DESC

did you check this

[deMorte]

That was not the problem but with your help I found the real issue.

I was querying the date I was printing in a different place and there the order was based on the autonumber and not on the date.

Sometimes I wonder how I manage to feed myself.

Thanks for the help.

[deMorte]

I found another problem relating to my previous post. As said I managed to figure out why the wrong date was printed but later discovered that the subjects are infact not arranged by dates.

I managed to fix this and I'm currently using LEFT JOIN on the tables

SELECT DISTINCT s.id, s.type, s.name, s.status 
FROM subject s  
LEFT JOIN date d  
ON s.id = d.subject_id 
WHERE s.type='".$sType."' AND s.status=1  
ORDER BY d.date, d.time DESC"; 

Now I get the subjects with dates in right order.

The problem I'm having currently is that the subject that does not have a date is printed first and the subject that has date in the past is after this. Last comes the subjects with dates in the future. As such:

Subject 4 Date NOT SET 
Subject 2 Date 01.01.2008, Time 10.00. (Date in the past)  
Subject 1 Date 01.03.2008, Time 12.00. (Nearest to the future)  
Subject 3 Date 01.03.2008, Time 13.00. (Next to the future) 

I'd like the order to be S1, S3, S2, S4. If I change the date ordering to descending (ascending is the default, I think) the dates most far from this day are first. And this is also not what I want.

Ideas would be appreciated.

[phparion]

first of all I noticed you have two columns date and time in the same table that you use in ORDER BY clause... why do you need two similar entities for each record? wouldn't DATETIME works in mysql for both?

secondly, you are ordering by DATE and TIME therefore the subjects which do not have any date will appear first. you got to mention date for them. If you are using datetime column to order your data then you MUST STORE date and time for each record...

one more thing, you are using DATE as column name in your table, I am confused why doesn't mysql produce an error because I think it is reserved word... or it is reserved in php and not mysql?

[deMorte]

I'm quite sure date is reserved but I abstracted the table a little and didn't take that into consideration. Sorry about that.

The answer to your first observation is similar. The "time" (which I'm quite sure is reserved also) is actually a starting time and there is an ending time. Times and dates in the "date" -table are input by users. Users don't have to input any dates on their subjects. I do use datetimes on timestamps that aren't edited by users.

I was hoping that the subjects could be ordered the way I want with one miraclous MySQL query. Now I'm thinking of using two separate querys, the first one gets all the subjects that have a date in the future and the second one gets all the subjects. After this I'll compare the Id's on the returned selections and first print the subjects with dates (from the first query) and after that the others (minus the ones with dates) in some order.

If someone managed to piece together what I'm doing and has better ideas, I'm all ears.

[edited by: deMorte at 9:22 am (utc) on Feb. 21, 2008]