okay, this is complicated, but hear me out- here's my situation:
I have 5 tables-

Account
-AccountID
-ClientID
-AccountNumber

Order
-OrderID
-AccountID
-OrderNumber

OrderContactBridge
-OrderContactBridgeID
-OrderID
-ContactID
-ContactTypeID

Contact
-ContactID
-ContactName

ContactType
-ContactTypeID
-ContactTypeName

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:

SELECT
`AccountNumber`,
`ContactLastName`,
`ContactFirstName`
FROM
`Account`
JOIN `Order`
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`)
WHERE
`ClientID` = 'x'
ORDER BY
`AccountNumber`

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!

Thanks,

-wm