SQL JOIN QUERY ISSUE

[Simon Gare]

Hi all,

having problem with query below

SELECT DISTINCT dbo.logon.DRIVER_NO, dbo.booking_form.DriverNo FROM
((dbo.logon RIGHT JOIN dbo.Employees ON dbo.logon.DRIVER_NO =
dbo.Employees.DriverNo) ) LEFT JOIN dbo.booking_form ON dbo.logon.DRIVER_NO
= dbo.booking_form.DriverNo

problem is it produces more than 1 result for the driver from the
booking_form table. If a Driver is logged in then I need to show information
from the booking_form table for that driver but it produces row after row of
data. Have tried all combinations of JOIN but to no avail.

How do I select the last entry in the dbo.booking_form table for the driver
logged in?.

Regards
Simon
--
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk

 

[Bob Barrows [MVP]]

Hi all,

having problem with query below

SELECT DISTINCT dbo.logon.DRIVER_NO, dbo.booking_form.DriverNo FROM
((dbo.logon RIGHT JOIN dbo.Employees ON dbo.logon.DRIVER_NO =
dbo.Employees.DriverNo) ) LEFT JOIN dbo.booking_form ON
dbo.logon.DRIVER_NO = dbo.booking_form.DriverNo

problem is it produces more than 1 result for the driver from the
booking_form table. If a Driver is logged in then I need to show
information from the booking_form table for that driver but it
produces row after row of data. Have tried all combinations of JOIN
but to no avail.

How do I select the last entry in the dbo.booking_form table for the
driver logged in?.

I'm suspect this is SQL Server, but I'm not sure given the unnecessary
parentheses in your FROM clause, parentheses that would be necessary if this
were a Jet database. However, given the "dbo." qualifiers, the pendulum
swings toward SQL Server. Please don't make us guess!! Alyways tell us your
database type and version.

Having said that, we need more information about your table structures,
partidularly about their primary and foreign keys. Given the joins you used,
I can make a guess that logon seems to be the "master" table in a 1-to-many
relationship with booking_form. But I'm mystified about the relationship
between Employees and logon: is that 1-to-1 or 1-to-many? I guess you are
using outer joins because you want the query to return all Employee records,
regardless of whether they appear in logon or booking_form. Too much
guesswork here. Please see this: www.aspfaq.com/5006.

Now, your select clause only mentions fields from logon and booking_form:
why include Employees in the query if you don't want any information from
it? The only time it is appropriate to include a table in a FROM clause
without including fields from that table in the select clause is if you are
using that table to limit, group or sort the results, none of which seems to
be the case here. Most of my questions will be answered if you provide the
DDL, a few rows of sample data (in tabular form - relevant fields only,
please), and the desired resultset using that sample data, again in tabular
form.