SQL advice - Dev Shed

Dev Shed Forums Sponsor:

July 1st, 2009, 09:05 AM

eco22

Registered User

Join Date: Jul 2009

Posts: 5

Time spent in forums: 51 m 49 sec
Reputation Power: 0

SQL advice

Hi all,

this is my first post and I'm a newb in MS SQL and have a basic understanding in SQL but I hope to get better at it soon

I have a problem and can't figure out how to setup my tables properly.

You will find table constructs and their values, the SQL query and it's output.

To me the Query is wrong, not in the way it returns the result but in it's use.

I am trying to retrieve the Language Skills of my users.

Table Request

Code:

IDRequest	       int
Name			 nvarchar(50)
LanguageSkillNL	 int
LanguageSkillFR	 int
LanguageSkillEN	 int

Table Values

Code:

1	John	3	1	1
2	Frank	3	2	1

Table LanguageSkill

Code:

IDLanguageSkill	int
LanguageSkill	nchar(30)

Table Values

Code:

1	Good                          
2	Average                       
3	Bad

SQL Query

Code:

SELECT		t1.IDRequest,
			t1.Name,
			t2.LanguageSkill as 'Dutch',
			t3.LanguageSkill as 'French',
			t4.LanguageSkill as 'English'
	FROM Request t1
	LEFT JOIN LanguageSkill t2
		ON (t1.LanguageSkillNL = t2.IDLanguageSkill)
	LEFT JOIN LanguageSkill t3
		ON (t1.LanguageSkillFR = t3.IDLanguageSkill)
	LEFT JOIN LanguageSkill t4
		ON (t1.LanguageSkillEN = t4.IDLanguageSkill)
	ORDER BY IDRequest;

SQL Output

Code:

1	John	Bad  	Good		Good
2	Frank	Bad	Average	Good

How would you deal with the above without hardcoding the values in the Request Table?

Many thanks for your help and/or pointers to documentation.
-eco22

July 1st, 2009, 09:55 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 20,792

Time spent in forums: 2 Months 1 Week 3 Days 20 h 24 m 10 sec
Reputation Power: 2482

i would do it like this --

Code:

CREATE TABLE Persons
( ID   INTEGER      NOT NULL PRIMARY KEY
, Name NVARCHAR(50) NOT NULL
);
INSERT INTO Persons VALUES ( 1, 'John' );
INSERT INTO Persons VALUES ( 2, 'Frank' );

CREATE TABLE Languages
( Lang  CHAR(2)     NOT NULL PRIMARY KEY
, Descr VARCHAR(10) NOT NULL
);
INSERT INTO Languages VALUES ( 'NL', 'Dutch' );
INSERT INTO Languages VALUES ( 'FR', 'French' );
INSERT INTO Languages VALUES ( 'EN', 'English' );

CREATE TABLE Requests
( ID   INTEGER      NOT NULL 
, FOREIGN KEY ( ID ) REFERENCES Persons ( ID )
, Lang CHAR(2)      NOT NULL
, FOREIGN KEY ( Lang ) REFERENCES Languages ( Lang )
, PRIMARY KEY ( ID , Lang )
, Skill VARCHAR(7)  NOT NULL
);
INSERT INTO Requests VALUES ( 1, 'NL', 'Bad' );
INSERT INTO Requests VALUES ( 1, 'FR', 'Good' );
INSERT INTO Requests VALUES ( 1, 'EN', 'Good' );
INSERT INTO Requests VALUES ( 2, 'NL', 'Bad' );
INSERT INTO Requests VALUES ( 2, 'FR', 'Average' );
INSERT INTO Requests VALUES ( 2, 'EN', 'Good' );

SELECT Persons.ID
     , Persons.Name
     , Requests.Lang
     , Requests.Skill
  FROM Persons
LEFT OUTER
  JOIN Requests
    ON Requests.ID = Persons.ID

results --

Code:

ID  Name   Lang Skill   
 1  John   EN   Good    
 1  John   FR   Good    
 1  John   NL   Bad     
 2  Frank  EN   Good    
 2  Frank  FR   Average 
 2  Frank  NL   Bad

alternatively, to spell out the languages...

Code:

SELECT Persons.ID
     , Persons.Name
     , Languages.Descr
     , Requests.Skill
  FROM Persons
LEFT OUTER
  JOIN Requests
    ON Requests.ID = Persons.ID    
LEFT OUTER
  JOIN Languages
    ON Languages.Lang = Requests.Lang

results --

Code:

ID  Name   Lang     Skill
 1  John   English  Good
 1  John   French   Good
 1  John   Dutch    Bad
 2  Frank  English  Good
 2  Frank  French   Average
 2  Frank  Dutch    Bad

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

Last edited by r937 : July 1st, 2009 at 09:58 AM.

July 1st, 2009, 07:26 PM

eco22

Registered User

Join Date: Jul 2009

Posts: 5

Time spent in forums: 51 m 49 sec
Reputation Power: 0

Much appreciated r937,

I'll give it a go... It certainly looks more 'ethical'

Thanks
-eco22

Viewing: Dev Shed Forums > Databases > MS SQL Development > SQL advice

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!