MySQL query getting too complex

[Fernando_4]

Hi all, I've made a query that gathers info from several tables, after it was working, there was the need to integrate yet another table, however I can't seem to put it to work. I've seen some of this "witchcraft" before, but can't seem to find a tutorial, basically because I don't know what to call it and google doesn't guess for me xD

This block is working as intended:

SELECT 
car.carID, car.carBAR, car.carTYPE, 
car.carNAME, car.paxMIN, car.paxMAX, 
car.babySEAT, car.boosterSEAT, car.maxLUGGAGE, 
car.carMARKUP, car.carSEASON, car.carOVERTIME, 
price.PRICE, price.DISCOUNT, toll.tollsPRICE
FROM `AcarCONFIG` AS car 
INNER JOIN `ptFAOatt` AS price ON car.carID = price.carID
LEFT JOIN `ptTOLLS` AS toll ON toll.tollsID = price.tollsID
WHERE car.serviceID = 1
AND car.paxMIN <= 2
AND car.paxMAX >= 2
AND ((price.zoneA = 2 AND price.zoneB = 7) OR (price.zoneA = 7 AND price.zoneB = 2))
AND car.carSTATUS > 0
AND price.STATUS > 0
ORDER BY car.carSTATUS ASC

This is the bit I need to implement with the previous query. I tried chaining them as I've seen around the web, but it keeps giving me errors and I can't seem to find solutions.

(SELECT x.extraType, x.extraFree, x.extraMax, x.extraPrice
FROM `AcarExtras`
WHERE x.status > 0
AND x.carID = car.carID)

So we have 3 tables on the fist query, AcarCONFIG that has a list of cars and their specs, ptFAOatt that has prices from given zoneA and zoneB by carID (order is not important A->B or B->A) and ptTOLLS that gives me the highway toll price.
Now each car is going to have certain extras with associated free and maximum amounts, a price and a type to select the html block.

So for every car that comes out of the 1st query I'd want to list all of it's extras in one go. I know I can take the 1st query's carID's and run them through a simple query on the extras table. But I'm sure there's some way to get this done in one hit.

Any links to documentation on these selects enclosed in ()'s would help me figure out what they're really for. Although seeing it in action might do the trick as well.

[Szabi Zsoldos]

Can you post an sqlfiddle ?

[Fernando_4]

Ok, I've been trying to get this together and it seems to be working. Different approach though. Give me some suggestions as to how to better this plz.

I'm using prepared statements to run the query then building an array in the while loop, which then is used to build the client's output.

My while loops was something like this:

while($stmt->fetch()) {
    $temp =  array(
        "id" => $id,
        "bar" => $bar,
        "type" => $type,
        "name" => $name,
        "pMin" => $pMin,
        "pMax" => $pMax,
        "bbSeat" => $bbSeat,
        "boSeat" => $boSeat,
        "lugg" => $lugg,
        "markup" => $markup,
        "season" => $season,
        "over" => $over,
        "price" => $price,
        "discount" => $discount,
        "toll" => $toll
    );
    $carList[$id] = $temp;
}

So now I guess I just need to create a secondary temp array to store the extras and then put it into $carList[$id]['extras'] turning into something like this (the result array):

$carList =  array(
    $id => array(
        "id" => $id,
        "bar" => $bar,
        "type" => $type,
        "name" => $name,
        "pMin" => $pMin,
        "pMax" => $pMax,
        "extras" => array(
            0 => $free,
            1 => $max,
            2 => and so forth...
        ),
        "markup" => $markup,
        "season" => $season,
        "over" => $over,
        "price" => $price,
        "discount" => $discount,
        "toll" => $toll
);

Here is the SQLFiddle you asked for Szabi.

[Fernando_4]

Looks like I got it working... seeing other people's queries made me process thoughts another way which weren't working. The Fiddle posted above is almost there though, here's the fix:

SELECT 
car.carID, car.carBAR, car.carTYPE, 
car.carNAME, car.paxMIN, car.paxMAX, 
car.babySEAT, car.boosterSEAT, car.maxLUGGAGE, 
car.carMARKUP, car.carSEASON, car.carOVERTIME, 
price.PRICE, price.DISCOUNT, toll.tollsPRICE,

x.extraType, x.extraFree, x.extraMax, x.extraPrice

FROM `AcarCONFIG` AS car 
INNER JOIN `ptFAOatt` AS price ON car.carID = price.carID
LEFT JOIN `ptTOLLS` AS toll ON toll.tollsID = price.tollsID

LEFT JOIN `AcarExtras` AS x ON x.carID = car.carID AND x.status > 0

WHERE car.serviceID = 1
AND car.paxMIN <= 2
AND car.paxMAX >= 2
AND ((price.zoneA = 2 AND price.zoneB = 7) OR (price.zoneA = 7 AND price.zoneB = 2))
AND car.carSTATUS > 0
AND price.STATUS > 0

ORDER BY car.carSTATUS ASC, x.status ASC

Just adding those 2 lines with the edit on the last made it all work^^