join question

Last Post 09 Apr 2003 03:52 PM by BobBarker. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
js_SQL
New Member
New Member

--
12 Apr 2002 07:29 AM
say i have 2 tables

tablea
apid
aname

tableb
bpid
apid
bdate

and i have info like this
tablea
-------
1
helo

tableb
--------
1
1
2/2/2002 10:00:05

how do i write a query which would give me this
i need to know apid and bpid for bdate = 2/2/2002 10:00:10 if this date exists but it should give me apid where aname = 'helo'
js_SQL
New Member
New Member

--
12 Apr 2002 07:30 AM
this is the query i have
SELECT a.apid,b.bpidFROM
tablea a
LEFT JOIN
tableb b
on a.aPid=b.bPid
WHERE
a.anmae = 'helo'
AND
b.bdate = '2/2/2002 10:00:10'
FoFa
New Member
New Member

--
09 Apr 2003 04:43 AM
Join Question.
I have noticed that visual queries in Access, their SQL is different in the joins than queries I usually write in SqlServer, Besides the Access/SqlServer differances. As and example, I have 3 tables (just picked three I could join for this example, don't read to much into this). Access SQL with 2 outer joins appears as such:
SELECT EMP.Emp_FirstName,
EMP.Emp_LastName,
OLOOK.Title,
OLOOK.[E-mail address],
XCHG.[Display Name]
FROM dbo_EmployeeMaster AS EMP
LEFT JOIN ([Global Address List] AS OLOOK
LEFT JOIN Header_CSV_Exchange AS XCHG
ON OLOOK.[Display name] = XCHG.[Display Name])
ON EMP.Emp_DepartmentName = OLOOK.Department;

Yet if I was to write it for Sql I would write it as:
SELECT EMP.Emp_FirstName,
EMP.Emp_LastName,
OLOOK.Title,
OLOOK."E-mail address",
XCHG."Display Name"
FROM dbo_EmployeeMaster AS EMP
LEFT JOIN "Global Address List" AS OLOOK
ON OLOOK.Department = EMP.Emp_DepartmentName
LEFT JOIN Header_CSV_Exchange AS XCHG
ON XCHG."Display Name" = OLOOK."Display name"

So, I was wondering, am I doing it wrong, what's the differance? Why does Access seem to "NEST" the joins? When you are using a LEFT or RIGHT join does the order of the data items in the ON clause matter, and if so, what is correct. I have read BOL but maybe I don't understand totally.
Thanks
BobBarker
New Member
New Member

--
09 Apr 2003 03:52 PM
Unfortunately, Access requires the nesting in order to be able to parse the statement. The actual nesting does not do anything. The order of operations is not enforced by the parenthesis. Really, the only thing that the nesting gives you is code that is more difficult to read!! Thank goodness Access has a nice QBE grid.

SQL Server is a bit more forgiving and doesn't require you to add the parenthesis. In fact I don't think it will let you add the parenthesis, unless you are using a derived table.

Other than that, I'm not aware of any reason for the difference.

BB
You are not authorized to post a reply.

Acceptable Use Policy