CODEWITHSUNDEEP
mysqlsubquery
Nik
Nik

Reputation: 129

Passing the results of a Mysql query to a subquery on the same table

CREATE TABLE test (
id INT(12),
time VARCHAR(16),
group INT(2),
taken TINYINT(1),
RID int(11) NOT NULL auto_increment,
primary KEY (RID));


id | time | group | taken
---------------------------
1  | 13.00|   1   |   1
---------------------------
2  | 13.00|   2   |   0
---------------------------
3  | 14.00|   2   |   0
---------------------------
4  | 15.00|   2   |   0
---------------------------
5  | 12.00|   3   |   0

Having a table structure and sample data as above, I want to get the smallest "group" number which has not been "taken" (taken=0)

I have come with two queries :

SELECT * From `test` 
WHERE taken=0

and

SELECT * FROM `test`
WHERE `group` =  ( SELECT MIN(`group`) FROM `test` )

Can someone show me how to combine the two queries so that I can pass the results of the first query to the second query to get as below.

id | time | group | taken
---------------------------
2  | 13.00|   2   |   0
---------------------------
3  | 14.00|   2   |   0
---------------------------
4  | 15.00|   2   |   0
---------------------------

Upvotes: 0

Views: 146

Answers (3)

IanH
IanH

Reputation: 191

try this:

SELECT min(`group`) FROM (
    SELECT * FROM test
    WHERE taken = 0) 
AS t;

Upvotes: 0

Kickstart
Kickstart

Reputation: 21523

Use the sub query to get the lowest group for taken of 0. Join your main table to the results of the sub query.

Something like this:-

SELECT a.* 
From `test` a
INNER JOIN
(
    SELECT MIN(`group`) AS min_group
    FROM `test` 
    WHERE taken=0
) b
ON a.taken = b.taken
AND a.`group` = b.min_group

Upvotes: 1

ebo
ebo

Reputation: 2747

You can use the result of the first query in the second query as follows:

SELECT * 
FROM TEST 
WHERE `group` = (SELECT MIN(`group`) 
                 FROM `test` 
                 WHERE taken = 0)

Which gives you the desired result according to this SQLFiddle

Upvotes: 1

Related Questions