troubleshooting Question

merge two queries with MySQL

Avatar of Robert Saylor
Robert SaylorFlag for United States of America asked on
MySQL ServerPHP
2 Comments1 Solution337 ViewsLast Modified:
Hi, I want to merge 2 queries so I don't have to run query 1 then a nested query 2 with php. How would you merge these two as 1 query?

Query 1:
SELECT
	DISTINCT `contacts`.`contactID`,
	`contacts`.`first`,
	`contacts`.`last`,
	`contacts`.`email`

FROM
	`suspended_inventory` si, `reservations`,`contacts`
WHERE
	`si`.`reservationID` = `reservations`.`reservationID`
	AND `reservations`.`reservation_date` BETWEEN '20110101' AND '20141231'
	AND `si`.`contactID` = `contacts`.`contactID`
	AND `contacts`.`email` != ''
	AND `contacts`.`deceased` != 'Y'
	AND `contacts`.`omit_from_future_mailings` != 'Y'

ORDER BY `contacts`.`contactID`

I then get a contactID value. In Query 1 I would like to count the number of records returned. If 0 return 0.

Query 2
SELECT
	COUNT(`inventory`.`reservationID`) AS 'total'

FROM
	`inventory`,`reservations`

WHERE
	`inventory`.`passengerID` = '99'
	AND `inventory`.`reservationID` = `reservations`.`reservationID`
	AND `reservations`.`reservation_date` BETWEEN '20110101' AND '20141231'

I would like to run Query 2 inside Query 1 in the select area returning the value if possible. If not, then I will have to run this as a nested query in php.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros