SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Correct way to select from two tables?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

token
Posting Yak Master

United Kingdom
133 Posts

Posted - 07/10/2013 :  18:50:38  Show Profile  Reply with Quote
Back in the old days, I used to write select statements like this:


SELECT 
table1.columnA, table2,columnA

FROM
table1, table2

WHERE
table1.columnA = 'Some value'


However I was told that having comma separated table names in the "FROM" clause is not ANSI92 compatible. There should always be a JOIN statement.

This leads to my problem.... I want to do a comparison of data between two tables but there is no common field in both tables with which to create a join. If I use the 'legacy' method of comma separated table names in the FROM clause, then it works perfectly. But I feel uncomfortable using this method if it is considered wrong or bad practice.

Anyone know what to do in this situation?

Edited by - token on 07/10/2013 18:55:08

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 07/10/2013 :  19:16:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
How can you do a comparison if there's nothing in common? Using the comma separated way or the JOIN way is basically the same thing, just a different style of coding. If you didn't have anything in common with the old way, then it was doing a cartesian join which is not good.

Can you show us a data example of how a data comparison would work if nothing is in common?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 07/11/2013 :  00:17:10  Show Profile  Reply with Quote
quote:
Originally posted by token

Back in the old days, I used to write select statements like this:


SELECT 
table1.columnA, table2,columnA

FROM
table1, table2

WHERE
table1.columnA = 'Some value'


However I was told that having comma separated table names in the "FROM" clause is not ANSI92 compatible. There should always be a JOIN statement.

SELECT t1.columnA, t2,columnA
FROM table1 t1
JOIN table2 t2 ON 1=1
WHERE t1.columnA = 'Some value'


--
Chandu

Edited by - bandi on 07/11/2013 00:18:08
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/11/2013 :  00:59:12  Show Profile  Reply with Quote
quote:
Originally posted by bandi

quote:
Originally posted by token

Back in the old days, I used to write select statements like this:


SELECT 
table1.columnA, table2,columnA

FROM
table1, table2

WHERE
table1.columnA = 'Some value'


However I was told that having comma separated table names in the "FROM" clause is not ANSI92 compatible. There should always be a JOIN statement.

SELECT t1.columnA, t2,columnA
FROM table1 t1
JOIN table2 t2 ON 1=1
WHERE t1.columnA = 'Some value'


--
Chandu


this is again a CROSS JOIN as Tara suggested

equivalent to

SELECT t1.columnA, t2,columnA
FROM table1 t1
CROSS JOIN table2 t2
WHERE t1.columnA = 'Some value'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/11/2013 :  12:25:45  Show Profile  Reply with Quote
quote:
Originally posted by bandi

quote:
Originally posted by token

Back in the old days, I used to write select statements like this:


SELECT 
table1.columnA, table2,columnA

FROM
table1, table2

WHERE
table1.columnA = 'Some value'


However I was told that having comma separated table names in the "FROM" clause is not ANSI92 compatible. There should always be a JOIN statement.

SELECT t1.columnA, t2,columnA
FROM table1 t1
JOIN table2 t2 ON 1=1
WHERE t1.columnA = 'Some value'


--
Chandu

He did ask for he correct way. ;) (just busting your chops)
Go to Top of Page

token
Posting Yak Master

United Kingdom
133 Posts

Posted - 07/12/2013 :  05:33:18  Show Profile  Reply with Quote
I used the CROSS JOIN as recommended and it works perfectly. Thank you!

I guess I need to start reading new SQL books again :(

FYI: One table had a list of particular places with geolocations. Another table had a different list of places with geolocations. I was comparing the distances between the various locations.

I can't join on a geolocation field (e.g. geography) hence no common field to join on.

Edited by - token on 07/12/2013 05:36:34
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/12/2013 :  06:06:02  Show Profile  Reply with Quote
you're welcome
yep...your scenario seems like you need to use CROSS JOIN itself.

read below to get idea on joins
http://w3schools.com/sql/sql_join.asp

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000