Issue in SQL Query while using “or” condition

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.

• Status: Open
• Question Views: 880
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: MS SQL Server

Answered By jessy_neil 0 points N/A #129316

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.

About jessy_neil

Questions
1

Answers
2

Best Answers
1

• Vote Up 0 Vote Down

• Posted on - 06/25/2011
• Question Category: MS SQL Server