Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts

    MORE access table joins: table aliases...

    It's me again

    Right, let's say I have the following query:

    SELECT
    [tblRooms].[roomDescription],
    [tblRooms].[roomName] as inRoom,
    [tblRoomDirections].[directionID],
    [tblRoomDirections].[targetRoomID],
    [tblObjects].[objectName]
    FROM (([tblRooms] INNER JOIN [tblRoomDirections] ON [tblRoomDirections].[roomID] = [tblRooms].[id])
    INNER JOIN [tblObjectRooms] ON [tblObjectRooms].[roomID] = [tblRooms].[id])
    INNER JOIN [tblObjects] ON [tblObjects].[id] = [tblObjectRooms].[objectID]
    WHERE [tblRooms].[id]=4

    This works pretty well: it gives me a recordset showing all the objects and all the possible exit routes ("targetRoomID") for a particular room. However, it gives me the ID numbers of the target rooms rather than their names.

    I need to link, therefore, back to tblRooms: but tblRooms is already in there. This is, I guess, where table and column aliases come in but for the life of me I can't figure out the syntax.

    I've been messing about with the following but get various errors from Access along the lines of "If you don't know what's wrong with it then I'm certainly not going to tell you".

    SELECT
    [tblRooms].[roomDescription],
    [tblRooms].[roomName] AS inRoom,
    [tblRoomDirections].[directionID],
    [tblRoomDirections].[targetRoomID],
    [tblObjects].[objectName],
    [T1].[roomName] AS toRoom
    FROM ((([tblRooms] INNER JOIN [tblRoomDirections] ON [tblRoomDirections].[roomID] = [tblRooms].[id])
    INNER JOIN [tblObjectRooms] ON [tblObjectRooms].[roomID] = [tblRooms].[id])
    INNER JOIN [tblObjects] ON [tblObjects].[id] = [tblObjectRooms].[objectID])
    INNER JOIN [tblRooms T1] ON [tblRoomDirections].[roomID] = [T1].[id]
    WHERE [tblRooms].[id]=4


    Can anyone show me the error of my ways?

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    Code:
    INNER JOIN [tblRooms T1] ON [tblRoomDirections].[roomID] = [T1].[id]
    should be
    Code:
    INNER JOIN [tblRooms] T1 ON [tblRoomDirections].[roomID] = [T1].[id]
    ( i think )
    I am the luckiest man in the world

  • #3
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Thanks Roelf, yes that solved my syntax woes.

    However; it's not pulling out the results I expected.

    I have the following:

    Code:
    SELECT
    [T1].[roomDescription] AS inRoomDesc,
    [T1].[id] AS inRoomID,
    [T1].[roomName] AS inRoom,
    [tblRoomDirections].[directionID],
    [tblRoomDirections].[targetRoomID] AS toRoomID,
    [T2].[roomName] AS toRoom,
    [tblObjects].[objectName]
    FROM ((([tblRooms] T1 INNER JOIN [tblRoomDirections] ON [tblRoomDirections].[roomID] = [T1].[id])
    INNER JOIN [tblObjectRooms] ON [tblObjectRooms].[roomID] = [T1].[id])
    INNER JOIN [tblObjects] ON [tblObjects].[id] = [tblObjectRooms].[objectID])
    INNER JOIN [tblRooms] T2 ON [tblRoomDirections].[roomID] = [T2].[id]
    WHERE [T1].[id]=5
    As you can see, I've replaced both calls to join tblRooms, with aliases.

    The problem is with the "toRoom" column. The "toRoomID" column is correctly pulling out a list of room ID's that the user can get to from the current room, but it's not matching the ID's to those rooms' respective names: the "toRoom" column always shows the name of the current room. I've attached a 4k gif example result set.

    Once more, what am I doing wrong?



    (actually, looking at it again, I notice that I've got tblRoomDirections in two INNER JOIN calls - should I be using aliases for this, too? If so could you give me an example as I can't really get my head round which columns to pull from which alias, or even if it matters )
    Attached Thumbnails Attached Thumbnails MORE access table joins: table aliases...-queryresults.gif  
    Last edited by Spudhead; 05-25-2004 at 11:24 AM.

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    shouldn't the
    Code:
    INNER JOIN [tblRooms] T2 ON [tblRoomDirections].[roomID] = [T2].[id]
    be
    Code:
    INNER JOIN [tblRooms] T2 ON [tblRoomDirections].[targetRoomID] = [T2].[id]
    I am the luckiest man in the world

  • #5
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Ah.

    Umm.

    Ahem.


    <looks around sheepishly and runs away>

    Thanks Roelf

  • #6
    New to the CF scene
    Join Date
    Apr 2013
    Posts
    6
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Can someone help me write this query?

    I have a table that looks like this:


    ID EEID DR CR
    1 333 30
    2 333 50
    3 333 30
    4 333 30
    5 444 60
    6 444 60
    7 444 70

    ID is my primary key column and it autonumbers the records. EEID is a person, and there are many records per one person. DR is a debit entry for that person, CR is a credit entry for that person.
    Is it possible to run one or both of the following queries?
    1) A query that returns to me matching records(matching means where the debit equals the credit for a person). If the query ran on the above table, it should return to me records (beginning with ID number): 1, 3, 5, and 6. May get tricky because there can be multiple same-dollar value transactions per person-for example, person 333 has a couple $30 debits. The query should "know" that record 1 matches to 3 and it shouldn't match 4 to 3. "First come first match".....
    2)A query that returns to me the opposite of what I defined in 1). A query that returns to me UNMATCHED records. If run on the above table, the query should return to me records 2, 4, and 7.

    THANKS IN ADVANCE!!!!!!! I have tried inner joins, all kinds of things, and I am going nuts! My syntax is never right

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    This is a crosspost from the "Other Databases" forum.

    I thought you said, in the other forum, that you wouldn't know how to use ASP for this?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    New to the CF scene
    Join Date
    Apr 2013
    Posts
    6
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,
    This was my first post in here because I couldn't figure out how to start a new thread.
    I don't know what ASP is....what does it stand for?

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,588
    Thanks
    80
    Thanked 4,497 Times in 4,461 Posts
    Active Server Pages. But don't worry about it. Let's just close this thread.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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