SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    276
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select query help please

    Hi,
    The below query works well for returning 1 result however I now need to expand it to return multiple results. At the moment I feed it 1 car type and 1 company id and it spits out one result, I then loop the query to get the number of results I need - but this is very inefficiant so I want to change it so I pass all my variables at once.

    So where I have cars.type ='1' I want to have car.type ='1' or cars.type='2' which in itself works but how to I extend the company section as when I add the extra 'Or's' in there the query goes wacky and spits out hundreds of results which are all the same - I think its something to do with the inner join but im not sure.

    Code MySQL:
    SELECT prices.single_f, 
    prices.return_f, 
    prices.vehicle, 
    resort.company_id, 
    resort.resort, 
    cars.type, 
    cars.description, 
    cars.long_description, 
    cars.image, 
    surcharges.type, 
    surcharges.adult, 
    surcharges.kids, 
    prices.timefrom, 
    prices.timeto, 
    prices.resort_id, 
    cars.min, 
    cars.max, 
    company.is_enabled
    FROM prices
    INNER JOIN resort ON resort.id = prices.resort_id
    AND resort.company_id = '1'
    AND resort.resort = 'Acoteias'
    INNER JOIN company ON company.id = '1'
    INNER JOIN cars ON cars.type = prices.vehicle
    AND cars.type = '2'
    LEFT JOIN surcharges ON surcharges.resort_id = prices.resort_id
    WHERE prices.break <= '102'
    AND company.is_enabled = 'Yes'
    AND (
    (
    prices.timefrom <= prices.timeto
    AND prices.timefrom <= '05:00:00'
    AND prices.timeto >= '05:00:00'
    )
    OR (
    prices.timefrom >= prices.timeto
    AND prices.timefrom <= '05:00:00'
    AND '23:59:59' >= '05:00:00'
    )
    OR (
    prices.timefrom >= prices.timeto
    AND '00:00:00' <= '05:00:00'
    AND prices.timeto >= '05:00:00'
    )
    )
    LIMIT 0 , 30

    Thanks
    Chris

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    in words, what are you getting -- the prices of a particular car or the cars for a particular price? see the difference in the question?

    i see prices related to resorts, but apparently only a certain resort, then these prices related to all companies that have an id of 1, together with cars related to vehicle prices, and surcharges related to resort prices...

    i am so lost...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    276
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, I have just re-read this and realised it made no sense... what I want to return is all car types for certain companies - so if I remove the and clause for the cars.type I get all the cars returned for one company which is correct, how do I then pass it multiple company ID's so it shows all cars for given company id's

    Code MySQL:
    SELECT prices.single_f, 
    prices.return_f, 
    prices.vehicle, 
    resort.company_id, 
    resort.resort, 
    cars.type, 
    cars.description, 
    cars.long_description, 
    cars.image, 
    surcharges.type, 
    surcharges.adult, 
    surcharges.kids, 
    prices.timefrom, 
    prices.timeto, 
    prices.resort_id, 
    cars.min, 
    cars.max, 
    company.is_enabled
    FROM prices
    INNER JOIN resort ON resort.id = prices.resort_id
    AND resort.company_id = '1'
    AND resort.resort = 'Acoteias'
    INNER JOIN company ON company.id = '1'
    INNER JOIN cars ON cars.type = prices.vehicle
    LEFT JOIN surcharges ON surcharges.resort_id = prices.resort_id
    WHERE prices.break <= '102'
    AND company.is_enabled = 'Yes'
    AND (
    (
    prices.timefrom <= prices.timeto
    AND prices.timefrom <= '05:00:00'
    AND prices.timeto >= '05:00:00'
    )
    OR (
    prices.timefrom >= prices.timeto
    AND prices.timefrom <= '05:00:00'
    AND '23:59:59' >= '05:00:00'
    )
    OR (
    prices.timefrom >= prices.timeto
    AND '00:00:00' <= '05:00:00'
    AND prices.timeto >= '05:00:00'
    )
    )
    LIMIT 0 , 30

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    how are cars and companies related?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    276
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    how are cars and companies related?
    I have a cars table with the below structure

    id(int)
    type(varchar)
    min(int)
    max(int)
    description(text)
    long_description(text)
    image(varchar)
    xml_description(varchar)

    and in my company table i have the below
    id(int)
    name(varchar)
    email(varchar)
    cars(text)
    .....
    ....
    ...
    ..
    .
    the car types that each company run are stored in a comma delimited string such as 1,5,8,25, and I simply explode these and loop the query the number of times I need to in order to produce the results I need. As explained below I can now get all car types for 1 company ok but im strugling on giving it multiple company id's.

    Hope this makes sense
    Chris

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm afraid i have some rather bad news

    what you want is extremely difficult with your current design, and will likely remain inefficient, although i will bet that it would still be better than running a query inside a loop

    best would be a redesign of the one-to-many relationship

    as an alternative, instead of looping, pull back all the car data using something like
    Code:
    select id, etc. from cars where id in (  1,5,8,25  )
    where you got the 1,5,8,25 from the companies table, then do array matching in php

    a join is also possible but it involves a table scan and i'm not sure it wouldn't mess up your other one-to-many relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Mar 2005
    Posts
    276
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To be honest, I was begining to see that it was going to be hard however I thought I was missing somthing.. when you say redisign do you mean the whole db / tables or just the query?

    Thanks
    Chris


Bookmarks

Posting Permissions

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