SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Join Predicate


Join Predicate

Author
Message
Ramesh Saive
Ramesh Saive
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3480 Visits: 2643
Comments posted to this topic are about the item Join Predicate

--Ramesh


hodgy
hodgy
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2051 Visits: 596
nice question. nice and sneaky.

Life: it twists and turns like a twisty turny thing

Sagesh
Sagesh
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 220
I tried this query in SQL SERVER 2005,It's giving some error.It's a SQL SERVER 2008 feature?

Thanks & Regards
Sagesh.K
Pete Brown
Pete Brown
Right there with Babe
Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)Right there with Babe (786 reputation)

Group: General Forum Members
Points: 786 Visits: 162
I wonder what benefit questions like these have to the community at large.
I thought the question of the day was there to inform not just to make the author feel smug!!!
StarNamer
StarNamer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1646 Visits: 1992
Sneaky.

Reminds me of the advice when reading program code (any language) ...
"Don't trust the comments (or formatting?). Read the code!"

Sagesh (12/13/2007)
I tried this query in SQL SERVER 2005,It's giving some error.It's a SQL SERVER 2008 feature?

Thanks & Regards
Sagesh.K

Worked fine for me in 2K5.

use scratch
--drop table customers
--drop table employees
create table customers (customerid int, name varchar(10))
create table employees (employeeid int, name varchar(10))
insert customers (customerid, name)
select 1,'Dave' union all
select 2,'John' union all
select 3,'Tom' union all
select 4,'Dick' union all
select 5,'Harry'
insert employees (employeeid, name)
select 2,'John' union all
select 4,'Dick' union all
select 6,'Anne' union all
select 8,'Martha'
GO
SELECT *
FROM dbo.Customers
MIDDLE JOIN dbo.Employees ON CustomerID = EmployeeID



Derek
P Jones
P Jones
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1239 Visits: 1517
What benefit...? Makes you look up middle join in BOLSmile
Sagesh
Sagesh
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 220
Thanks for the valuable information.
I checked the Query like this :
"SELECT * FROM dbo.Customers AS C
MIDDLE JOIN dbo.Employees E ON C.CustomerID = E.EmployeeID",so it's showing some error.If i try to join same field name in different tables,that time also i am getting some error.
Like : "SELECT * FROM dbo.Customers AS C
MIDDLE JOIN dbo.Employees E ON C.CustomerID = E.CustomerID "

Please provide me some usefull links to know more about MIDDLE JOIN.

Thanks & Regards
Sagesh.k Smile
Adrian Nichols-360275
Adrian Nichols-360275
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1058 Visits: 915
Sagesh,

There is no MIDDLE JOIN. As MIDDLE is not a keyword or predicate and there is no alias already specified for tblCustomers, MIDDLE is taken as the alias.

Your queries do not work as you have specified an alias for the table (AS c).



Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10342 Visits: 13687
26% got this correct. How many of them got this right by luck, not having realised no such thing as middle join?

I am very upset to have missed out on a chance to get a point for getting something wrong Crying

---------------------------------------------------------------------
StarNamer
StarNamer
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1646 Visits: 1992
Sagesh (12/13/2007)
Thanks for the valuable information.
I checked the Query like this :
"SELECT * FROM dbo.Customers AS C
MIDDLE JOIN dbo.Employees E ON C.CustomerID = E.EmployeeID",so it's showing some error.If i try to join same field name in different tables,that time also i am getting some error.
Like : "SELECT * FROM dbo.Customers AS C
MIDDLE JOIN dbo.Employees E ON C.CustomerID = E.CustomerID "

Please provide me some usefull links to know more about MIDDLE JOIN.

Thanks & Regards
Sagesh.k Smile

That's because you've rewritten the query and broken it!

The point is that the AS keyword is optional, so SQL server reads

SELECT * FROM dbo.Customers
MIDDLE JOIN dbo.Employees ON CustomerID=EmployeeID

as

SELECT * FROM dbo.Customers AS MIDDLE
JOIN dbo.Employees ON CustomerID=EmployeeID


When you rewrote it, you inserted "AS C" after the name of table Customers, so now the word "MIDDLE", which isn't a keyword, is unexpected and you get an error!

Because the words "MIDDLE JOIN" have been formatted in upper case and put at the start of a new line, people get fooled into thinking it's a new type of join, when it's just aliasing the Customers table with the name 'MIDDLE'. There is no such thing as a middle join!

Derek
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search