MIN() with more then one result

[ckrudelux]

SELECT t.*, MIN(p.played) FROM `track` as t LEFT JOIN `play` as p ON t.id=p.t_id ORDER BY RAND()

this just gives me a one result how do I do if I want all the lowest values? example: row: 1 id: 1row: 2 id: 2row: 4 id: 3row: 2 id: 4row: 1 id: 5 should return id 1 and 5 Just notice that this doesn't return anything:

SELECT t.* FROM `track` as t LEFT JOIN `play` as p ON t.id=p.t_id WHERE p.played=NULL ORDER BY RAND()

How is it then doing a left join on rows which does not exists shouldn't the default value be used and why can't I find any then using the default value, should return all rows but I get none.

[justsomeguy]

If that query returns no rows then no rows match the WHERE condition. If there were matches then those rows would be returned. This will select all rows with the lowest value: SELECT t.*, p.played FROM `track` as t LEFT JOIN `play` as p ON t.id=p.t_id WHERE p.played IN (SELECT MIN(played) FROM play) ORDER BY RAND()

[ckrudelux]

If that query returns no rows then no rows match the WHERE condition. If there were matches then those rows would be returned. This will select all rows with the lowest value: SELECT t.*, p.played FROM `track` as t LEFT JOIN `play` as p ON t.id=p.t_id WHERE p.played IN (SELECT MIN(played) FROM play) ORDER BY RAND()

Thanks, but this won't work like I need it cause. table1:name: test, id: 1name: test, id: 2name: test, id: 3name: test, id: 4name: test, id: 5 table2:played: 1, t_id: 2, id:1played: 2, t_id: 4, id:1 lowest value of played isn't 1 but the default value 0 then I join the tables so I can't select play and just grab the lowest tryed to add a JOIN on the second select but didn't work

SELECT t.*, p.played FROM `track` as t LEFT JOIN `play` as p ON t.id=p.t_id WHERE p.played IN (SELECT MIN(`play`.played) FROM `play` RIGHT JOIN `track` ON `play`.t_id=`track`.id) ORDER BY RAND()

[justsomeguy]

I don't understand what you're saying, the lowest value is 1. There is not a record that you showed that has a value of 0. You said you wanted to get all records that have the minimum value, and that's what I showed you. If you don't want all records with the minimum value, then what do you want?

[ckrudelux]

I don't understand what you're saying, the lowest value is 1. There is not a record that you showed that has a value of 0. You said you wanted to get all records that have the minimum value, and that's what I showed you. If you don't want all records with the minimum value, then what do you want?

Correct there is no inserted record which is 0 but then doing a left join from the track table all rows which don't match up will get the default value from the play table which is 0.

CREATE TABLE `play` {`played` int(11) DEFAULT 0,`u_id` int(11),`t_id` int(11),`ut` varchar(32),id int(11) PRIMARY KEY AUTO_INCREMENT}

[justsomeguy]

Correct there is no inserted record which is 0 but then doing a left join from the track table all rows which don't match up will get the default value from the play table which is 0.

No, if you do a left or right join and there are rows which don't match in the other table then those missing columns have a value of null, not 0 or whatever else the default for one of the fields in the other table is. This is described in the 5th bullet point on this page: http://dev.mysql.com....0/en/join.html And in more detail here: http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html This is one of the features of a left or right join. If you don't want to include rows that are missing then use an inner join.

[ckrudelux]

No, if you do a left or right join and there are rows which don't match in the other table then those missing columns have a value of null, not 0 or whatever else the default for one of the fields in the other table is. This is described in the 5th bullet point on this page: http://dev.mysql.com....0/en/join.html And in more detail here: http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html This is one of the features of a left or right join. If you don't want to include rows that are missing then use an inner join.

Took a while to figure this out but I got it now on, doing conditional if not all bin played I use this query:

SELECT * FROM `track` WHERE id NOT IN (SELECT t.id FROM `track` as t LEFT JOIN `play` as p ON t.id=p.`t_id` WHERE p.u_id=1)  ORDER BY RAND()