Sorry guys,

But i was able to resolve the issue. I had to take the line
customfieldid = 51 and join in with customset.recordid = part.id and customfieldid = 51.

Thanks

--- In firebird-support@yahoogroups.com, "Allstars" <data@...> wrote:
>
> Ok so i have this query, I want the customset.info to show a blank value for a part number. but when i set the customfieldid to 51 the record does not show up. I want the record to show with a blank even if it doesn't exist in the customset table. I tried using coalesce and a case statement, but the row still doesn't show. Any suggestions? Thanks!!
>
> select
> allbillofMaterials.bomnumber
> ,allBillofMaterials.totalTitles
> ,discontinuedVendors.DiscontTitles
> ,allBillofMaterials.bomDescription
> ,part.num
> ,part.description
> ,vendor.id
> ,
> case when customset.info is null
> then ''
> else customset.info
> end as "Grade Level"
>
> from allBillofMaterials
> inner join discontinuedVendors on discontinuedVendors.bomnumber = allBillofMaterials.bomNumber
> inner join bom on bom.num = allBillOfMaterials.bomnumber
> inner join bomitem on bomitem.bomid = bom.id
> inner join part on part.id = bomitem.partid
> inner join vendorparts on vendorparts.partid = part.id
> inner join vendor on vendor.id = vendorparts.vendorid
> --left outer join customset on customset.recordid = part.id
> where bomitem.typeid = 20
> and vendor.id in (229, 255, 261) -- Discontinued Vendor
> --and customset.customfieldid = 51
> order by allBillofMaterials.bomDescription
> ******************************************************
> P.S. I want it show like this:
>
> bomNumber TotalTitles DicontTitles Description Numb Customset.Info
> --------------------------------------------------------------------
> 23420394 3 1 foo 123
> 24564566 3 2 bar 123 H
>
>
> NOT LIKE THIS
>
> bomNumber TotalTitles DicontTitles Description Numb Customset.Info
> --------------------------------------------------------------------
> 24564566 3 2 bar 123 H
>