Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help with a query Expand / Collapse
Author
Message
Posted Friday, January 25, 2013 9:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 26, 2013 4:03 AM
Points: 2, Visits: 2
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 ?
Post #1411977
Posted Saturday, January 26, 2013 12:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 6:18 AM
Points: 1,037, Visits: 7,022
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





Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1411986
Posted Saturday, January 26, 2013 4:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 26, 2013 4:03 AM
Points: 2, Visits: 2
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.
Post #1412001
Posted Monday, January 28, 2013 3:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
You did not get any results because you probably did not have records in table "Tickets" where "Reported_by = Assign_to"

SELECT	UsersTbl.Name AS first, UsersTbl.Name AS second
FROM Tickets
INNER JOIN UsersTbl ON Tickets.Reported_by = UsersTbl.Id -- Fine, upto this point
INNER JOIN UsersTbl 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*/
SELECT UsersTbl.Name AS first, UsersTbl.Name AS second
FROM Tickets
INNER JOIN UsersTbl ON Tickets.Reported_by = UsersTbl.Id AND Tickets.Assign_to = UsersTbl.Id -- You can move the condition up like this
INNER JOIN UsersTbl AS UsersTbl_1 ON 1 = 1

/*The above query is again equivalent to below query*/
SELECT UsersTbl.Name AS first, UsersTbl.Name AS second
FROM Tickets
INNER JOIN UsersTbl ON Tickets.Reported_by = UsersTbl.Id AND Tickets.Assign_to = UsersTbl.Id
CROSS JOIN UsersTbl 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/
Post #1412288
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse