Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2006
    Posts
    6

    Unanswered: Advanced joined query, need help

    Hi all,

    I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in the whole. I really need MySQL wizz to give me a hand (and maybe refer me to books to get me to the wizz level myself).

    First off, english is a second language to me and sometimes my sentences might be a little awkward. Please forgive me. Mon français est bien meilleur.

    Description of database
    My database is pretty large and I tried to design it following the best practice (using a lot of many-to-many relationship tables). It holds the description of over 200 stores, with opening hours, services, locations, etc. I am trying to create a AJAX store search engin, with Google Map display, where the user can choose from a number of fields the conditions of his search. When he validates his search, the query is sent to the server, which retrieves the list and update the map.

    The conditions are:
    • Postal Code
    • City id
    • Service id
    • Opening hours


    Next is the schematic description of the table store_info
    | idStore | ... | idCity | openingHourMonday | closingHourMonday | openingHourTuesday | closingHourTuesday | ... |

    The schematic description of the table store_service
    | idService | vchLabel | ... |

    The schematic description of the table store_info_service
    | idService | idStore |

    The schematic description of the table store_info_postalcode
    | vchPostalCode | idStore |

    (NOTE: those Postal Codes are based on the flyer distribution list, so it doesn't contain all the postal code of the country, and some user input can return NULL).


    the Script
    Getting the information out with JOINED query is not a problem, the trouble is getting the right data out. Getting the conditions expressions right is what I need.

    So, I've built my PHP script to compose my query in parts ($SELECT, $FROM, $WHERE). Based on the GET parameters, I add to the query string the needed expressions to refine the query. I unite the parts in $query, and run it. Its been working OK, but not up to my linking. I wan't it bullet proof.

    Here is the base query:

    Code:
    SELECT i.*, 
    v.`vchLabel` AS ville, 
    FROM `stores_info` AS i 
    JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille` 
    WHERE i.`swActiv`=1 AND (p.idLangue = 'FR' OR p.idLangue IS NULL) 
    GROUP BY i.`idStore`;
    Here are some of my solutions:

    1. Postal code: If a user input a postal code which returns no data, needs to be repeated with a substring of the P.C. (eg.H1H1H1 -> H1H1H_). Using LIKE in

    Code:
    SELECT i.*, 
    v.`vchLabel` AS ville, 
    FROM `stores_info` AS i 
    JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille` 
    JOIN `stores_cp` AS cp ON i.`idStore` = cp.`idStore` 
    WHERE i.`swActiv`=1
    AND cp.`cp` LIKE 'H1H1H1' 
    GROUP BY i.`idStore`;
    return 0

    Code:
    SELECT i.*, 
    v.`vchLabel` AS ville, 
    p.`vchLabel` AS province 
    FROM `stores_info` AS i 
    JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille` 
    JOIN `stores_cp` AS cp ON i.`idStore` = cp.`idStore` 
    WHERE i.`swActiv`=1
    AND cp.`cp` LIKE 'H1H1H_' 
    GROUP BY i.`idStore`;
    return 1 store


    2. Closing time: the user can ask for stores open now and for the next 30, 60, or 300 minutes (5hre). It could be any number of minutes, but if it goes later then midnight, the closing time is removed and the user receives a warning.

    Code:
    SELECT i.*, 
    v.`vchLabel` AS ville, 
    FROM `stores_info` AS i 
    JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille` 
    WHERE i.`swActiv`=1
    AND '13:20:05' BETWEEN i.`wednesdayopen` AND i.`wednesdayclose` 
    AND '18:20:05' < i.`wednesdayclose` 
    GROUP BY i.`idStore`;
    3. idCity: the user can choose multiple cities from a drop-down list. The id get passed to the request in the form of a list separated by coma. I then use the IN() statement

    Code:
    SELECT i.*, 
    v.`vchLabel` AS ville, 
    FROM `stores_info` AS i 
    JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille` 
    WHERE i.`swActiv`=1
    AND i.`idVille` IN (401,1102) 
    GROUP BY i.`idStore`;
    Returns the list of all the stores located in city 401 or 1102.

    And thats where it gets complicated.

    Trouble #1: The services
    The user has checkbox associated with the idService. The idServices get sent to the request in the same form as the idCity (eg. 1,4).

    At first I used the IN() statement, but realised that it got the store that offers service 1 OR 4. I needed the stores that offered the two services.

    So I used = to compare the string to a subquery (SELECT GROUP_CONCAT(idServices) FROM store_info_service WHERE idStore = $idStore), but of course a store that offers services 1,2,3,4 was not returned, but it DID offer the services requested. I tried the IN() construct with this subquery, but nothing conclusive came out.

    Trouble #2: Concurrent search clauses
    The user can select more then one condition for his search. For exemple, he could look for the stores open in the next hour, around his own postal code (eg. H1H___).

    It has been a requisite of this project that the user receives listing whatever the cost. If one condition is not met, it is bypassed to give the stores corresponding to the other conditions. A javascript alert informs the user of that.

    How do I structure such a script? I've made it work with TIME and POSTAL CODE, doing repeated queries until a result is found.

    Code:
    while (no result){
         if (isset(T)){
              unset(T);
              try again;
         }else{
              reset(T);
         }
    
         if (isset(PC)){
              truncate PC;
              try again;
         }
    }
    Of course, this is a simplified version of my PHP script. That way, I try with the T, without the T, reducing PC every 2 queries. It seems pretty hard and time consumming right now, imagine when I add CITY and SERVICE to the equations.

    Does anyone have an idea how I could simplify this process?

    One hypothesis was to run the 4 queries separately and cross-referenced the results in PHP, only keeping the result sets which have common results, and keeping a warning on the defective queries. If a query is really messed up, I could keep only one condition and return these results, based on semantic priority (Postal code > City > Opening hours > Services).

    As you can see this is a though one. I'd really like to be able to wrap my mind around problems of the kind and come up with effective solutions. I tried to find books only about queries (not administration). If you have any recommandation, go ahead.

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    trouble #1 -- services

    this will require a subquery on the store_info_service table, with a GROUP BY on idStore, counting the number of idServices that were found
    Code:
    SELECT i.*, 
    v.`vchLabel` AS ville, 
    FROM `stores_info` AS i 
    JOIN (
         select idStore
           from store_info_service
          where idService in ( 1, 4 )
         group
             by idStore
         having count(*) = 2 
         ) as goodstores
       on goodstores.idStore = i.idStore
    WHERE i.`swActiv`=1
    by the way, be very careful about using the dreaded evil "select star" and GROUP BY at the same time -- in fact, you do not need to GROUP BY in your main queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2006
    Posts
    6
    Thanks for the quick reply. A subquery in the JOIN statement, that would never have crossed my mind.

    I'm trying to figure out what your telling me. If we take the following content

    Code:
    idStore |  idService
       A    |     1
       B    |     1
       B    |     2
       B    |     4
       C    |     1
       C    |     4
    And requesting for idService 1 and 4, would the store B and C be returned?

    Can you formulate an explanation to help me out understand? (Maybe put your query in plain english?)

    And how come the .* is so evil? Especially with the GROUP BY clause?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i can explain it by taking your example and processing it exactly the way the database would

    the first part is the WHERE clause

    this eliminates the rows we aren't interested in, leaving only these --
    Code:
    idStore idService
       A       1
       B       1
       B       4
       C       1
       C       4
    now we GROUP BY idStore and produce counts --
    Code:
    idStore count(*)
       A       1
       B       2
       C       2
    and finally, apply the HAVING clause, which gives this result --
    Code:
    idStore
       B
       C
    simple, yes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2006
    Posts
    6
    simple, yes?
    very

    Its always so simple when you know how.

    What do you think about my failsafe for the empty results. Is there any better way doing by something in 1 MySQL query?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    as you asked my opinion, i will say this: to me your scheme sounds far too much trouble than the results would warrant

    and the results could even be counter productive

    if i want to search for sporting goods within 4 miles of my house, i do not want to be shown housewares within 4 miles of my house

    the rule for search should be k.i.s.s.

    how do you know which of several search criteria is more important?

    i would give the message "your search returned 0 results; try some less specific criteria"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2006
    Posts
    6
    Your opinion is welcome (but my boss's is more important then your's)

    Quote Originally Posted by r937
    if i want to search for sporting goods within 4 miles of my house, i do not want to be shown housewares within 4 miles of my house
    But you might be happy to find sporting goods within 15 miles.
    I do get your point though, better keep it simple. I probably will return the whole list and tell the user to be less specific.

    Quote Originally Posted by r937
    the rule for search should be k.i.s.s.
    Go ahead, impress me with your fine acronymes. (what does it mean?)

    Thank you very much, I'll report back my result with your help.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  9. #9
    Join Date
    Dec 2006
    Posts
    6
    Hey,

    Tried it, can't find what's wrong.

    I ran this query:

    Code:
    SELECT i.*, 
    v.`vchLabel` AS ville, 
    FROM `stores_info` AS i 
    JOIN `geo_ville` AS v ON i.`idVille` = v.`idVille` 
    JOIN ( select idStore from stores_service_idStore where idService in ( 4,5 ) group by idStore having count(*) = 2 ) as goodstores on goodstores.idStore = i.idStore 
    WHERE i.`swActiv`=1
    AND i.`idVille` IN (401)
    It gave me one store (store A).

    I ran it again with only service 5, and count of 1.
    It gave me one OTHER store (store B). But you would agree that store A, in the same city, has service number 5, right?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    run the subquery by itself -- without the GROUP BY and HAVING if necessary -- to assure yourself it's working correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2006
    Posts
    6
    I found out the problem. My database was (it seems) corrupted. There where two identical rows, so the store had 2 input of idService 4, for a count of 2.

    So for the the subquery checking for "where idService in ( 4,5 ) group by idStore having count(*) = 2", having twice the id 4 was ok.

    I believe that the subquery trick does work.

    Cased solved, Thanks R937, Merry Christmas!

    I'll post back with the final result of my work.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •