New column retrival on Sql
-
Hi guys... Actually i have a table called products which has 4 products in it say for example pepsi, miranda, coke and fanta. The table has two columns product id and productname as follows: 1 pepsi 2 miranda 3 coke 4 fanta now i have another table called agent, which has 3 columns such as agentid, region, productid. Now each agentid has a region and productid assigned to it as follows: 1001 DXB 1 1001 AJM 4 1002 ABC 2 1003 XYZ 3 1003 DEF 4 and so on. now in my application i need to make use of a query which gives me a result of all the product name with status for any particular agent id, for example: for agentid 1001 the result should be
productname status
pepsi true
miranda false
coke false
fanta truethe query i tried is :
select a.productname, status = case b.product_id
when 1 then 'True' else 'False'
when 2 then 'True' else 'False'
when 3 then 'True' else 'False'
when 4 then 'True' else 'False' end
from products a, agent b where a.productid = b.productid and agentid ='1001'but doest work... any help is appreciated...
-
Hi guys... Actually i have a table called products which has 4 products in it say for example pepsi, miranda, coke and fanta. The table has two columns product id and productname as follows: 1 pepsi 2 miranda 3 coke 4 fanta now i have another table called agent, which has 3 columns such as agentid, region, productid. Now each agentid has a region and productid assigned to it as follows: 1001 DXB 1 1001 AJM 4 1002 ABC 2 1003 XYZ 3 1003 DEF 4 and so on. now in my application i need to make use of a query which gives me a result of all the product name with status for any particular agent id, for example: for agentid 1001 the result should be
productname status
pepsi true
miranda false
coke false
fanta truethe query i tried is :
select a.productname, status = case b.product_id
when 1 then 'True' else 'False'
when 2 then 'True' else 'False'
when 3 then 'True' else 'False'
when 4 then 'True' else 'False' end
from products a, agent b where a.productid = b.productid and agentid ='1001'but doest work... any help is appreciated...
select a.productname,
case when b.agentid is null then 'False' else 'True' end AS status
from products a
left outer join agent b on a.productid = b.productid
and b.agentid ='1001' -
select a.productname,
case when b.agentid is null then 'False' else 'True' end AS status
from products a
left outer join agent b on a.productid = b.productid
and b.agentid ='1001'