Performing a Query on a Result from Another Query

Performing a query on a result from another query?

Usually you can plug a Query's result (which is basically a table) as the FROM clause source
of another query, so something like this will be written:

SELECT COUNT(*), SUM(SUBQUERY.AGE) from
(
  SELECT availables.bookdate AS Date, DATEDIFF(now(),availables.updated_at) as Age
  FROM availables
  INNER JOIN rooms
  ON availables.room_id=rooms.id
  WHERE availables.bookdate BETWEEN '2009-06-25' AND date_add('2009-06-25', INTERVAL 4 DAY) AND rooms.hostel_id = 5094
  GROUP BY availables.bookdate
) AS SUBQUERY

How to query on a result of another query?

You can use EXISTS:

SELECT *
FROM   customer c
WHERE  EXISTS (SELECT 1
               FROM   depositor d
               WHERE  c.customer_name = d.customer_name)
OR     EXISTS (SELECT 1
               FROM   borrower b
               WHERE  c.customer_name = b.customer_name)

or:

SELECT *
FROM   customer c
WHERE  EXISTS (
          SELECT 1
          FROM   depositor d
          WHERE  c.customer_name = d.customer_name
          UNION ALL
          SELECT 1
          FROM   borrower b
          WHERE  c.customer_name = b.customer_name
       )

or IN:

SELECT *
FROM   customer
WHERE  customer_name IN ( SELECT customer_name FROM depositor )
OR     customer_name IN ( SELECT customer_name FROM borrower )

or:

SELECT *
FROM   customer
WHERE  customer_name IN (
         SELECT customer_name FROM depositor
       UNION ALL
         SELECT customer_name FROM borrower
       )

How to query on another query result?

You can always do something like:

select emp_code, min(emp_name) as emp_name, sum(hours)
from (
  <your original query here>
) as e
group by emp_code
order by emp_name;

Using a query result in another query

Firstly, Your code is open to SQL injection related attacks. Please learn to use Prepared Statements

Now, from a query point of view, you can rather utilize JOIN to make this into a single query:

SELECT ci.* 
FROM CatalogueItems AS ci
JOIN mycart AS mc ON mc.itemID = ci.itemID 
WHERE mc.email = $email  /* $email is the input filter for email */

PHP code utilizing Prepared Statements of MySQLi library would look as follows:

$conn = new mysqli(server, dbuser, dbpw, db);
$email = $_GET['email'];

$querystring = "SELECT ci.* 
                FROM CatalogueItems AS ci
                JOIN mycart AS mc ON mc.itemID = ci.itemID 
                WHERE mc.email = ?";  // ? is the placeholder for email input

// Prepare the statement
$stmt = $conn->prepare($querystring);

// Bind the input parameters
$stmt->bind_param('s', $email);  // 's' represents string input type for email

// execute the query
$stmt->execute();

// fetch the results
$result = $stmt->get_result();
$rs = $result->fetch_array(MYSQLI_ASSOC);

// Eventually dont forget to close the statement
// Unless you have a similar query to be executed, for eg, inside a loop
$stmt->close();

How to use result of an SQL query as a field in another query

Something like this should work:

SELECT e.Id,e.name,r.rental_date
FROM entities e
LEFT JOIN
(
   SELECT entity_id,MAX(rental_date) AS rental_date
   FROM rentals
   GROUP BY entity_id
) r ON r.entity_id=e.Id

How to use result from SELECT query in another one

You can use the function FIND_IN_SET():

SELECT *
FROM table_two
WHERE FIND_IN_SET(id, @result);

sql count(*) query on results of another query performing multiple results

Try This Solution :

SELECT C.CountryName,Count(u.UserId) AS UserCount FROM  User_Subscribe AS us 
LEFT JOIN User AS u ON us.UserId = u.UserId
LEFT JOIN Country AS c ON u.CountryId = c.CountryId
WHERE c.CountryId = 3
GROUP BY c.CountryName

---

Related Topics