CODEWITHSUNDEEP
phpmysqlsubquery
tony9099
tony9099

Reputation: 4717

getting select from a nested query with multiple rows

below is the given table.

----------------------------------
| area   |  country  |   date     |  
----------------------------------
| a1     |     c1    | 13-01-2013 | 
----------------------------------
| a2     |     c2    | 06-01-2013 | 
----------------------------------
| a3     |     c1    | 12-01-2013 | 
----------------------------------
| a4     |     c2    | 10-01-2013 | 
----------------------------------
| a5     |     c3    | 13-01-2013 | 
----------------------------------
| a1     |     c1    | 13-01-2013 | 
----------------------------------
| a2     |     c2    | 06-01-2013 | 
----------------------------------
| a3     |     c1    | 12-01-2013 | 
----------------------------------
| a4     |     c2    | 10-01-2013 | 
----------------------------------
| a5     |     c3    | 13-01-2013 |

I want my query to do to following.

"Show the name of the countries that have the area present more than once between 2 given dates."

I tried the below query but it is giving me an error. > #1242 - Subquery returns more than 1 row

SELECT country
FROM table
WHERE area = (
SELECT area
FROM table
WHERE dateandtime > ' 13-01-2013'
AND dateandtime < '20-01-2013'
GROUP BY area
HAVING count(DISTINCT date) > 1 );

Upvotes: 0

Views: 921

Answers (3)

echo_Me
echo_Me

Reputation: 37233

you may looking for this

    SELECT country
    FROM table1

    WHERE date between ' 13-01-2013' AND  '20-01-2013'

    GROUP BY country
    HAVING count(date) > 1 ;

DEMO HERE

Upvotes: 0

Wrikken
Wrikken

Reputation: 70490

If you want a country, don't select an area:

SELECT DISTINCT country
FROM (
   SELECT country
   FROM table
   WHERE dateandtime > ' 13-01-2013'
   AND dateandtime < '20-01-2013'
   GROUP BY area
   HAVING count(DISTINCT date) > 1
) areas;

Upvotes: 0

Nick Rolando
Nick Rolando

Reputation: 26177

To compare a value to a list of values, you need to use IN. I also made a couple changes to fix your syntax.

SELECT country
FROM table
WHERE area IN (
SELECT area
FROM table
WHERE dateandtime > ' 13-01-2013'
AND dateandtime < '20-01-2013'
GROUP BY area, country, `date`
HAVING count(date) > 1 )
);

I believe the query could also be simplified to:

SELECT country
FROM table
GROUP BY area, country, `date`
HAVING count(date) > 1
AND dateandtime BETWEEN '14-01-2013' AND '19-01-2013'
);

Upvotes: 1

Related Questions