Asked By
kmilton
210 points
N/A
Posted on - 06/25/2011
Hi,
I have created a 3 SQL tables. The table details are as follows,
Table Fields
Food_Item Food_Item_id, Food_Item_name, item_category_id
Food_item_category Food_item_id, Food_item_name
Food_item_order Food_item_id, item_id, item_category_id
I tried to search for all food_item_id in Food_item_order table where few rows have item_id and item_category_id as null. Some rows have the combination of item_category_id and item_id as null. I tried to get all of the items that are null by item_category_id from the table. I have used “OR” condition in the SQL query, but it seems that it doesn’t produce the expected result. Can someone help me modify this query to produce the exact result? Thanks.
Issue in SQL Query while using “or” condition
Try this query,
Select fi.Food_item_id from Food_item fi
JOIN
order fio on fi.item_category_id = fio.item_category_id
where ( (fio.item_category_id is NOT NULL ) or( fi.Food_item_id in
(Select fi.id from Food_item_order fio
JOIN
Food_item fi on fi.Food_item_id=fio.itemid
where (fio.itemid is NOT NULL))))
You have to use “OR” condition between the “JOIN” queries. Here I have tried to use “JOIN” within a “JOIN” query to filter the NULL value combinations in item_id and item_category_id. It may create a problem if there are many items in Food_item_category. And also if the Food_item_order has a value in item_category_id and the respective item_id column value has NULL, then it may produce issues in the result. If the query is not producing the expected results, you have to change the table structure of the Food_order_item table.
Hope this query resolves the problem. Please do reply if the issue persists. If you resolved the issue by any other ideas, please share with us.
