Help with a query

  • Hi,

    I have a query and I am not sure why it doesnt return any lines. I am building some kind of a bug system and I want to show all the kinds of fields. I have 2 main tables: users, tickets. Now, I made reationship between them and for each reported by I want to assign an name of user by his id in the user table. The same goes for assign to. When I ran this query:

    "SELECT UsersTbl.Name AS first, UsersTbl.Name AS second

    FROM Tickets INNER JOIN

    UsersTbl ON Tickets.Reported_by = UsersTbl.Id INNER JOIN

    UsersTbl AS UsersTbl_1 ON Tickets.Assign_to = UsersTbl.Id"

    I dont get any thing in return. Why ?

  • Hello and welcome to ssc.

    Your second INNER JOIN condition references the table alias of the first. This should work, assuming suitable values in your data:

    SELECT

    u1.Name AS first,

    u2.Name AS second

    FROM Tickets t

    INNER JOIN UsersTbl u1

    ON t.Reported_by = u1.Id

    INNER JOIN UsersTbl u2

    ON t.Assign_to = u2.Id

    If it doesn't return any rows, try this to see what's happening:

    SELECT t.*, u1.*, u2.*

    FROM Tickets t

    LEFT JOIN UsersTbl u1

    ON t.Reported_by = u1.Id

    LEFT JOIN UsersTbl u2

    ON t.Assign_to = u2.Id


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • It works !!! thanks....

    I wrote as usertbl1 - so it will not refer to the first field.

    why it is not the same as what you did ?

    Any way, you really helped me.

  • You did not get any results because you probably did not have records in table "Tickets" where "Reported_by = Assign_to"

    SELECTUsersTbl.Name AS first, UsersTbl.Name AS second

    FROMTickets

    INNER JOINUsersTbl ON Tickets.Reported_by = UsersTbl.Id -- Fine, upto this point

    INNER JOINUsersTbl AS UsersTbl_1 ON Tickets.Assign_to = UsersTbl.Id -- Here, you are accessing the table with alias name "UsersTbl_1"

    /*Hence, the above query is equivalent to the below query*/

    SELECTUsersTbl.Name AS first, UsersTbl.Name AS second

    FROMTickets

    INNER JOINUsersTbl ON Tickets.Reported_by = UsersTbl.Id AND Tickets.Assign_to = UsersTbl.Id -- You can move the condition up like this

    INNER JOINUsersTbl AS UsersTbl_1 ON 1 = 1

    /*The above query is again equivalent to below query*/

    SELECTUsersTbl.Name AS first, UsersTbl.Name AS second

    FROMTickets

    INNER JOINUsersTbl ON Tickets.Reported_by = UsersTbl.Id AND Tickets.Assign_to = UsersTbl.Id

    CROSS JOINUsersTbl AS UsersTbl_1


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply