Need help approaching a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help approaching a query

Author

Topic

capsize
Starting Member

6 Posts

Posted - 2010-02-22 : 20:29:47

Hi,

Im really having a hard time wrapping my head around this, hopefully someone can provide some direction.

Basically what Id like to do is the following:

@code = nvar, will be a session variable on the website
avail = bit, returned as a result of the subquery

select id, product, (select * from table2 where cCode=@cCode) as avail)
from table1

this is for a product list, and will show availability by world region. When a user vists the website, they are issued a country code (cCode) as a session variable, and will be used in the subquery.

Im trying to get a return like the following:

ID | Product | Avail
------------------------
1 | Prod1 | True
2 | Prod2 | False

So that I can activate a "purchase available" button if that product has NOT been checked off as unavailable in that region.

In the product setup on the website I have a section where a product owner can check off the regions where the product is NOT to be sold. On update I insert the cCode value for the checked region to table2, along with the product ID# (corresponding to table1). So if the subquery returns any records, that product is unavailable for sale in the user's region.

Any help would be greatly appreciated.

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-22 : 22:35:28

[code]
select id, product,
case when exists (select * from table2 where cCode=@cCode) then 1 else 0 end as avail
from table1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 03:58:06

What is the relation between the two tables?
I guess you will get the same avail value for all rows

Madhivanan

Failing to plan is Planning to fail

capsize
Starting Member

6 Posts

Posted - 2010-02-23 : 09:31:47

Hi Madhivanan,

Table 1 is of course the product list, containing the product id and the product name.

Table 2 lists the product id and the country code (cCode) of the regions that are restricted from sale, for each product. There will only be a row in this table if a region is restricted.

if when performing the subquery, a value is found, I would like to return false, so that:

if a customer is visiting from the US, (cCode=US) and there is a row in table2 for product id 35 that has a value of US in the cCode column, it should return False in the main query.

KH -- i tried running a version of your query, however it was returning all the same values, as Madhivanan noted.

If Im looking at this in the wrong direction -- other suggestions are very welcome.

thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 09:34:36

Try this

select id, product, 
       case when exists (select * from table2 where cCode=@cCode and productid=t1.productid) then 1 else 0 end as avail
from   table1 as t1

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 10:17:02

quote:
Originally posted by madhivanan

Try this
select id, product,
case when exists (select * from table2 where cCode=@cCode and productid=t1.productid) then 1 else 0 end as avail
from table1 as t1

Madhivanan

Failing to plan is Planning to fail

shouldnt return values be other way round as per Ops reqmnt?

case when exists (select * from table2 where cCode=@cCode and productid=t1.productid) then 0 else 1 end as avail

if a customer is visiting from the US, (cCode=US) and there is a row in table2 for product id 35 that has a value of US in the cCode column, it should return False in the main query.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-23 : 10:26:14

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

Try this
select id, product,
case when exists (select * from table2 where cCode=@cCode and productid=t1.productid) then 1 else 0 end as avail
from table1 as t1

Madhivanan

Failing to plan is Planning to fail

shouldnt return values be other way round as per Ops reqmnt?

case when exists (select * from table2 where cCode=@cCode and productid=t1.productid) then 0 else 1 end as avail

if a customer is visiting from the US, (cCode=US) and there is a row in table2 for product id 35 that has a value of US in the cCode column, it should return False in the main query.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I still dont understand your point

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources