x

What are the skills needed for project 'Internet Advertising'

SKILL

SKILL_ID    SKILL_DESCRIPTION
1   Visual Basic Programming
2   COBOL Programming
3   Java Programming
4   Project Management
5   Web Application Programming
6   Oracle Developer Programming
7   Oracle Database Administrator
8   Windows NT network Administrator
9   Windows 2000 Network Administrator

PROJECT

P_ID    PROJECT_NAME    CLIENT_ID   MGR_ID  PARENT_P_ID
1   Hardware Support Intranet   2   105  
2   Hardware Support Interface  2   103 1
3   Hardware Support Database   2   102 1
4   Teller Support System   4   105  
5   Internet Advertising    6   105  
6   Network Design  6   104 5
7   Exploration Database    5   102  

PROJECT_SKILL

P_ID    SKILL_ID
1   8
1   9
2   3
3   6
3   7
4   2
4   7
5   5
5   9
6   9
7   6
7   7

Edit: Would the answer look like this

Select  Project_Name,  P_ID,Skill_ID,  Skill_Description From  Project,  Skill,  Project_Skill Where  P_ID=Skill_ID,P_ID=Skill_description And Project_Name=Internet Advertising,  P_ID=5 And Skill_ID= 5,9, Skill_description= Web application programming, windows 2000 network administrator 
more ▼

asked Mar 26, 2010 at 02:02 PM in Default

Michael 4 gravatar image

Michael 4
9 9 9 9

Would the answer look like this Select Project_Name,P_ID,Skill_ID,Skill_Description From Project,Skill,Project_Skill Where P_ID=Skill_ID,P_ID=Skill_description And Project_Name=Internet Advertising, P_ID=5 And Skill_ID= 5,9, Skill_description= Web application programming, windows 2000 network administrator
Mar 26, 2010 at 03:35 PM Michael 4
@Michael , I moved your comment into the main body and formatted to make it easier to read. I hope this is what you intended.
Mar 26, 2010 at 04:42 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Michael, thank you for making an attempt a solution this time (I added your comment to the original question so it could be formatted for easier reading). You are on the right track, but you need to tweak a few things.

First, when you are joining multiple tables that have a column in the same name, you need to disambiguate for the server. This can be done by prefacing the column name with either the table name or table alias. If you do not know what a table alias is yet, just use the table name, aliases will come later.

Next, when you are using a string literal, you need to enclose it in single quotes. Such as 'Internet Advertising'

Then, you cannot separate criteria in your where clause with commas, they have to be separate by 'and's. In your attempt you seem to go back and forth and use both.

The big problem of course is that I think you are answering the wrong question. You are specifying the skills it should be returning. If I understand the question right, that should be the results you get not the criteria you use.

If you want to try again and post another attempt here, I think many of us would be happy to comment on it. If including a code sample, please either post it as an answer or edit your original question. You cannot format code samples in a comment. Also, please remember to format the code samples. It is so much easier to read that way.

more ▼

answered Mar 26, 2010 at 04:49 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left
Select Skill_ID, Skill_Description 
From Skill,
Where Select_Id IN ( Select Skill_ID 
                     From Project_Skill
                            Where P_ID IN ( Select P_ID 
                                            From Project
                                            Where Project_ID = 'Internet Advertising'
                                           )
                    )

Brief Explanation: Above I use three queries; One main select query and two sub queries(or nested queries)

  1. Lets start with the last one: What last sub query does is find the project ID of 'Internet Advertising' using the project table then it will pass the value to the middle query.
  2. The middle sub query will return the value of the ID of the skills(Skill_ID) needed based on the project ID. Once it calculates the Skill_ID using the PROJECT_SKILL table it will pass the value to the first query
  3. Now the first query has all the information and will use that to return the skill description along with the skill_id from the SKILL table.

Also you can solve these problem easily using JOINs.

I dont know if i helped or confused you here but you should be able to get the idea and research a little bit and you soon be able to solve the problem. Once again try a little bit harder to solve your homework by yourself before you post here.

Good Luck!!

more ▼

answered Mar 26, 2010 at 05:20 PM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

Dani - you might want to replace the curly braces {} with round ones ()
Mar 26, 2010 at 05:24 PM Matt Whitfield ♦♦
Thanks. It feels weird and I was wondering why? :-)
Mar 26, 2010 at 05:32 PM DaniSQL
@Michael, I think the solution with joins tends to be easier to read, but Dani gave you a valid solution with a good explanation.
Mar 26, 2010 at 06:16 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

You have provided nothing more than a query. You need to provide some detail and what your actual question is.

more ▼

answered Mar 26, 2010 at 02:56 PM

CirqueDeSQLeil gravatar image

CirqueDeSQLeil
4k 10 11 15

(comments are locked)
10|1200 characters needed characters left

Is this April the fool? Michael, I am starting to feel you are purposely trying to annoy the community!

more ▼

answered Mar 26, 2010 at 03:00 PM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x369
x74

asked: Mar 26, 2010 at 02:02 PM

Seen: 1527 times

Last Updated: Mar 26, 2010 at 04:41 PM