Nyx Assasin Nyx Assasin - 3 months ago 16
Vb.net Question

MYSQL - Searching Criteria between 2 Combined Table

I want to ask something

I have 2 Tables and that`s are :

Table: General Inventory

enter image description here

and Table: Item_Master_List

enter image description here

now my goal here is to combine both tables based on ItemCode like

...where GeneralInventory.ItemCode=Item_Master_List.ItemCode


something like that. Now I did that and here is my full code for that

Select GI.ItemCode,IML.Description,IML.StandardUOM,IML.StandardPrice
from GeneralInventory GI,item_master_list IML where
GI.ItemCode = IML.ItemCode


and here is the output

enter image description here

Now i have some criteria in my program and it looks like this

enter image description here

Here is my question. How can I applied the search feature in my MYSQL Command? How can i search the right data? using the combined columns? I tried this one but it results on multiple data redundancy

Select Distinct GI.ItemCode,IML.Description,IML.StandardUOM,IML.StandardPrice from
GeneralInventory GI,item_master_list IML where GI.ItemCode = GI.ItemCode and
IML.ItemCode = '' or IML.Description = 'Baking Soda 454 g' or IML.StandardUOM = '';


enter image description here

Additional Question

I have a another table called
Table: StockAdjust
and this is what it has

enter image description here

and now the best code that I have is this

SELECT DISTINCT GI.ItemCode,
IML.Description,
IML.StandardUOM,
IML.StandardPrice
FROM GeneralInventory GI
INNER JOIN item_master_list IML
ON GI.ItemCode = IML.ItemCode
WHERE IML.ItemCode = 'My Data' OR
IML.Description = 'My Data' OR
IML.StandardUOM = 'My Data'


The code above will combine Table
GeneralInventory GI
and
item_master_list IML
where
GI.ItemCode = IML.ItemCode
and filtering criteria using the ff.
IML.ItemCode, IML.Description and IML.StandardUOM


Now my question here is how can I apply
NOT IN
command? like

Above code.....AND GI.ItemCode NOT IN (select ItemCode in StockAdjust)


How can I apply a code here that will do the code above and it will not include Data thats already in
StockAdjust
;

Answer

I think you are getting confused in your query because you placed the table join criteria into the WHERE clause, which also contains the logic for the search. Instead, use explicit join syntax in your query:

SELECT DISTINCT GI.ItemCode,
                IML.Description,
                IML.StandardUOM,
                IML.StandardPrice
FROM GeneralInventory GI
INNER JOIN item_master_list IML
    ON GI.ItemCode = IML.ItemCode
WHERE (IML.ItemCode = '' AND
       GI.ItemCode NOT IN (SELECT ItemCode FROM StockAdjust) OR
      IML.Description = 'Baking Soda 454 g' OR
      IML.StandardUOM = ''