Results 1 to 3 of 3

Thread: SQL Statement

  1. #1
    Join Date
    Jan 2004
    Posts
    18

    Unanswered: SQL Statement

    I have two table,

    Groups[ID,GroupName,HeadID,DeputyHeadID]
    [1,aaa,1,2]
    [2,bbb,3,"Null"]
    [2,ccc,5,"Null"]

    Employees[ID,FirstName,LastName]
    [1,aaa,bbb]
    [2,ccc,ddd]
    [3,eee,fff]
    [4,ggg,hhh]
    [5,iii,jjj]
    [6,kkk,lll]

    I would like to take all records in "Groups" and link there corresponding [HeadID,DeputyHeadID] with "Employees.ID" so that when I output the [GroupName], the FirstName and LastName would be displayed.

    My current SQL statement, not doing what I want it to do:

    SELECT Employees.*, Groups.*
    FROM Employees, Groups
    WHERE Employees.ID = Groups.HeadID AND Employees.ID = Groups.DeputyHeadID

    Hope I'm clear on this.
    Last edited by Gazzou; 06-02-04 at 14:12.

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this?

    SELECT Employees.*, e1.FirstName,e1.LastName, e2.FirstName,e2.LastName
    FROM Groups g
    left join Employees e1 on e1.id=g.HeadID
    left join Employees e2 on e2.id=g.DeputyHeadID

  3. #3
    Join Date
    Jan 2004
    Posts
    18

    Question

    This almost works as I want it to, but for some reason, it only goes trough the loop one time. I figured it could be something with my SQL statement, but I don't know where it could be.

    sqlHead = "SELECT DLSUs.*, Employees.ID, Employees.FirstName FROM DLSUs, Employees WHERE DLSUs.DLSUType = 'LSU' AND Employees.ID = DLSUs.HeadID"

    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.Open sqlHead, mydb, adOpenKeySet

    do until objRS.EOF
    Response.Write "<tr>"
    Response.Write "<td width=""320"">"
    If NOT objRS("Link") = "" Then
    Response.Write "<a href=" & objRS("Link") & ">" & objRS("Title_EN") & "</a>"
    Else
    Response.Write objRS("Title_EN")
    End If
    Response.Write "</td>"
    Response.Write "<td width=""170"" height=""1""><img border=""0"" src=""images/envelope.gif"" alt=""Envelope"" width=""14"" height=""11"">"
    Response.Write objRS("FirstName")
    Response.Write "</td></tr>"
    objRS.MoveNext
    loop
    objRS.close()

    ------------------------------

    This will output in a table:

    [DLSUs.Title_EN], [Employees.FirstName Where Employees.ID = DLSUs.HeadID]

    Anyone can help?
    Last edited by Gazzou; 06-06-04 at 13:42.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •