fecth data for each profile--Platfrom and skills

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

SQL

DECLARE @PROFILE VARCHAR(255)
set @PROFILE='DB Developer'

SELECT  @PROFILE=profile from TM_Profile where profile=@PROFILE



select @PROFILE as Profile,c.lookupvalue as platform,d.SkillLookupValue as skills from Reference c
INNER join
Reference_Skills d
on c.Lookupid = SkillLookupKey   where @PROFILE='DB Developer' and c.lookupvalue='Database'

Posted 30-Apr-14 5:17am

Member 10501509

Updated 30-Apr-14 23:27pm

v6

Add a Solution

Comments

CHill60 30-Apr-14 11:21am

What is your question?

Member 10501509 30-Apr-14 11:23am

i want to fetch data for each profile -->platfrom and skills

Member 10501509 30-Apr-14 11:24am

i have fetched data for one profile please u can see in above output. similarly i want to fetch for all profiles.

CHill60 30-Apr-14 12:15pm

You are not fetching data based on profile - it's hard-coded and not being read from table Reference or Reference_Skills. You are selecting values based on c.lookupvalue='Database'.
And the line SELECT @PROFILE=profile from TM_Profile where profile=@PROFILE is meaningless ... set a value to a field where that field is equal to the value just set in the line above.

Member 10501509 30-Apr-14 12:20pm

i am not getting it please help me

CHill60 30-Apr-14 12:22pm

What are the structures of your tables Reference and Reference_Skills?

Member 10501509 30-Apr-14 12:36pm

execute above table structure and data in ur local system

Member 10501509 30-Apr-14 12:36pm

I have given four table structure

CHill60 30-Apr-14 12:38pm

Whoa! I only needed the structure, not the data! Give a minute to make sense of all that

Member 10501509 30-Apr-14 12:39pm

please take ur own time

Member 10501509 30-Apr-14 12:44pm

please execute the above schema and data in ur local system so that u can understand very easly.

CHill60 30-Apr-14 12:48pm

No, I'm not going to do that. How is the Profile table linked to the others? There are no foreign keys defined in this lot.

Member 10501509 30-Apr-14 12:52pm

TM_profile table linked with TM_Specialization ( TM_profile.profileid=TM_Specialization .profileid)
Reference table is linked with Reference_Skills(Reference.LookupID=Reference_Skills.SkillLookupKey)

Member 10501509 30-Apr-14 12:54pm

Did u understand my problem now

CHill60 30-Apr-14 12:58pm

But there is no link between Profile/TM_Specialization and Reference/Reference_Skills ... meaning you can't extract data from the latter based on Profile

Member 10501509 30-Apr-14 13:03pm

Relation is there b/w TM_Profile/TM_Specialization and Reference/Reference_Skills u can see in the above tables

CHill60 30-Apr-14 13:26pm

Can't see it ... what column links TM_Profile and Reference?

Member 10501509 30-Apr-14 13:31pm

There is no relation TM_Profile and Reference.

Member 10501509 30-Apr-14 13:32pm

With relation we cant write query? ,please find alternate solution

Member 10501509 30-Apr-14 13:35pm

there relation between TM_Specialization and Reference
Specialization=Lookupvalue where Reference.lookupkey='Platform'

Member 10501509 30-Apr-14 12:55pm

use four table to produce the result.

Member 10501509 30-Apr-14 13:00pm

sample out put
Profile platform skills
DB Developer Database Derby aka Java DB
Software engg Java Eclipse
Software engg Java Jasper
Software engg DOT NET ASP.NET 2010
Software engg DOT NET c# 3.0

Nelek 30-Apr-14 12:48pm

OMG, what a code bunch.

Please read urgently and serveral times following link: best way to post code snippets[^]

CHill60 30-Apr-14 12:49pm

it's a doozy isn't it! :-)

Nelek 30-Apr-14 13:03pm

Thanks for the re-edit. It hurted the eyes before ;)

Member 10501509 30-Apr-14 13:04pm

Please help me to get solution for problem

Nelek 30-Apr-14 13:19pm

I am helping you to improve the quality of your posts and making them easier to understand for the rest of the people.
The easy to read and to understand, the more answers you will get.

So... Although I can not help with the SQL-Problem. I DO am helping you to get the solution for the problem

Member 10501509 30-Apr-14 11:47am

please me give solution its very urgent

Member 10501509 30-Apr-14 13:27pm

i should get sample out put by using above tables.
Profile platform skills 
DB Developer Database Derby aka Java DB 
Software engg Java Eclipse 
Software engg Java Jasper 
Software engg DOT NET ASP.NET 2010 
Software engg DOT NET c# 3.0

CHill60 30-Apr-14 13:48pm

If the following gets everything listed then you can use PIVOT to get the skills listed on a single line
SELECT Profile, R.LookupValue AS platform, RS.SkillLookupValue AS skills
FROM TM_Profile P
LEFT OUTER JOIN TM_Specialization S ON P.ProfileID = S.ProfileID
LEFT OUTER JOIN Reference R ON S.Specialization = R.LookupValue
LEFT OUTER JOIN Reference_Skills RS ON RS.SkillLookupKey = R.LookupKey
ORDER BY Profile, RS.SkillLookupValue

Member 10501509 30-Apr-14 14:00pm

I am getting skills column null

CHill60 30-Apr-14 14:36pm

Which means the join from TM_Specialization to Reference isn't right OR the join from Reference to Reference_Skills isn't right

Member 10501509 30-Apr-14 13:56pm

can use pivot and can you give solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)