March 19, 2015 at 9:03 am
I have following two tables with two rows
/****** Object: Table [dbo]. Script Date: 03/19/2015 20:00:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].(
[ID] [int] NULL
) ON [PRIMARY]
END
GO
INSERT [dbo]. ([ID]) VALUES (1234)
INSERT [dbo]. ([ID]) VALUES (1234)
/****** Object: Table [dbo].[A] Script Date: 03/19/2015 20:00:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[A]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[A](
[ID] [int] NULL
) ON [PRIMARY]
END
GO
INSERT [dbo].[A] ([ID]) VALUES (1234)
INSERT [dbo].[A] ([ID]) VALUES (1234)
When I try to do
SELECT * FROM A
INNER JOIN B
on A.ID=B.ID
Then I get Cross join instead of Inner join .. Why is it like that any idea? you can try creating above tables with data and then try doing an inner join you will see that SQL Server will give you cartesian join in stead on Inner join isn't that Weird???
March 19, 2015 at 9:14 am
Why are you inserting the same values in each table twice? This might be causing the unexpected results?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
March 19, 2015 at 9:15 am
No, SQL's giving you an inner join, it's just that your data has every row in one table matching every row of the other table because they're all the same value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2015 at 9:18 am
GilaMonster (3/19/2015)
No, SQL's giving you an inner join, it's just that your data has every row in one table matching every row of the other table because they're all the same value.
Thanks for the reply Gila.
Actually, I have two tables in my database having company_ID columns
and I Need to do inner join on both tables in order to get matching rows from both tables based on Company_ID .
And I have same CompanyID's in both tables .. Can't I join them like that? Please help!
March 19, 2015 at 9:22 am
Alvin Ramard (3/19/2015)
Why are you inserting the same values in each table twice? This might be causing the unexpected results?
No, I just did it to explain the situation. I have two different temp tables both have Company_ID columns AND I need to do an INNER JOIN on both temp tables on the bases of Company_ID to get the matching rows.
And I have same Company_ID's in both tables but SQL Server is doing cross join for them instead of inner join.
March 19, 2015 at 9:26 am
No, SQL is not doing a cross join. It's doing an inner join.
When you have duplicate rows then you will see more rows coming back from a join than you may expect, because of what join means. An inner join returns all rows from table 2 that match for each row of table 1.
So, if we have in table 1 two rows with a join column value of 4 and in table 2 we have three rows with a join column value of 4, then what comes back is 6 rows.
You get 6 rows because the process is:
For the first row in table 1, return all matching rows from table 2. This returns three rows because there are three rows in table 2 with a value of 4
For the second row in table 1, return all matching rows from table 2. This also returns three rows because there are three rows in table 2 with a value of 4
Net result, your *inner* join has returned 6 rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2015 at 9:28 am
Zohaib Anwar (3/19/2015)
and I Need to do inner join on both tables in order to get matching rows from both tables based on Company_ID .And I have same CompanyID's in both tables .. Can't I join them like that? Please help!
Of course you can join them like that
FROM Table1 inner join Table2 on Table1.CompanyID = Table2.CompanyID
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2015 at 9:37 am
As previously stated, you're getting a join on matched rows only. The problem is that your first row on table A matches both rows on table B and the second row on table A matches both rows on table B.
If you correct the data as shown on the following example, you'll see that this is happening correctly.
CREATE TABLE dbo.B(
ID int NULL,
Name varchar(50)
);
INSERT dbo.B (ID,Name)
VALUES (1234,'Company A'),(1234,'Company B');
CREATE TABLE dbo.A(
ID int NULL,
Name varchar(50)
);
INSERT dbo.A (ID,Name)
VALUES (1234,'Company A'),(1234,'Company B');
--Apparent cartesian product made by duplicate rows
SELECT *
FROM A
JOIN B on A.ID=B.ID;
UPDATE A
SET ID = 1235
WHERE Name = 'Company B';
--Only one row from table A matches both rows on table B
SELECT *
FROM A
JOIN B on A.ID=B.ID;
UPDATE B
SET ID = 1235
WHERE Name = 'Company B';
--Expected Inner Join
SELECT *
FROM A
JOIN B on A.ID=B.ID;
--Real Cross Join
SELECT *
FROM A
CROSS
JOIN B ;
GO
DROP TABLE A;
DROP TABLE B;
March 19, 2015 at 9:39 am
Ok, Thanks for the explanation.
select * from A INNER JOIN B ON A.ID=B.ID
Please let me know how can we modify above query so that it will gives us only 1234 instead of following
ID ID
1234 1234
1234 1234
1234 1234
1234 1234
I only want following result set.
ID
1234
WHERE value of ID is same in both tables. Thanks for your help in advance
March 19, 2015 at 9:48 am
Luis Cazares (3/19/2015)
As previously stated, you're getting a join on matched rows only. The problem is that your first row on table A matches both rows on table B and the second row on table A matches both rows on table B.If you correct the data as shown on the following example, you'll see that this is happening correctly.
CREATE TABLE dbo.B(
ID int NULL,
Name varchar(50)
);
INSERT dbo.B (ID,Name)
VALUES (1234,'Company A'),(1234,'Company B');
CREATE TABLE dbo.A(
ID int NULL,
Name varchar(50)
);
INSERT dbo.A (ID,Name)
VALUES (1234,'Company A'),(1234,'Company B');
--Apparent cartesian product made by duplicate rows
SELECT *
FROM A
JOIN B on A.ID=B.ID;
UPDATE A
SET ID = 1235
WHERE Name = 'Company B';
--Only one row from table A matches both rows on table B
SELECT *
FROM A
JOIN B on A.ID=B.ID;
UPDATE B
SET ID = 1235
WHERE Name = 'Company B';
--Expected Inner Join
SELECT *
FROM A
JOIN B on A.ID=B.ID;
--Real Cross Join
SELECT *
FROM A
CROSS
JOIN B ;
GO
DROP TABLE A;
DROP TABLE B;
Thanks for the explanation Luis, but this doesn't solves my problem.
In above example you have used two columns (ID and Name)
ID Contains ID and Name contains Company name.
Now in my case I also have two columns (ID and Product)
ID contains CompanyID and Product contains company product.
Now as you have done data update like following
UPDATE A
SET ID = 1235
WHERE Name = 'Company B';
I cant do it 🙁
I have same products with same CompanyID's so I cant update CompanyID or product and I need to show them as a single row.
March 19, 2015 at 9:51 am
Zohaib Anwar (3/19/2015)
Ok, Thanks for the explanation.select * from A INNER JOIN B ON A.ID=B.ID
Please let me know how can we modify above query so that it will gives us only 1234
Remove your duplicates or use DISTINCT
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 19, 2015 at 9:52 am
Zohaib Anwar (3/19/2015)
I have same products with same CompanyID's so I cant update CompanyID or product and I need to show them as a single row.
You've got duplicate rows that you need to clean up. Or you're missing a join clause. Or you need to aggregate one of the tables before joining.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply