[RESOLVED] getting matches on null values

[jrough]

Sorry I marked this resolved but it is not resolved.

My join should match on ndc on inventory and link tables. I am getting 6492 results but the ndc column in the first table has null values. So how can it pull the sql on the equi join if there is no ndc value in the first table. I'm seeing null ndc values in the php display. I want all the matches that are real matches and aren't null. Is it doing matches on the null values? here are about
10 null ndc values in the link table. That could be the number I'm getting. How do I fix the sql?

SELECT bu, cart_id, ndc, error_flag, item_id, description, upc, ndc_uom, std_uom FROM inventory INNER JOIN link ON inventory.ndc=link.ndc AND bu ='08005' ORDER BY bu

The problem is I get a total of 6492 records but I'm looking at the records and some have the match key ndc in the parent table blank. How could it possibly be a join if there is no record?

Then I rewrote it thinking it should be a equi-join:
SELECT bu, cart_id, ndc, error_flag, item_id, description, upc, ndc_uom, std_uom FROM inventory INNER JOIN link ON inventory.ndc=onelink.ndc AND bu ='08005' ORDER BY bu

but got the same result?

thanks

[Crowly]

Add a where statement to filter out the null rows
Something like this (added aliases and some line breaks)

SELECT bu, cart_id, ndc, error_flag, item_id, description, upc, ndc_uom, std_uom 
FROM inventory AS i
INNER JOIN link AS l ON i.ndc=l.ndc AND bu ='08005' 
WHERE i.ndc IS NOT NULL
ORDER BY bu

[jrough]

thanks very much I didn't think it was possible to match on nulls.

[Crowly]

You have to use IS NULL or IS NOT NULL. Var = Null or Var != Null, and simular, is not possible.