So, what I want to get is a listing of all AccountNumber for ClientID='x', and a ContactName. I want it to return NULL for ContactName, unless any of the orders for that account have a Contact of ContactTypeID='y', in which case I want it to return the first one that comes up.
What I've got is:
ON (`Account`.`AccountID` = `Order`.`AccountID`)
LEFT JOIN `OrderContactBridge`
ON (`OrderContactBridge`.`OrderID` = `Order`.`OrderID`)
AND (`OrderContactBridge`.`ContactTypeID` = 'y')
LEFT JOIN `Contact`
ON (`OrderContactBridge`.`ContactID` = `Contact`.`ContactID`)
`ClientID` = 'x'
The problem with this is that if the AccountID has multiple OrderIDs, one of which has Contact of ContactType 'y', and another doesn't, it gives me 2 entries for the AccountNumber, one with the ContactName and one with NULL. How do I get rid of the NULL, but still have it display NULL when none of the OrderIDs have a ContactName of ContactType 'y' associated with them?
I hope this makes sense... any help would be most appreciated!