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)
 how to display records using case statements

Author  Topic 

ummahajan
Starting Member

2 Posts

Posted - 2008-02-20 : 14:11:21

i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows


as per data in MS Sql

my tables are as follows
1)material
fields are -- material_id,project_type,project_id,qty, --

2)AB_Corporate_project
fields are-- ab_crp_id,custname,contract_no,field_no

3)Other_project
fields are -- other_proj_id,other_custname,po

for ex :
vales in table's are
AB_Corporate_project
=====================
ab_crp_id custname contract_no field_no
1 abc 234 66
2 xyz 33 20

Other_project
============
other_proj_id other_custname po
1 xxcx 111
2 dsd 222

material
=========
material_id project_type project_id qty
1 AB Corporate 1 3
2 AB Corporate 2 4
3 Other Project 1 7
4 Other Project 2 10

i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project


sample query i write :--

select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id,
ab.custname ,op.other_proj_id,op.other_custname,op. po
case if m.project_type = 'AB Corporate' then
select * from AB_Corporate_project where ab.ab_crp_id = m.project_id
else if m.project_type = 'Other Project' then
select * from Other_project where op.other_proj_id=m.project_id
end
from material m,AB_Corporate_project ab,Other_project op


but this query not work,also it gives errors

i want sql query to show data as follows


material_id project_type project_id custname other_custname qty
1 AB Corporate 1 abc -- 3
2 AB Corporate 2 xyz -- 4
3 Other Project 1 -- xxcx 7
4 Other Project 2 -- dsd 10

so plz help me how can i write sql query for to show the output
plz send a sql query



uday

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-21 : 02:22:30
Try this,

DECLARE @AB_Corporate_project TABLE (ab_crp_id INT, custname VARCHAR(100), contract_no INT, field_no INT)
INSERT INTO @AB_Corporate_project
SELECT 1, 'abc', 234, 66 UNION ALL
SELECT 2, 'xyz', 33, 20

DECLARE @Other_project TABLE (other_proj_id INT, other_custname VARCHAR(100), po INT)
INSERT INTO @Other_project
SELECT 1, 'xxcx', 111 UNION ALL
SELECT 2, 'dsd', 222

DECLARE @material TABLE (material_id INT, project_type VARCHAR(100), project_id INT, qty INT)

INSERT INTO @material
SELECT 1, 'AB Corporate', 1, 3 UNION ALL
SELECT 2, 'AB Corporate', 2, 4 UNION ALL
SELECT 3, 'Other Project', 1, 7 UNION ALL
SELECT 4, 'Other Project', 2, 10

/*
SELECT * FROM @material
SELECT * FROM @AB_Corporate_project
SELECT * FROM @Other_project
*/

SELECT m.material_id , m.project_type, m.project_id, AB.custname, OP.other_custname, m.qty
FROM @material M
LEFT JOIN @AB_Corporate_project AB ON AB.ab_crp_id = M.project_id AND M.project_type = 'AB Corporate'
LEFT JOIN @Other_project OP ON OP.other_proj_id = M.project_id AND M.project_type = 'Other Project'
Go to Top of Page
   

- Advertisement -