if else logic in sql select query

Hi All

I have a sqlserver database with product, catagory and sub catagory format. Before I describe my problem, let me share whats I have in db. Their are two types of sinaros, either the products are directly assigned to a catagory or a product is placed in subcatagory that is in turn have a catagory. I use the following table struct for both of the scenarios:

Product>>subcat bridge>>subcatagory

Product>>catbridge>>catagory

Here are the queries to get them:

1. If product assinged direct in catagory then

select product.pid, product.Prd_heading,product.[Description], product.Brand, product.img from product,cat_bridge,category where product.id=cat_bridge.pid and cat_bridge.catid=category.catid;

2. If product assinged to sub cat then

select product.pid, product.Prd_heading,product.[Description], product.Brand, product.img from product,subcat_bridge,subcategory where product.pid=subcat_bridge.pid and subcat_bridge.subcatid=subcategory.subcatid and subcategory.catid=category.catid;

Now the problem is, I want to use a single query to download all the products to a CSV format and I need to combine both of the queries with a single one, probably with if else logic, but I am not getting it, I mean how to acheive. Can anyone help me sort this out?

Thanks in Advance

Regards

Mykhan

Here's one nice way to do it using a UNION ALL (note that I'm adding a column, 'COND', that will tell you which condition you're in). Note also the use of UNION ALL vs UNION which you can read about herehttp://www.fmsinc.com/free/NewTips/SQL/SQLtip5.asp

Select 'CASE1' as COND, product.pid, product.Prd_heading,product.[Description], product.Brand, product.img
from product,
cat_bridge,
category
where product.id=cat_bridge.pid and cat_bridge.catid=category.catid;

UNION ALL

select 'CASE2' as COND, product.pid, product.Prd_heading,product.[Description], product.Brand, product.img
from product,
subcat_bridge,
subcategory
where product.pid=subcat_bridge.pid and subcat_bridge.subcatid=subcategory.subcatid and subcategory.catid=category.catid;

|||

Sorry Double Post

|||

dbland07666

Thats a very useful example, I didnt knew about Union All and Union before, it might come handy in other places as well, but I have two problems with your solution, which I am not getting

First, Its not working and I am getting the following error:

Incorrect syntax near the keyword 'UNION'. And I am confused whether Union ALL works with SQL 2000 or not.

And secondly, Can you elaborate of the 'case1' as COND a little more, I didnt get it

Sorry to bother you, if you can help, I will be really thankful.

Regards

Mykhan

|||

Sorry -- cut and paste errors on my part. Take out the semicolons, ie:

Select 'CASE1' as COND, product.pid, product.Prd_heading,product.[Description], product.Brand, product.img
from product,
cat_bridge,
category
where product.id=cat_bridge.pid and cat_bridge.catid=category.catid

UNION ALL

select 'CASE2' as COND, product.pid, product.Prd_heading,product.[Description], product.Brand, product.img
from product,
subcat_bridge,
subcategory
where product.pid=subcat_bridge.pid and subcat_bridge.subcatid=subcategory.subcatid and subcategory.catid=category.catid

Re the COND column, what I mean is that if you're returning the data into some kind of collection, you can test on COND if you need to know whether you were able to link directly to category or had to go through subcategory

|||

Sorry -- cut and paste errors on my part. Take out the semicolons

And I was foolish enough not to spot that minor mistake

Anyway thanks a lot, that helped.

Regards

Mykhan

sql