January 25, 2013 at 9:35 pm
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 ?
January 26, 2013 at 12:21 am
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
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 26, 2013 at 4:05 am
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.
January 28, 2013 at 3:04 am
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
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