match the correct value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

match the correct value

Author

Topic

bigsinc
Starting Member

7 Posts

Posted - 2008-04-25 : 08:14:14

I have this sql statement:

SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills
FROM PersonSkills INNER JOIN....

How can I choose a certain value from PersonSkills.Skills?

for example, I would like to choose a value from PersonSkills.Skills that matches a certain product. I can do the Max/Min value but the selected value needs to match the product.

The tables that I have are:
PersonSkills:
id, ProductID, Skills

Product:
id, ProductName

Ive been reading and playing around with this without success.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 08:17:57

Follow the advice given here
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

and you will get a working answer faster!

E 12°55'05.25"
N 56°04'39.16"

bigsinc
Starting Member

7 Posts

Posted - 2008-04-25 : 09:08:14

quote:
Originally posted by Peso

Follow the advice given here
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

and you will get a working answer faster!

E 12°55'05.25"
N 56°04'39.16"

Sorry about that :)

What I am doing is assigning skills (int) to a product.

How can I select a specific skill value (in my case from the PersonSkills.Skills table)

This is my sql statement:

SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, MAX(PersonSkills.Skills) AS Skills
FROM PersonSkills INNER JOIN
Products ON PersonSkills.ProductID = Products.ID INNER JOIN
Persons ON PersonSkills.PersonID = Persons.ID INNER JOIN
Companies ON Persons.Company = Companies.Id INNER JOIN
Countries ON Companies.Country = Countries.ID INNER JOIN
Regions ON Countries.Region = Regions.ID INNER JOIN
ProductGroups ON Products.ProductGroup = ProductGroups.ID INNER JOIN
ProductGeneration ON Products.ProductGeneration = ProductGeneration.ID INNER JOIN
Vendors ON Products.Vendor = Vendors.ID
WHERE Persons.Active=1 AND (Countries.ID =76) GROUP BY Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName

The PersonSkill.Skill table:
id, PersonID, ProductID, Skills

Then I have the Persons, and Products tables.

I need to select the skill value that matches the same product.
As you can see, at the moment Im using MAX(Persons.Skill) which only returns the highest value.

Ive tried to link the PersonSkill.PersonID to the Persons.ID and I also tried to link the PersonSkill.ProductID to the Products.ID but with out success.
By link, I mean I used Join.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 09:27:03

You didn't pay too much attention to the blog post I sent to you with a link, did you?

E 12°55'05.25"
N 56°04'39.16"

bigsinc
Starting Member

7 Posts

Posted - 2008-04-25 : 09:57:31

quote:
Originally posted by Peso

You didn't pay too much attention to the blog post I sent to you with a link, did you?

E 12°55'05.25"
N 56°04'39.16"

1. State the question
How can I select a specific value from a specific table?

2. Please post the DDL of your tables
Im sorry but Im not sure what I should do here.
Im using web developer 2008 Express edition. Where can I can generate the sql script.

3. Post some sample data in the form of DML
This is the sql statement.

SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, MAX(PersonSkills.Skills) AS Skills
FROM PersonSkills INNER JOIN
Products ON PersonSkills.ProductID = Products.ID INNER JOIN
Persons ON PersonSkills.PersonID = Persons.ID INNER JOIN
Companies ON Persons.Company = Companies.Id INNER JOIN
Countries ON Companies.Country = Countries.ID INNER JOIN
Regions ON Countries.Region = Regions.ID INNER JOIN
ProductGroups ON Products.ProductGroup = ProductGroups.ID INNER JOIN
ProductGeneration ON Products.ProductGeneration = ProductGeneration.ID INNER JOIN
Vendors ON Products.Vendor = Vendors.ID
WHERE Persons.Active=1 AND (Countries.ID =76) GROUP BY Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName

The reason why Im not giving you any possible solutions to this problem is because I kind of lost.

5. Post the expected results
At the moment I am getting all the right results but the only field that I am having troubles with is the PersonSkills.Skills because I am choosing the MAX value instead of the value that matches the choosen product.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 10:07:46

Sample data?

E 12°55'05.25"
N 56°04'39.16"

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-04-29 : 15:22:55

quote:
Originally posted by bigsinc
2. Please post the DDL of your tables
Im sorry but Im not sure what I should do here.
Im using web developer 2008 Express edition. Where can I can generate the sql script.

Right click on the table in Sql Server Management Studio. Choose "Script Table As->CREATE to->New Query Window". Repeat for each table as needed for the query you need.

quote:
Originally posted by bigsinc
3. Post some sample data in the form of DML
This is the sql statement.

How does "Post some sample data in the form of DML" translate to "This is the sql statement."? POST SOME SAMPLE DATA! Preferably in the form:


INSERT INTO <YourTable>
  SELECT <Row1Col1Data>, <Row1Col2Data>, etc UNION ALL
  SELECT <Row2Col1Data>, <Row2Col2Data>, etc.

Repeat this for each and every single table and column of information that's relevant to getting the results you want.

quote:
Originally posted by bigsinc
5. Post the expected results
At the moment I am getting all the right results but the only field that I am having troubles with is the PersonSkills.Skills because I am choosing the MAX value instead of the value that matches the choosen product.

How do we know that? You haven't given us the DML or sample data we need in order to determine that.

If you're going to come here and ask for help, please pay attention to what the people who try and help you are saying. If you refuse to listen and cooperate, no one can help you; trying is just a waste of time. If you can't help us help you, don't expect to get the help you want.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources