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 followsas per data in MS Sqlmy tables are as follows1)material fields are -- material_id,project_type,project_id,qty, --2)AB_Corporate_project fields are-- ab_crp_id,custname,contract_no,field_no3)Other_project fields are -- other_proj_id,other_custname,pofor ex :vales in table's areAB_Corporate_project=====================ab_crp_id custname contract_no field_no 1 abc 234 66 2 xyz 33 20Other_project============other_proj_id other_custname po 1 xxcx 111 2 dsd 222material=========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 10i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_projectsample 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. pocase if m.project_type = 'AB Corporate' then select * from AB_Corporate_project where ab.ab_crp_id = m.project_idelse if m.project_type = 'Other Project' then select * from Other_project where op.other_proj_id=m.project_idendfrom material m,AB_Corporate_project ab,Other_project opbut this query not work,also it gives errorsi want sql query to show data as followsmaterial_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 outputplz send a sql queryuday |
|
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 ALLSELECT 2, 'xyz', 33, 20DECLARE @Other_project TABLE (other_proj_id INT, other_custname VARCHAR(100), po INT)INSERT INTO @Other_project SELECT 1, 'xxcx', 111 UNION ALLSELECT 2, 'dsd', 222DECLARE @material TABLE (material_id INT, project_type VARCHAR(100), project_id INT, qty INT)INSERT INTO @material SELECT 1, 'AB Corporate', 1, 3 UNION ALLSELECT 2, 'AB Corporate', 2, 4 UNION ALLSELECT 3, 'Other Project', 1, 7 UNION ALLSELECT 4, 'Other Project', 2, 10/*SELECT * FROM @materialSELECT * FROM @AB_Corporate_projectSELECT * FROM @Other_project*/SELECT m.material_id , m.project_type, m.project_id, AB.custname, OP.other_custname, m.qtyFROM @material MLEFT 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' |
|
|
|
|
|