CODEWITHSUNDEEP
phpmysql
daulat
daulat

Reputation: 949

How to run multiple query in single query

I don't know what kind of title i need to use for this question anyone can help me to choose a title.

Anyway My question is
I need to run this query. 

SELECT key_features,about_course FROM `page_sections` WHERE page_id=15 AND country_id=241 AND  state_id=291 AND city_id=1277;

If no record found. Then i need to run same query with city_id=0

SELECT key_features,about_course  FROM `page_sections` WHERE page_id=15 AND country_id=241 AND  state_id=291 AND city_id=0;

If no record found. Then i need to run same query with state_id=0 && city_id=0 

SELECT key_features,about_course  FROM `page_sections` WHERE page_id=15 AND country_id=241 AND  state_id=0 AND city_id=0;

If no record found. Then i need to run same query with country_id=0 and state_id=0 && city_id=0 

SELECT key_features,about_course  FROM `page_sections` WHERE page_id=15 AND country_id=0 AND  state_id=0 AND city_id=0;

Now there is any better way to run all this query from top to down and get only one row.

I don't want to use neatened if else...

Upvotes: 2

Views: 67

Answers (3)

KIKO Software
KIKO Software

Reputation: 16771

Perhaps something like this?

SELECT 
  key_features,
  about_course 
FROM 
  page_sections 
WHERE 
  page_id = 15
ORDER BY 
  city_id DESC,
  state_id DESC,
  country_id DESC
LIMIT 1;

because of the LIMIT 1 you will only get one row. The trick is the ordering. I assume zero is the lowest number in the country_id, state_id and city_id fields, so anything higher should show up first in the results.

EDIT: I removed the selection of the specific country_id, state_id and city_id fields as you asked.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Hmmm. If you want one row, how about this:

SELECT key_features, about_course
FROM `page_sections`
WHERE page_id = 15 AND country_id = 241 AND state_id = 291 AND city_id = 1277
UNION ALL
SELECT key_features, about_course
FROM `page_sections`
WHERE page_id = 15 AND country_id = 241 AND state_id = 291 AND city_id = 0
UNION ALL
SELECT key_features, about_course
FROM `page_sections`
WHERE page_id = 15 AND country_id = 241 AND state_id = 0 AND city_id = 0
UNION ALL
SELECT key_features, about_course
FROM `page_sections`
WHERE page_id = 15 AND country_id = 0 AND state_id = 0 AND city_id = 0
ORDER BY country_id DESC, state_id DESC city_id DESC
LIMIT 1;

(This assumes that the ids are all non-negative, which seems like a reasonable assumption.)

The reason I am using UNION ALL instead of OR is so the subqueries can all take advantage of an index on page_sections(page_id, country_id, state_id, city_id). Using OR would probably preclude the use of an index.

Assuming that there is only one (or a handful of matches) for each condition, this will be a little slower than any of your original queries. However, it will probably be faster than going back-and-forth to the database multiple times.

EDIT:

Silly me. In MySQL, this is the best way to write the query:

SELECT key_features, about_course
FROM `page_sections`
WHERE (page_id, country_id, state_id, city_id) IN
       ( (15, 241, 291, 1277),
         (15, 241, 291, 0),
         (15, 241, 0, 0),
         (15, 0, 0, 0)
       )
ORDER BY country_id DESC, state_id DESC city_id DESC
LIMIT 1;

Upvotes: 1

Philipp
Philipp

Reputation: 15639

I simple approach would be to always select the field with the given id, or zero and then order and limit the result.

SELECT key_features,about_course
FROM `page_sections` 
WHERE page_id=15 
    AND (country_id=241 OR country_id=0)
    AND (state_id=291 OR state_id=0)
    AND (city_id=1277 OR city_id=0)
ORDER BY country_id DESC, state_id DESC, city_id DESC
LIMIT 1;

Upvotes: 2

Related Questions