Showing posts with label describe. Show all posts
Showing posts with label describe. Show all posts

Wednesday, March 28, 2012

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 PostEmbarrassed

|||

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 workingBig Smile 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 mistakeBig Smile

Anyway thanks a lot, that helped.

Regards

Mykhan

sql

Wednesday, March 7, 2012

Identity columns

Can anyone describe how SQLServer calculates identitycolumns? Does it use some internal counter when generating the next identity,or something a little more mundane such as gets the highest existing identityvalue at the point of the insertion and increments it by the IDENT_INCR valueof the identity column?

I'm not worrying about reliability or gaps in values, but iam wondering if it would be less efficient for me to manually manage theidentity/primary key in the form of a counter in another table used to generatethe new identity, or simply let the DB do it for me. I dont mind if there aregaps in the sequence etc. so would it be less efficient for me to calculate thefield than SQLServer itself? Basically, is the overhead to the DB of me doingit greater than the overhead of the app doing it...

Thanks

Here's how the values are calculated:

http://blogs.msdn.com/sqlprogrammability/archive/2006/04/04/567724.aspx

It would take MUCH MORE time for you to do it than letting SQL Server manage it for you. By several orders of magnitude.

|||Thanks for that. how are the 'generated' in memory though without accessing the table?