INNER JOIN - 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

INNER JOIN

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-10 : 08:26:16

Hi guys,

Im trying to create a unique table (FinalTable), using Table1 and Table2 from the same database.

FinalTable: I need to have the info from Table1 and 2. Not all of it. Imagine that I have the following Columns in each table:

Eyes_Colour Name_Student

In my Final_Table I need all the Eyes_Colour and Name_Student that are the same in Table1 and Table2. So the info that I will have it will be, all those students that have the same: Name_Student and Eyes_Colour, in both tables (Table1 and 2)

The query that im using its:

SELECT Eyes_Colour, Name_Student INTO Final_Table FROM Table1
INNER JOIN Table2
ON Table1.Name_Student = Table2.Name_Student AND Table2.Eyes_Colour = Table1.Eyes_Colour

msg 209, Level 16, State 1, Line 2
Ambiguous column name 'Name_Student'
msg 209, Level 16, State 1, Line 2
Ambiguous column name 'Eyes_Colour'

Any idea?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-10 : 08:39:35

[code]
SELECT Eyes_Colour, Table1.Name_Student INTO Final_Table FROM Table1
INNER JOIN Table2
ON Table1.Name_Student = Table2.Name_Student AND Table2.Eyes_Colour = Table1.Eyes_Colour
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ocean
Starting Member

22 Posts

Posted - 2012-05-10 : 08:51:30

Still not working ...

I have the same msg 209, level 16, state 1, line 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-10 : 09:02:01

add the prefix to Eyes_colour also

KH
[spoiler]Time is always against us[/spoiler]

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-10 : 09:03:42

SELECT Table2.Eyes_Colour, Table1.Name_Student INTO Final_Table FROM Table1
INNER JOIN Table2
ON Table1.Name_Student = Table2.Name_Student AND Table2.Eyes_Colour = Table1.Eyes_Colour

Is working that way, using both tables in SELECT.

But the info that I have, is repeating, I dont know if its because, is the number of times that they match, and thats why I have the same rows depending on the student or why.

How Im able just, to have as output, the Name and the Colour one time?

Using COUNT. Where do I have to use COUNT in the query?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-10 : 09:06:31

it is repeating because for each Name_Student & Eyes_Colour in table1 there are more than 1 such records in table2.

You can use DISTINCT to only return the distinct value

KH
[spoiler]Time is always against us[/spoiler]

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-10 : 09:26:57

Perfect!

Thanks

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-10 : 12:22:41

If you want to combine the data from both the table .You can use the Union to retrive distinct records from both table

select * into tab3 from
( select Eyes_Colour,Name_Student from tab1
union
select Eyes_Colour,Name_Student from tab2) A

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-10 : 12:42:05

Thats perfect!!

quote:
Originally posted by vijays3

If you want to combine the data from both the table .You can use the Union to retrive distinct records from both table

select * into tab3 from
( select Eyes_Colour,Name_Student from tab1
union
select Eyes_Colour,Name_Student from tab2) A

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-10 : 17:53:56

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources