Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2011
    Posts
    4

    Unanswered: Mysql query on linking table

    I have a search form where people can select several services of companies, the tables are as follows:

    Business business_id, business_name

    Business Data

    1, Prince & Son

    2, Smith Ltd

    3, Browns

    Services service_id, service_name

    Service Data

    1, Shot Blasting

    2, Painting

    3 Steam Cleaning

    Business_service business_id, service_id

    1, 1

    1, 2

    2, 2

    3 1

    3 2

    So if the user selected to search one item e.g Shot Blasting the sql will be :

    Select * FROM business
    Inner Join business_service On business_service.business_id = business.business_id
    Inner Join service On service.service_id = business_service.service_id
    WHERE business_service.service_id = 1

    Which works fine, but should the user search for a business that offers "Shot Blasting" AND "Painting" I would expect them to see business_id 1 and 3. I know the following sql will not work but I thought I'd show where i'd got to.

    Select * FROM business
    Inner Join business_service On business_service.business_id = business.business_id
    Inner Join service On service.service_id = business_service.service_id
    WHERE business_service.service_id = 1 And business_service.service_id = 2

    I know this is really simple but I just can't seem to get my head around it.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Business.business_id
         , Business.business_name
      FROM Services
    INNER
      JOIN Business_service
        ON Business_service.service_id = Services.service_id
    INNER
      JOIN Business
        ON Business.business_id = Business_service.business_id
     WHERE Services.service_name IN ( 'Shot Blasting' , 'Painting' )
    GROUP
        BY Business.business_id
         , Business.business_name
    HAVING COUNT(*) = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2011
    Posts
    4
    Thank you for that, that does the trick. However I need to do the exact thing on another linking table at the same time, is this possible?? I can work out all the joins its just how would I be able to repeat:
    WHERE service.service_id IN ( '1', '3')
    Group
    BY business.business_id
    HAVING COUNT(*) = 2

    Thank you for your time

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sorry, i don't understand your question

    what other table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2011
    Posts
    4
    Sorry I didn't explain myself. There will be another table, so lets just say they also had sold products....so there would be a products table and a linking table called_business products to store each businesses products in. Then as the user can select two or more services for a business they could also at the same time select two products....therefore there search could be finding a business with 2 services "and" 2 products.

    I hope that makes more sense.

    Thanks
    Last edited by simster; 09-21-12 at 16:53.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Business.business_id
         , Business.business_name
      FROM ( SELECT business_id
               FROM Business_service
              WHERE service_id IN ( 1 , 2 )
             GROUP
                 BY business_id
             HAVING COUNT(*) = 2 ) AS ok_services
    INNER
      JOIN ( SELECT business_id
               FROM Business_products
              WHERE product_id IN ( 9 , 37 )
             GROUP
                 BY business_id
             HAVING COUNT(*) = 2 ) AS ok_products
        ON ok_products.business_id = ok_services.business_id         
    INNER
      JOIN Business
        ON Business.business_id = ok_services.business_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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