Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Join Predicate Expand / Collapse
Author
Message
Posted Wednesday, December 12, 2007 8:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:52 AM
Points: 2,551, Visits: 2,594
Comments posted to this topic are about the item Join Predicate

--Ramesh

Post #432627
Posted Thursday, December 13, 2007 2:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 4:39 AM
Points: 1,865, Visits: 593
nice question. nice and sneaky.

Life: it twists and turns like a twisty turny thing
Post #432714
Posted Thursday, December 13, 2007 2:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:31 AM
Points: 112, Visits: 210
I tried this query in SQL SERVER 2005,It's giving some error.It's a SQL SERVER 2008 feature?

Thanks & Regards
Sagesh.K
Post #432722
Posted Thursday, December 13, 2007 3:00 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, March 24, 2009 4:38 AM
Points: 780, 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!!!
Post #432725
Posted Thursday, December 13, 2007 3:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:17 AM
Points: 1,274, Visits: 1,985
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
Post #432727
Posted Thursday, December 13, 2007 3:43 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:50 AM
Points: 561, Visits: 1,209
What benefit...? Makes you look up middle join in BOL:)
Post #432735
Posted Thursday, December 13, 2007 3:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:31 AM
Points: 112, Visits: 210
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 :)
Post #432742
Posted Thursday, December 13, 2007 4:32 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 4, 2009 10:02 AM
Points: 810, 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...
Post #432752
Posted Thursday, December 13, 2007 4:49 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:37 AM
Points: 5,879, Visits: 13,009
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


---------------------------------------------------------------------

Post #432766
Posted Thursday, December 13, 2007 8:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 5:17 AM
Points: 1,274, Visits: 1,985
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 :)

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
Post #432860
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse