Table 1
RegName ToolID
Ex1 124
Ex2 124
Table 2
DepName ToolID
EX5 124
EX6 124
Select c.Regname, select d.depname
From table1 as c
Join table2 as d
On c.toolID = d.toolID
Where c.toolID = ‘124’
I figure it should return only two rows but I am getting four rows return.
What I am doing wrong ?
Based on the query (Which I think I gave you), it is returning exactly what you asked it to. Each row in table 1 maps to each row in table 2, so you get something like
EX1, EX5
EX1, EX6
EX2, EX5
EX2, EX6
You will need some other key or relationship or where clause to filter the data further. The architecture of your tables does not map a single row from table 1 to a single row in table 2.
"What I hear, I forget; What I see, I remember; What I do, I understand." --Confucius
Remeber to Mark as Answer if this post helped you.
Marked as answer by Chen Yu - MSFT on May 03, 2012 02:40 AM
To get a meaningful/useful view of the two tables, you normally need to determine an identifying field from each table that can then be used in the ON clause in a JOIN.
THen in your view:
SELECT T1.*, T2.* FROM T1 JOIN T2 ON T1.IDFIELD1 = T2.IDFIELD2
You mention no fields are "common", but although the identifying fields may not have the same name or even be the same data type, you could use the convert / cast functions to join them in some way.
You just need to delete the second select in your query, it will show you what you want.
The query I tested:
declare @Table1 table(
RegName varchar(10), ToolID int)
insert into @Table1 select 'Ex1',124 union all
select 'Ex2',124
declare @Table2 table(
DepName varchar(10),ToolID int )
insert into @Table2 select 'Ex5',124 union all
select 'EX6',124
Select c.Regname,d.depname
From @Table1 as c
Join @Table2 as d
On c.toolID = d.toolID
Where c.toolID = 124
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
k1vuc01
0 Points
15 Posts
Help with sql table join
Apr 24, 2012 04:46 PM|LINK
grundebar
Contributor
4515 Points
726 Posts
Re: Help with sql table join
Apr 24, 2012 04:56 PM|LINK
Based on the query (Which I think I gave you), it is returning exactly what you asked it to. Each row in table 1 maps to each row in table 2, so you get something like
EX1, EX5
EX1, EX6
EX2, EX5
EX2, EX6
You will need some other key or relationship or where clause to filter the data further. The architecture of your tables does not map a single row from table 1 to a single row in table 2.
Remeber to Mark as Answer if this post helped you.
MahadPK
Participant
778 Points
225 Posts
Re: Help with sql table join
Apr 24, 2012 04:58 PM|LINK
To get a meaningful/useful view of the two tables, you normally need to determine an identifying field from each table that can then be used in the ON clause in a JOIN.
THen in your view:
You mention no fields are "common", but although the identifying fields may not have the same name or even be the same data type, you could use the convert / cast functions to join them in some way.
Chen Yu - MS...
All-Star
21584 Points
2493 Posts
Microsoft
Re: Help with sql table join
Apr 27, 2012 07:50 AM|LINK
Hi,
You just need to delete the second select in your query, it will show you what you want.
The query I tested:
Thanks.
Feedback to us
Develop and promote your apps in Windows Store