Joins in sql??

dkim18
dkim18 used Ask the Experts™
on
Hi,

can you help me with joins please?
I have the following tables
projects(pid,pname,assigner,assignee,createdby,modifiedby,typeid)
Users(userid,lastname,firstname)
Types(typeid,typename)
assigner,assignee,createdby,modifiedby use userid of users table.

In my select sql, I want to display user's fullname and typename from
Select  pid,pname,assigner,assignee,createdby,modifiedby,typeid from Projects
in the assigner,assignee,createdby,modifiedby,typeid  fields...




Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Hope this helps:

Replace Join with LEFT OUTER JOIN if either assigner, assignee or typeid columns allows NULL Values.
Select Projects.pid,Projects.pname,Projects.assigner,Projects.assignee,Projects.createdby,Projects.modifiedby,Projects.typeid,
t1.lastname assigner_last_name, t1.first_name assigner_first_name, t2.lastname assignee_last_name, t2.first_name assignee_first_name,
types.typename
from Projects join Users t1 on Projects.assigner = t1.userid
join Users t2 on Projects.assignee = t2.userid
join Types on Projects.typeid = Types.typeid

Open in new window

Commented:
select
pid
, pname
, u.firstname
, u.lastname
, u.firstname + ' ' + u.lastname as fullname
, t.typename
from users u
join projects p on u.userid=p.assigner or u.userid=p.assignee or u.userid=p.createdby or u.userid=modifiedby
join types t on p.typeid=t.typeid
If assigner,assignee,createdby,modifiedby use userid of users table as lastname first name, then you can also write:
SELECT pid, pname, u.firstname, u.lastname, u.firstname + ' ' + u.lastname as fullname, t.typename
FROM users u,projects p,types t
WHERE u.userid = CASE WHEN (u.firstname + ' ' + u.lastname) =  p.assigner THEN p.assigner
                                        WHEN (u.firstname + ' ' + u.lastname) =  p.assignee THEN p.assignee
                                        WHEN (u.firstname + ' ' + u.lastname) =  p.createdby THEN p.createdby
                                        WHEN (u.firstname + ' ' + u.lastname) =  p.modifiedby THEN p.modifiedby
                             END
AND p.typeid = t.typeid;

You can also write:
(select pid, pname, u.firstname, u.lastname, u.firstname + ' ' + u.lastname as fullname, t.typename
from users u join projects p on u.userid=p.assigner join types t on p.typeid=t.typeid)
UNION
(select pid, pname, u.firstname, u.lastname, u.firstname + ' ' + u.lastname as fullname, t.typename
from users u join projects p on u.userid=p.assignee join types t on p.typeid=t.typeid)
UNION
(select pid, pname, u.firstname, u.lastname, u.firstname + ' ' + u.lastname as fullname, t.typename
from users u join projects p on u.userid=p.createdby join types t on p.typeid=t.typeid)
UNION
(select pid, pname, u.firstname, u.lastname, u.firstname + ' ' + u.lastname as fullname, t.typename
from users u join projects p on u.userid=p.modifiedby join types t on p.typeid=t.typeid);

Once you get all the functionally correct ones, take EXPLAIN PLAN, weigh the costs, then execute the queries, weigh the timings and then choose the best one.


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial