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.