October 2, 2006 at 12:12 pm
Hello everyone. I m using SQL Server 2000, and i have the following tables
Table 1:
Session_ID
Session_Name
LogUserType_ID
SystemUserID
Buyer_ID
Supplier_ID
IPAddress
In Table 2 i have:
SystemUser_ID
User_Name
In Table 3 i have followong fields:
Buyer_ID
BuyerName
In Table 4 i have:
Supplier_ID
Supplier_Name
Now i want to write a Stored Procedure for getting data from tables 1,2,3,4 but based on condition .The condition is based on the table 1 's field "LogUserTypeID", If LogUserTypeId=1 then i get some data from Table 1 and get all data from table 2, and if "LogUserTypeID=2 ,then i get some data from Table 1 and get all data from table 3, and if "LogUserTypeID=3 then i get some data from Table 1 and get all data from table 4.
I try to write the follwong code (logic or flow of working)
If @LogUserType_ID=1
select Session_ID, Session_Name, IPAddress,SystemUser_ID,User_Name
from Table 1, Table 2
If @LogUserType_ID=2
select Session_ID, Session_Name, IPAddress, Buyer_ID, BuyerName
from Tbale 1, Table 3
else
select Session_ID, Session_Name, IPAddress, Supplier_ID, Supplier_Name
from Table1, Table 4
As i have no idea, abt LogUserTypeID, 1stly i want to get "LogUserTypeID" from Table 1 and then apply the following conditions, and the other thing is if u noticed,i m using 3 "SELECT Stmts " , I dont want to do this by using this technique, i want to reduce my code and try to use only one (1) SELECT Stmt for this , plz tell me how i do this .
its very urgent
plz reply me asap coz i m stuck.
October 2, 2006 at 12:34 pm
Is there any relationship between table1 and table2, table3 and table4. It seems to me the SystemUser_id, Buyer_ID, supplier_ID in table1 should be related to the same field name in Table1.
October 2, 2006 at 2:07 pm
Is there any secret reason why SystemUser, Buyer and Supplier must be in different tables?
You return it in the same column, that must give you a hint that they are actually the same entity, just playing different roles in different situations.
_____________
Code for TallyGenerator
October 2, 2006 at 2:50 pm
Create a view using this code and u can do select on the view.
Select Table1.Session_ID,Table1.Session_Name,Table1.LogUserType_ID,Table1.SystemUserID,Table2.[User_Name],
Table1.Buyer_ID,Table3.BuyerName,
Table1.Supplier_ID,Table4.Supplier_Name,
Table1.IPAddress
from Table1 LEFT OUTER JOIN Table2 ON Table1.SystemUserID = Table2.SystemUser_ID
LEFT OUTER JOIN Table3 ON Table1.Buyer_ID = Table3.Buyer_ID
LEFT OUTER JOIN Table4 ON Table1.Supplier_ID = Table4.Supplier_ID
Hope this helps.
Thanks
Sreejith
October 2, 2006 at 10:40 pm
Hello Sreejith, u taking my problem in a wring direction if u see my problem i have a LogUserTypeID in Table 1 , ( in this i have values 1,2,3 ), Now 1stly i get the LogUserTypeID from table 1 n store in a var
if var=1 then select from table1 and select from table 2
if var=2 then select from table1 and select from table 3
in else case
select from table1 and select from table 4
i hope now u got my point in ur case its retruns all valuse from all table without checking
plz tell me how i get the var value as i have no idea how to store a value which is returning from select statement.
plz reply me asap
its very urgent n till i have not find any good solution
October 2, 2006 at 10:53 pm
select T1.Session_ID, T1.Session_Name, T1.IPAddress,
COALESCE(T2.SystemUser_ID, T3. Buyer_ID, T4.Supplier_ID) as PartyID,
COALESCE(T2.User_Name, T3.BuyerName, T4.Supplier_Name) as PartyName
FROM Table1 T1
LEFT JOIN Table2 T2 ON T1.SystemUser_ID = T2.SystemUser_ID AND T1.LogUserType_ID = 1
LEFT JOIN Table3 T3 ON T1.Buyer_ID = T2.Buyer_ID AND T1.LogUserType_ID = 2
LEFT JOIN Table4 T4 ON T1.Supplier_ID = T2.Supplier_ID AND T1.LogUserType_ID = 3
WHERE T1.LogUserType_ID = @LogUserType_ID
_____________
Code for TallyGenerator
October 2, 2006 at 11:13 pm
Thanx Sergity for ur reply, now plz tell me
i have a select stmt which retruns values e.g
selct LogUserType_ID from table 1
how i store the result of this select stmt in varibale , coz i need that var for further processing , as i want to apply some conditions n get data according to that conditon
if i write this :
declare var int
var=select LogUserType_ID from table 1
then sql server throws the error systax near = is incorrect
plz tell me how i get the result in var
plz reply me asap
October 2, 2006 at 11:26 pm
Why are you asking me?
BOL is on you desk, just press F1.
Find SELECT in index, then choose SELECT @local_variable.
I would not explain better.
BTW, don't use cursors. Think.
_____________
Code for TallyGenerator
October 2, 2006 at 11:31 pm
hello Sergiy, i did this thing earlier but in my case my query returns more then 1 value so thats y me posting question here, if u dont want to answer then simple u no need to reply me . But i think Forums r made for peoples help, if someone post the problems its mean it cant find the sloution anywhere.
anyways thanx for replying.
October 2, 2006 at 11:43 pm
You did not read it carefully.
Read again. There is an answer on your question over there.
And again. DON'T USE CURSORS.
I don't see any reason why you need that @var if not to use it in cursor.
Do your task in the right way and you not gonna face that problem.
_____________
Code for TallyGenerator
October 2, 2006 at 11:50 pm
Thanx Sergiy, now i got ur point, and i m sorry if i m gonna harsh , as i m stuked with this problem since last night n gone mad
i hope u dont mind it. n again thanx for ur reply .now i will try this logic n hope for the best.
once again Thanku so much .
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply