Urgent Mysql

[jim_scott]

PLEASE HELP.

(select a.partNo as `Part Number`, b.Description as `Part Type`, a.description as `Description`, count(c.itemID) as `Quantity` from partsBoughtIn a, PartType b, itemcost c where a.PartTypeID = b.ID and c.itemType = 'P' and c.itemID = a.id and c.jobmaterialID = 0 group by a.id)
union distinct
(select a.partNo as `Part Number`, b.Description as `Part Type`, a.description as `Description`, 0 as `Quantity` from partsBoughtIn a, PartType b where a.PartTypeID = b.Id) order by `Part Number`

thats a union i use however the first part brings back a count of all those part numbers that have entries in the itemcost table

however the second part i want to bring back those that dont have an entry and make quantity = 0

this query brings back the first part fine, but as expected the second part brings back all the part numbers even those that have entries in item cost.

any ideas how i can get a union to select only distinct part numbers and not distinct whole records.

thanks

frank

[jim_scott]

got it sorted

select a.partNo as `Part Number`, b.Description as `Part Type`, a.description as `Description`, count(c.itemID) as `Quantity` from (partsboughtin a inner join parttype b on a.parttypeid = b.id) left join itemcost c on (a.id = c.itemid and c.jobmaterialid = 0 and c.itemtype = 'P') group by a.partNo, a.id