SitePoint Sponsor

User Tag List

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

    Select query Duplicating results

    Hi,
    The below query works in so far that it returns the expected results except that it returns the same results twice,

    I cant see where I have went wrong with the query, can you?

    Thanks
    Chris


    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
    , cars.shuttle
    , company.is_enabled
    , cars.shuttle
    FROM prices
    INNER JOIN resort ON resort.id = prices.resort_id
    AND resort.resort = 'Amarilla Golf'
    INNER JOIN company ON company.airports_served = resort.airport_id
    INNER JOIN cars ON cars.type = prices.vehicle
    LEFT JOIN surcharges ON surcharges.resort_id = prices.resort_id
    WHERE prices.break <= '146'
    AND company.is_enabled = 'Yes'
    AND (
    (
    prices.timefrom <= prices.timeto
    AND prices.timefrom <= '17:00:00'
    AND prices.timeto >= '17:00:00'
    )
    OR (
    prices.timefrom >= prices.timeto
    AND prices.timefrom <= '17:00:00'
    AND '23:59:59' >= '17:00:00'
    )
    OR (
    prices.timefrom >= prices.timeto
    AND '00:00:00' <= '17:00:00'
    AND prices.timeto >= '17:00:00'
    )
    )

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    it's gotta be your data
    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)
    ok, thanks for answering. I will check my data and try again. Can you explain why tho when I add a ORDER BY clause on the query it increases the time to execute the query from a couple of seconds to over 80 seconds.

    im simply adding ORDER BY prices.return_f ASC

    Thanks
    Chris

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    how many rows? if its a bunch of rows, then your server probably needs to be configured with a larger sort buffer. with a sort buffer that's too small, the rows have to be written to disk and sorted there.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •