Help with a view and distinct

Question

Help with a view and distinct

Labneh

Old Hand

Points: 329
More actions
December 26, 2008 at 5:30 am

#195378

Hi all,
This is my first time here and i would like some help with a view if it is possible...
i have a view that has three tables. all three tables are linked by ID_Number.
when i just run it without any where clause, it brings all the records that i need. there is no problem here... the problem is when i execute it with an (in) where clause.
for example, where skill_ID in (900,910,920,930)
if i did the above where clause, if a person has multiple skills that match, that record comes up more that one time... basically if that person has the 900 and 910, the record will show up twice.
so the question, how can i make it so i can just get that person once?
i tried using distinct, but that didn't help, it just brings the same records again.
oh, and i forgot to mention that there will be more that one condition, for example, where skill_ID in (900,910,920,930) and country ('usa,canada')
thanks in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

can you post the actual query? that would allow us to give an exact answer; for here, i think general suggestions will just lead to more questions and confusion.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Labneh Old Hand Points: 329 More actions · Answer 2

here is the query

SELECT dbo.GRADUATES.ID_NUMBER, dbo.GRADUATES.EMAIL, dbo.GRADUATES.FULLNAME, dbo.OCCUPATIONALDATA.EMPLOYMENT_EMAIL, dbo.SKILLS_DATA.SKILLS_ID,dbo.GRADUATES.CELLPHONE, dbo.GRADUATES.COUNTY

FROM dbo.SKILLS_DATA INNER JOIN

dbo.GRADUATES ON

dbo.SKILLS_DATA.ID_NUMBER = dbo.GRADUATES.ID_NUMBER INNER JOIN

dbo.OCCUPATIONALDATA ON dbo.GRADUATES.ID_NUMBER = dbo.OCCUPATIONALDATA.ID_NUMBER

WHERE (dbo.SKILLS_DATA.SKILLS_ID IN (900, 910)) AND (dbo.GRADUATES.COUNTY IN ('warren'))

thanks Lowell

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Distinct won’t help because the records are not the same. Each record has a different Skill_ID.

If you don’t care about the skill_id column, you can omit it from the select list and then use select distinct. If you do want to see exactly one skill_id then how will you select which one to see? You can use min or max functions on skill_id column and group by on the rest of the columns.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Labneh Old Hand Points: 329 More actions · Answer 4

Adi

thanks alot... removing skill_id worked!!!!

thanks a lot again 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 5

DISTICT gets the distinct items for the columns you selected...if you look at your query results, the only itemt aht changes for the repeated persons that got selected is dbo.SKILLS_DATA.SKILLS_ID,, so if you exclude that and add distinct, you'd get the results you wanted;

SELECT DISTINCT

dbo.GRADUATES.ID_NUMBER,

dbo.GRADUATES.EMAIL,

dbo.GRADUATES.FULLNAME,

dbo.OCCUPATIONALDATA.EMPLOYMENT_EMAIL,

--dbo.SKILLS_DATA.SKILLS_ID,

dbo.GRADUATES.CELLPHONE,

dbo.GRADUATES.COUNTY

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Labneh Old Hand Points: 329 More actions · Answer 6

Sorry, it is been a long time but i just got back to work on this project...

when i remove skill_id from the view, execute the statement

SELECT dbo.GRADUATES.ID_NUMBER, dbo.GRADUATES.EMAIL, dbo.GRADUATES.FULLNAME, dbo.OCCUPATIONALDATA.EMPLOYMENT_EMAIL, dbo.GRADUATES.CELLPHONE, dbo.GRADUATES.COUNTY

FROM dbo.SKILLS_DATA INNER JOIN

dbo.GRADUATES ON

dbo.SKILLS_DATA.ID_NUMBER = dbo.GRADUATES.ID_NUMBER INNER JOIN

dbo.OCCUPATIONALDATA ON dbo.GRADUATES.ID_NUMBER = dbo.OCCUPATIONALDATA.ID_NUMBER

WHERE (dbo.SKILLS_DATA.SKILLS_ID IN (900, 910)) AND (dbo.GRADUATES.COUNTY IN ('warren'))

it gives me this error

Error Message: Invalid column name Skill_id

any help would be appreciated.