subquery returns more than one row

[simon551]

I have an if >1 in there that I was trying to use in order to keep the multiple line subquery from being run at all. But mysql is not letting me run it. any ideas? It's the middle section that is causing problems.

 

Let me explain a little more... I'm trying to use a subquery to count the number of records in a related table and then show some information based on that related table/record inline in the main query if there is only one match. This is a problem I'm running into I believe because I'm trying to do a little bit of unorthodox table design. I have 2 detail tables that reference the same record.

 

SELECT  itineraries.dateBooked, itineraries.ticketNum, 
itineraries.resCode, itineraries.traveler_empID, itineraries.traveler,  itinerary_dets.airline, itineraries.ticketID, 
itineraries.ifile,  itinerary_dets.dateDepart, itinerary_dets.dateReturn, 
itinerary_dets.departingFrom, itinerary_dets.destination, itinerary_dets.notes, c.cc,

IF (c.cc >1, "split ticket", (SELECT CONCAT(projects.projName, ' - ',tblclients.ClientName,' - ', tbldivisions.Division) AS proj
FROM itinerary_splits INNER Join projects ON itinerary_splits.projId=projects.projId INNER Join contracts ON projects.contractID = contracts.contractID
INNER Join tblclients ON contracts.clientID = tblclients.ClientID INNER Join tbldivisions ON contracts.divisionID = tbldivisions.DivID) )
AS project

FROM itineraries Inner Join itinerary_dets ON itineraries.ticketID = itinerary_dets.itineraryId 
Left Join tblemployees on tblemployees.empId=itineraries.traveler_empId  
Left Join (SELECT COUNT( itinerary_splits.projId) AS cc , itinerary_splits.ticketId FROM itinerary_splits GROUP BY itinerary_splits.ticketId) as c
ON c.ticketId=itineraries.ticketId

[Illusion]

as per my knowledge we can't pass any statement as an argument for IF and other control flow functions.It always expect an expression or a constant.

[fenway]

Yeah, that won't work... why not just do that query anyway, and check cc.c > 1 in php?