Help with MySQL SubSelect

kbc1

Hi all

Not exactly a PHP related question but more of a MySQl one (and I hate the MySQL.com forum) so thought I would ask on here!

I have a query which includes a sub select to obtain the "hotelname". I need to be able to add the value of the sub select to the WHERE clause of the main query but mysql tells me it is an unknown column, even thought I can ORDER BY the value when I remove it from the WHERE clause.

Here is my full query:

SELECT
`tblevents`.`eventtitle`,
`tblbookings`.`bookingid`,
`tblbookingpassengers`.`firstname`,
`tblbookingpassengers`.`lastname`,

(SELECT DISTINCT
X.`hotelname`
FROM
`tblbookingsdetails` AS T2
Inner Join `tblhotelrooms` ON T2.`hotelroomid` = `tblhotelrooms`.`hotelroomid`
Inner Join `tblhotels` X ON `tblhotelrooms`.`hotelid` = X.`hotelid` WHERE T2.bookingid = `tblbookings`.`bookingid` GROUP BY X.hotelid) AS hotelname

FROM
`tblevents`
Inner Join `tblbookings` ON `tblevents`.`eventid` = `tblbookings`.`eventid`
Inner Join `tblbookingpassengers` ON `tblbookings`.`bookingid` = `tblbookingpassengers`.`bookingid`
Inner Join `tblbookingsextras` ON `tblbookingpassengers`.`bookingpassengerid` = `tblbookingsextras`.`bookingpassengerid`

WHERE tblevents.eventid = 68 AND hotelname = 'Hotel Name Here'
ORDER BY hotelname

How is it possible that I can ORDER BY "hotelname" but I cannot add "hotelname" to the WHERE clause?

Thanks for reading.

dalecosp

kbc1;11009383 wrote:
Hi all

Not exactly a PHP related question but more of a MySQl one (and I hate the MySQL.com forum) so thought I would ask on here!

Well, there IS a database forum here (would be a good spot to put this one). Some really bright DB types hang there too ... or, have in the past, anyways.

I have a query which includes a sub select to obtain the "hotelname". I need to be able to add the value of the sub select to the WHERE clause of the main query but mysql tells me it is an unknown column, even thought I can ORDER BY the value when I remove it from the WHERE clause.

Here is my full query:
SELECT
`tblevents`.`eventtitle`,
`tblbookings`.`bookingid`,
`tblbookingpassengers`.`firstname`,
`tblbookingpassengers`.`lastname`,

(SELECT DISTINCT
X.`hotelname`
FROM
`tblbookingsdetails` AS T2
Inner Join `tblhotelrooms` ON T2.`hotelroomid` = `tblhotelrooms`.`hotelroomid`
Inner Join `tblhotels` X ON `tblhotelrooms`.`hotelid` = X.`hotelid` WHERE T2.bookingid = `tblbookings`.`bookingid` GROUP BY X.hotelid) AS hotelname

FROM
`tblevents`
Inner Join `tblbookings` ON `tblevents`.`eventid` = `tblbookings`.`eventid`
Inner Join `tblbookingpassengers` ON `tblbookings`.`bookingid` = `tblbookingpassengers`.`bookingid`
Inner Join `tblbookingsextras` ON `tblbookingpassengers`.`bookingpassengerid` = `tblbookingsextras`.`bookingpassengerid`

WHERE tblevents.eventid = 68 AND hotelname = 'Hotel Name Here'
ORDER BY hotelname
How is it possible that I can ORDER BY "hotelname" but I cannot add "hotelname" to the WHERE clause?

Scope issue, probably? Have you tried qualifying hotelname in the WHERE clause?

laserlight

Thread moved to the Database forum 🙂

bradgrafelman

I would say you're adding it to the wrong WHERE clause; move the condition into the WHERE clause within the subquery instead.

Alternatively, don't use a subquery.