SQL JOIN QUERY ISSUE

Discussion in 'ASP General' started by Simon Gare, Apr 12, 2007.

  1. Simon Gare

    Simon Gare Guest

    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
     
    Simon Gare, Apr 12, 2007
    #1
    1. Advertising

  2. 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"
     
    Bob Barrows [MVP], Apr 12, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Anonymous
    Replies:
    0
    Views:
    1,476
    Anonymous
    Oct 13, 2005
  2. Alan Silver
    Replies:
    0
    Views:
    889
    Alan Silver
    Jun 5, 2006
  3. googleboy
    Replies:
    1
    Views:
    929
    Benji York
    Oct 1, 2005
  4. werD
    Replies:
    3
    Views:
    5,345
    Joseph Kesselman
    Aug 24, 2006
  5. Jon F.

    CAML Query: Multiple Query Fields Issue

    Jon F., May 12, 2004, in forum: ASP .Net Web Services
    Replies:
    0
    Views:
    758
    Jon F.
    May 12, 2004
Loading...

Share This Page