How to run multiple query in single query

Question

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...

If first query work no need to run second
else if Second query work no need to run third
else if third query work no need to run fouth

Philipp · Accepted Answer · 2017-03-17 12:32:58Z

2

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;

answered Mar 17, 2017 at 12:32

Philipp

15.7k4 gold badges38 silver badges54 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2017-03-17 12:37:42Z

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;

KIKO Software · Accepted Answer · 2017-03-17 12:54:40Z

1

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.

edited Mar 17, 2017 at 12:54

answered Mar 17, 2017 at 12:41

KIKO Software

17.5k3 gold badges33 silver badges53 bronze badges

6 Comments

daulat Over a year ago

I think your this ans is best -> SELECT * FROM page_sections WHERE page_id = 17 ORDER BY city_id DESC, state_id DESC, country_id DESC LIMIT 1;

daulat Over a year ago

No need to pass country city state

KIKO Software Over a year ago

Yes, I was thinking along those lines, but wasn't sure it would work on your dataset.

daulat Over a year ago

change your ans

Philipp Over a year ago

@daulat are you sure, this it what you asked for? what if, there are entries with (page_id=15, country_id=242) and (page_id=15, country_id=241) and you want the query your example. The Code above would return the entry with country_id=242 instead of country_id=241

|

Collectives™ on Stack Overflow

How to run multiple query in single query

3 Answers 3

Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related