| Author |
 Topic  |
|
|
jfm
Posting Yak Master
134 Posts |
 Posted - 05/10/2012 : 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)
Singapore
16746 Posts |
Posted - 05/10/2012 : 08:39:35
|
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
KH
Time is always against us
|
 |
|
|
ocean
Starting Member
22 Posts |
Posted - 05/10/2012 : 08:51:30
|
Still not working ...
I have the same msg 209, level 16, state 1, line 1
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 05/10/2012 : 09:02:01
|
add the prefix to Eyes_colour also
KH
Time is always against us
|
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 05/10/2012 : 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)
Singapore
16746 Posts |
Posted - 05/10/2012 : 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
Time is always against us
|
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 05/10/2012 : 09:26:57
|
Perfect!
Thanks |
|
|
|
vijays3
Constraint Violating Yak Guru
India
311 Posts |
Posted - 05/10/2012 : 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
134 Posts |
Posted - 05/10/2012 : 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
India
311 Posts |
Posted - 05/10/2012 : 17:53:56
|
| Welcome |
|
|
| |
Topic |
|
INNER JOIN
