SQL query
-
I am creating a project in .net and i have to retrieve some data from tables on the conditions: 1) A user can create multiple profiles,upto 20 in 20 different categories (The data is stored in "profiles" table) 2) There is a "project" table in which projects are stored in 20 different categories 3) project categories are same as profile categories The problem is i want to retrieve all the projects where project categories are same as a users categories For example: if user A has profiles in categories x,y, and z I want to retrieve the details of projects in categories x,y, and z. number of profiles for different users are differen someone has 2 profiles while other one has 5 profiles it can be varied If anyone can help me in SQL Query I will be highly thankfull
-
I am creating a project in .net and i have to retrieve some data from tables on the conditions: 1) A user can create multiple profiles,upto 20 in 20 different categories (The data is stored in "profiles" table) 2) There is a "project" table in which projects are stored in 20 different categories 3) project categories are same as profile categories The problem is i want to retrieve all the projects where project categories are same as a users categories For example: if user A has profiles in categories x,y, and z I want to retrieve the details of projects in categories x,y, and z. number of profiles for different users are differen someone has 2 profiles while other one has 5 profiles it can be varied If anyone can help me in SQL Query I will be highly thankfull
not getting wht u exactly wants ? sql statement for dis tables ? coz its very simple you hav to use inner joins dats it
Reasons are not Important but Results are Important
-
not getting wht u exactly wants ? sql statement for dis tables ? coz its very simple you hav to use inner joins dats it
Reasons are not Important but Results are Important
Let me first explain you the requirement Table Profiles: employeeid ------ category ----------------------------------- a ----------- Finance b ---------- HRD c ---------- Sales a ------------ HRD a ------------ Sales b ------------ Finance Table Work category --------- Title ------------------------------------------------ Finance ---------- maintaining annual accounts HRD ------------ Recruitement of skilled employees Sales ------------ Sales of newly launched product Finance----------- Creating Report (Quarterly) HRD--------------- Managing Employee's Feedback Now if I want to find work available the result should be like: for employee a- Category --------- Title --------------------------------------------- Finance ---------- maintaining annual accounts Finance----------- Creating Report (Quarterly) HRD--------------- Managing Employee's Feedback HRD ------------ Recruitement of skilled employees Sales ----------- Sales of newly launched product for employee b- Category -------- Title -------------------------------------------------------- Finance----------- Creating Report (Quarterly) Finance --------- maintaining annual accounts HRD ------------ Recruitement of skilled employees HRD--------------- Managing Employee's Feedback The problem is that I don't know the exact appearance of an employee in the profile table, and thats why I think joins are out of scope(I am not an expert in SQL) Hope this may clear some fog.Thanks.
modified on Tuesday, August 19, 2008 8:33 AM
-
I am creating a project in .net and i have to retrieve some data from tables on the conditions: 1) A user can create multiple profiles,upto 20 in 20 different categories (The data is stored in "profiles" table) 2) There is a "project" table in which projects are stored in 20 different categories 3) project categories are same as profile categories The problem is i want to retrieve all the projects where project categories are same as a users categories For example: if user A has profiles in categories x,y, and z I want to retrieve the details of projects in categories x,y, and z. number of profiles for different users are differen someone has 2 profiles while other one has 5 profiles it can be varied If anyone can help me in SQL Query I will be highly thankfull
what i got is there is 3 tables 1> category - Master table 2> profile - here employee with category(Transaction table) 3> project - here project in different category(Transaction table) where category is common in profile and project. select distinct profile.employeeid,project.projectname,project.categoryname from project inner join category on category.catid = project.project_catid inner join profile on profile.prof_catid = project.catid where profile.employeeid='a' if above is correct Now tell me where r u not getting? or May be i am not getting you
Reasons are not Important but Results are Important. Swati
-
Let me first explain you the requirement Table Profiles: employeeid ------ category ----------------------------------- a ----------- Finance b ---------- HRD c ---------- Sales a ------------ HRD a ------------ Sales b ------------ Finance Table Work category --------- Title ------------------------------------------------ Finance ---------- maintaining annual accounts HRD ------------ Recruitement of skilled employees Sales ------------ Sales of newly launched product Finance----------- Creating Report (Quarterly) HRD--------------- Managing Employee's Feedback Now if I want to find work available the result should be like: for employee a- Category --------- Title --------------------------------------------- Finance ---------- maintaining annual accounts Finance----------- Creating Report (Quarterly) HRD--------------- Managing Employee's Feedback HRD ------------ Recruitement of skilled employees Sales ----------- Sales of newly launched product for employee b- Category -------- Title -------------------------------------------------------- Finance----------- Creating Report (Quarterly) Finance --------- maintaining annual accounts HRD ------------ Recruitement of skilled employees HRD--------------- Managing Employee's Feedback The problem is that I don't know the exact appearance of an employee in the profile table, and thats why I think joins are out of scope(I am not an expert in SQL) Hope this may clear some fog.Thanks.
modified on Tuesday, August 19, 2008 8:33 AM
When emplpyeeid is
athen use query downSELECT profiles.Employeeid,profiles.Category,[work].Title FROM profiles,[work] WHERE profiles.category = [work].category and profiles.employeeid='a' ORDER BY [work].category ASCIf you have to show all employeeid's then use this another querySELECT profiles.Employeeid,profiles.Category,[work].Title FROM profiles,[work] WHERE profiles.category = [work].category and profiles.employeeid in(SELECT DISTINCT t1.Employeeid FROM profiles AS t1) ORDER BY profiles.Employeeid,[work].category ASC
I Love T-SQL "Don't torture yourself,let the life to do it for you."
-
what i got is there is 3 tables 1> category - Master table 2> profile - here employee with category(Transaction table) 3> project - here project in different category(Transaction table) where category is common in profile and project. select distinct profile.employeeid,project.projectname,project.categoryname from project inner join category on category.catid = project.project_catid inner join profile on profile.prof_catid = project.catid where profile.employeeid='a' if above is correct Now tell me where r u not getting? or May be i am not getting you
Reasons are not Important but Results are Important. Swati
Both of you thanks for suggestions but its not working. Let me make a final attempt to clear my issue if I create a stored procedure as: create procedure selectworks ( @maincategory nvarchar(50) ) as select profile.category, project.title, project.subcategory, project.expirydate from profile,project where profile.category=@maincategory and project.category=@maincategory this is going to give me result for one category which i am going to provide( @maincategory) but the issue is that the user has profiles in more than one category if he has profiles in 3 categories this procedure should run for 3 times for 3 different categories but the result should come combined similarly if a user has profiles in 5 categories this procedure should run for 5 times for 5 different categories but should give combined result. Thanks in advance..
-
Both of you thanks for suggestions but its not working. Let me make a final attempt to clear my issue if I create a stored procedure as: create procedure selectworks ( @maincategory nvarchar(50) ) as select profile.category, project.title, project.subcategory, project.expirydate from profile,project where profile.category=@maincategory and project.category=@maincategory this is going to give me result for one category which i am going to provide( @maincategory) but the issue is that the user has profiles in more than one category if he has profiles in 3 categories this procedure should run for 3 times for 3 different categories but the result should come combined similarly if a user has profiles in 5 categories this procedure should run for 5 times for 5 different categories but should give combined result. Thanks in advance..
1>First of all why you wants to pass category rather then employee id ??? 2>if there is any necessity of passing category then you can use of char index Or IN where you have to pass all the category separated by comma.
ex:- @maincategory='1,2,3'
1> CHARINDEX(',',@maincategory ) > 0
2> project.category IN (@maincategory)
Reasons are not Important but Results are Important. Swati
-
1>First of all why you wants to pass category rather then employee id ??? 2>if there is any necessity of passing category then you can use of char index Or IN where you have to pass all the category separated by comma.
ex:- @maincategory='1,2,3'
1> CHARINDEX(',',@maincategory ) > 0
2> project.category IN (@maincategory)
Reasons are not Important but Results are Important. Swati
Thanks a lot Swati I got the solution.Thanks...