select query help please

[chrisio]

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.

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

[r937]

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...

[chrisio]

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

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

[r937]

how are cars and companies related?

[chrisio]

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

[r937]

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

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

[chrisio]

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