Wrox Programmer Forums
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 21st, 2003, 10:28 PM
kaz kaz is offline
Authorized User
 
Join Date: Dec 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default JOIN problem

How can I re-state this SQL statement without JOIN?

SELECT Document.DocumentID, Document.DocumentName,
FROM UserProject, (Document INNER JOIN ProjectDocument ON Document.DocumentID = ProjectDocument.DocumentID) INNER JOIN Project ON ProjectDocument.ProjectID = Project.ProjectID
WHERE (((UserProject.UserID)=1));

Thank you,

 
Old December 22nd, 2003, 12:07 AM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

I'm not sure about the () around the tables and joins in the from clause, but this is basically how you do it. You just need to put all your tables in the FROM with commas separating them, then put the join criteria in the WHERE clause.

SELECT doc.DocumentID, doc.DocumentName,
FROM UserProject AS up, Document, ProjectDocument AS pd, Project AS p
WHERE doc.DocumentID = pd.DocumentID
AND pd.ProjectID = p.ProjectID
AND up.UserID=1;

or something like that. Again, the () from (Document to .DocumentID) don't quite make sense but you should be able to get the general idea.

Peter
------------------------------------------------------
Work smarter, not harder.
 
Old December 22nd, 2003, 05:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

There is an error in the code that Peter posted, it is missing the AS doc so the statement won't run. If you do not alias a table then you must reference the column by using the full table name. The SQL statement should read:
Code:
SELECT doc.DocumentID, doc.DocumentName, 
  FROM UserProject AS up, Document AS doc, ProjectDocument AS pd, Project AS p
 WHERE doc.DocumentID = pd.DocumentID
   AND pd.ProjectID = p.ProjectID
   AND up.UserID = 1;
   Just a typo I am sure but I though it best to correct for clarity.

Regards
Owain Williams
 
Old December 22nd, 2003, 08:37 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Of course, placing the JOIN criteria in the WHERE clause simply removes the keyword 'JOIN' from the query; it's still doing the relational INNER JOIN operation (using "old-style" syntax).

To truly formulate the query without a JOIN at all would require correlated subqueries. But why would anyone want to do that, unless it were a homework assignment? ;)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old December 22nd, 2003, 04:13 PM
kaz kaz is offline
Authorized User
 
Join Date: Dec 2003
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you, all. It works now.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with Join/Where jpryor SQL Server 2005 2 April 12th, 2008 01:25 AM
inner join problem john-boy SQL Language 1 March 29th, 2006 06:30 AM
Inner Join Problem rylemer SQL Language 2 October 21st, 2005 04:41 PM
Oracle 8i inner join and left join problem puteri_84 Oracle 2 August 19th, 2004 07:14 AM
SQL Inner Join Problem tp194 Classic ASP Databases 2 July 8th, 2003 09:59 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.