May 14, 2009 at 2:22 pm
I have 3 table, below is the structure
Table 1
-------
ID_Number
Skill_ID
Table 2
-------
ID_Number
Full_Name
Country
Table 3
-------
ID_Number
Email_Address
i made a view that connects ID_Number from table 2, to table 1, and from table 2 to table 3
and i added Skill_id from table 1, ID_Number, full_name and Country from table 2 and Email_Address from table 3
when i open the view i see duplicate records per person.
What i want to do is to query this view with this where clause
select distinct..... where skill_id in (10, 13) or country in (US, CANADA)
i can't figure out what to put between distinct and where to make it not to give me duplicate records 🙁
Any help would be appreciated
Thanks
May 15, 2009 at 12:26 am
Hi,
For better assistance in answering your question
See this link http://www.sqlservercentral.com/articles/Best+Practices/61537/
Then post your codes.
ARUN SAS
May 15, 2009 at 6:38 am
The problem your facing is that 1 person might have more than one record in some of the other tables
you can have a look at the contents of the other tables, use the count(*) function to see if there are any duplicats
select id_number, count(*)
from table_1
group by id_number
having count(*) > 1
do that for all 3 tables, if any rows are returned then your view is going to contain more than 1 row for some people.
When it comes to your view, it depends what you are selecting out of it.
If you were just selecting the id_number then
select distinct id_number
from view_name
will give you a distinct list of the ID_numbers
however if you want the email address as well then
select distinct id_number, email_address
from view_name
will give you distinct combinations of email_address and id_number. So if any person has more than 1 email address they will get 2 rows in the resulting record set.
If you could post the SQL you are using to create the view, table definitions (preferably as a create table script) and some sample data then we can see if there is anything wrong with your SQL which might result in undesirable consequences 😎
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply