How to use select inside another select (Sub-Query) in mysql query?

Question

I need to use select id inside another select but I don't know how can do it? My idea is something like this:

$query10 = "SELECT id FROM $database_table WHERE UTCdate >= '{$from_date}' AND satellite_derived_time >= CAST('{$from_time}' AS UNSIGNED)";

$query20 = "SELECT id FROM $database_table WHERE UTCdate >= '{$to_date}' AND satellite_derived_time >= CAST('{$to_time}' AS UNSIGNED)";


$query = "SELECT * FROM $database_table WHERE id BETWEEN '$query10' AND '$query20'";

Barmar · Accepted Answer · 2014-05-17 08:58:10Z

1

Use JOINs

SELECT t.*
FROM $database_table AS tfrom
JOIN $database_table AS tto
JOIN $database_table AS t ON t.id BETWEEN tfrom.id AND tto.id
WHERE tfrom.UTCdate >= '$from_date' AND tfrom.satellite_derived_time >= CAST('$from_time' AS UNSIGNED)
AND tto.UTCdate >= '$to_date' AND tto.satellite_derived_time >= CAST('$to_time' AS unsigned)

or use subqueries:

SELECT *
FROM $database_table
WHERE id BETWEEN (SELECT id FROM $database_table WHERE UTCdate >= '{$from_date}' AND satellite_derived_time >= CAST('{$from_time}' AS UNSIGNED))
             AND (SELECT id FROM $database_table WHERE UTCdate >= '{$to_date}' AND satellite_derived_time >= CAST('{$to_time}' AS UNSIGNED))

answered May 17, 2014 at 8:58

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

UserMat Over a year ago

Query failedsubquery returns more than one row.

UserMat Over a year ago

I think there is a problem and it shows me above message. I used second method (subqueries).

Barmar Over a year ago

Your original code implies that there can only be one value in $query10 and $query20. How are multiple IDs supposed to be handled? Your original code doesn't make sense if there can be multiple IDs.

UserMat Over a year ago

Yes, there can be just one value in $query10 and $query20

Barmar Over a year ago

The error message says that they're returning more than one value.

|

Collectives™ on Stack Overflow

How to use select inside another select (Sub-Query) in mysql query?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related