CODEWITHSUNDEEP
sqlselectdouble
user3328880
user3328880

Reputation: 23

Return results of SQL query on one row

SELECT
    Tickets.id,
    (SELECT Users.Name FROM Users INNER JOIN Tickets ON Tickets.Creator = Users.id) AS naam,
    (SELECT Users.Name FROM Users INNER JOIN Tickets ON Tickets.Owner = Users.id) AS name
FROM Tickets
WHERE Tickets.id = 64;

I want on one row

id, username(Tickets.creator= Users.id), username(where Tickets.Owner = Users.id)

Users

id | name
----------
1  | jan
2  | henk
3  | maria

tickets

ticketsid | owner     | creator
-------------------------------
1         | 3         | 2
2         | 1         | 3
3         | 2         | 3

Upvotes: 1

Views: 79

Answers (2)

beny23
beny23

Reputation: 35018

How about

select t.id, o.name, c.name
  from tickets t
  join users o on o.id = t.owner
  join users c on c.id = t.creator
 where t.id = 64;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

Your queries want to be correlated with the outer query. You as missing that by doing an additional join to tickets:

SELECT
    Tickets.id,
    (SELECT Users.Name FROM Users WHERE Tickets.Creator = Users.id) AS naam,
    (SELECT Users.Name FROM Users WHERE Tickets.Owner = Users.id) AS name
FROM Tickets
WHERE Tickets.id = 64;

You can also express this query with joins:

select t.id, uc.name as CreatorName, uo.name as OwnerName
from Tickets t left outer join
     Users uc 
     on t.Creator = uc.id left outer join
     Users uo
     on t.Owner = uo.id
where t.id = 64;

This would be the more typical way of expressing this query.

Upvotes: 1

Related Questions