Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

merge two queries with MySQL

Posted on 2014-12-03
2
Medium Priority
?
298 Views
Last Modified: 2014-12-03
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`

Open in new window


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'

Open in new window


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.
0
Comment
Question by:Robert Saylor
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 40478478
something like this should be a starter:

SELECT	`contacts`.`contactID`,
	`contacts`.`first`,
	`contacts`.`last`,
	`contacts`.`email`
  ,  (SELECT COUNT(`inventory`.`reservationID`)
   FROM	`inventory`,`reservations`
  WHERE `inventory`.`passengerID` = '99'
	AND `inventory`.`reservationID` = `reservations`.`reservationID`
	AND `reservations`.`reservation_date` BETWEEN '20110101' AND '20141231'
  )   AS 'total'
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`
                                  

Open in new window

tip: use aliases in your queries:
http://www.experts-exchange.com/Database/Miscellaneous/A_11135-Why-should-I-use-aliases-in-my-queries.html
0
 
LVL 7

Author Closing Comment

by:Robert Saylor
ID: 40478495
Thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question