Jump to content
  • Advertisement
Sign in to follow this  
miner2049er

[web] Combining multiple MySQL queries into one

This topic is 5463 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Before I describe my problem, here are the MySQL tables I use, along with some example data: (BTW, my original fixed-width font message is now displayed in variable-width, so apologies for that.) ------------------------------------------------------------ table_subscribers ----------------- id name address birthdate etc. -- ---- ------- --------- 1 Brian 2 Joe 3 Mary etc. ------------------------------------------------------------ table_cheesemakers ------------------ id name id_city -- ---- ------- 1 Super Cheese Co. 4 2 Fromage-tastic 9 3 The Cheese Shack 7 etc. ------------------------------------------------------------ table_states ------------ id name -- ---- 1 Arizona 2 California 3 Hawaii etc. ------------------------------------------------------------ table_cities ------------ id name -- ---- 1 Fresno 2 Atlanta 3 Chicago etc. ------------------------------------------------------------ table_subscriber_states ----------------------- id id_subscriber id_state -- ------------- -------- 1 1 1 2 1 2 3 1 3 etc. ------------------------------------------------------------ table_subscriber_cities ----------------------- id id_subscriber id_city -- ------------- ------- 1 1 8 2 2 3 3 2 7 etc. ------------------------------------------------------------ table_city_states ----------------- id id_city id_state -- ------- -------- 1 1 3 2 2 3 3 3 6 etc. ------------------------------------------------------------ DESCRIPTION OF PROBLEM ---------------------- Subscribers join the service, and every so often they will get sent gourmet cheeses from cheesemakers around the USA. The subscribers do not get to choose individual cheesemakers, they can only specify which cities and states they receive cheese from. There is NO connection between the chosen cities and states - subscribers can choose any combination of cities and states. For example, subscribers can specify things like - "I want to get cheese from the cities Seattle and Miami, and the states Florida, Wisconsin and Georgia." In other words, if the subscriber signed up for Nevada, then whenever a cheesemaker located in ANY city in Nevada decides to send out some cheese, then that subscriber will be sent some cheese. And EVEN when a subscriber specifies a state (such as Washington) they can ALSO specify a city that falls within that state (such as Seattle.) But they will only receive one delivery of cheese from the cheesemaker, not two. So, onto the problem. I am a cheesemaker, and it is time to send out cheese to folks. I need to run my query, and find out which subscribers have signed up for the city I am located in, OR the state I am located in (or even both.) Even if a subscriber has chosen both my city and state, the subscriber's details will only appear once in the rows returned. To find out the subscribers who have chosen my CITY, I use the following query, which works fine: (incidentally, $cmid = my cheesemaker id from table_cheesemakers) SELECT ts.id, ts.name, ts.address FROM table_subscribers AS ts, table_cheesemakers AS tcm, table_subscriber_cities AS tsc WHERE ts.id = tsc.id_subscriber AND tsc.id_city = tcm.id_city AND tcm.id = $cmid Now, to find out the subscribers who have chosen my STATE, I use the following, which also works fine: SELECT ts.id, ts.surname, ts.address FROM table_subscribers AS ts, table_cheesemakers AS tcm, table_subscriber_states AS tss, table_city_states AS tcs WHERE ts.id = tss.id_subscriber AND tss.id_state = tcs.id_state AND tcs.id_city = tcm.id_city AND tcm.id = $cmid But here's the problem, my attempt at combining these two previous queries into one query, and return the subscribers who have chosen my state, city, or both - does not work: SELECT ts.id, ts.name, ts.address FROM table_subscribers AS ts, table_cheesemakers AS tcm, table_subscriber_cities AS tsc, table_subscriber_states AS tss, table_city_states AS tcs WHERE ( ts.id = tsc.id_subscriber AND tsc.id_city = tcm.id_city AND tcm.id = $cmid ) OR ( ts.id = tss.id_subscriber AND tss.id_state = tcs.id_state AND tcs.id_city = tcm.id_city AND tcm.id = $cmid ) Upon execution, my script hangs - most probably indicating a lot of computations taking place at the DB server. I haven't waited around for it to complete yet, but if I did, it would probably come back with a lot of rows. So, my first question is - what is the correct query I should be using? (and how that correct query works.) Question 2 - If I wish to add further selection criteria, such as "we can only send out our Tequila-flavored cheese to subscribers over the age of 21", or "avoid zipcodes 54321, 54322 and 54323 because cheese consumption is illegal there" will the solution offered to [Q1] be scalable/extendable to additional criteria? If anybody needs any more details or clarification, please let me know. Thanks for reading this, and thanks in advance for assistance! Simon.

Share this post


Link to post
Share on other sites
Advertisement
Poop. I was going to suggest a subquery, but I learned that EXISTS isn't supported until MYSQL 4.1. Upon re-reading your question though, the subquery wouldn't return everything you want anyway. What you really want is a UNION. I'm also assuming that the queries you listed had a typo (didn't see surname anywhere). UNION only works when the queries return the exact same column names and types, but that should work fine. So try this one:

SELECT
ts.id, ts.name, ts.address
FROM
table_subscribers AS ts,
table_cheesemakers AS tcm,
table_subscriber_cities AS tsc
WHERE
ts.id = tsc.id_subscriber
AND
tsc.id_city = tcm.id_city
AND
tcm.id = $cmid
UNION DISTINCT
SELECT
ts.id, ts.name, ts.address
FROM
table_subscribers AS ts,
table_cheesemakers AS tcm,
table_subscriber_states AS tss,
table_city_states AS tcs
WHERE
ts.id = tss.id_subscriber
AND
tss.id_state = tcs.id_state
AND
tcs.id_city = tcm.id_city
AND
tcm.id = $cmid

See if that doesn't do it for you. UNION can either be ALL or DISTINCT, but I'm assuming you don't need to see duplicate user information. Of course, it wouldn't be bad to use ALL when testing the query to make sure it's working right.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

GameDev.net is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!