Simon Gare wrote:
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.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"