SQL Query - Full Join

I have a SQL query that joins another select statement.  If the main query is null, I still want to pull from the nested query.  Is this even possible or would I need to create 2 temp table and do that?

Example:

SELECT E.ID, E.NAME, E.DEPT_ID, A.DEPT_DESC, E.PAYRATE FROM EMPLOYEE E
FULL OUTER JOIN
(SELECT D.DEPT_ID, D.DEPT_DESC FROM DEPT D) A ON A.DEPT_ID = E.DEPT_ID

Output:

2912      Joe Smuack      122     MIS     $22.00
NULL     NULL                NULL   FIN     NULL
holemaniaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ArgentiCommented:
I suppose you have a Department table and an Employee table. And you want ALL the Departments, even those who don't have employees assigned

so

SELECT 
   D.DEPT_DESC,
   E.*
FROM DEPT D
LEFT JOIN EMPLOYEE E on (E.DEPT_ID = D.DEPT_ID)

Open in new window


or, if you want also all employees without department
SELECT 
   D.DEPT_DESC,
   E.*
FROM DEPT D
FULL OUTER JOIN EMPLOYEE E on (E.DEPT_ID = D.DEPT_ID)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
The "nested query" (which is aka as ad-hoc view or derived table) isn't necessary here, and it is much better to flatten the join as shown by Argenti. And indeed, if you need rows even if the join cannot find a match, you always use an outer join (left, right or full, depending on the situation).
0
awking00Information Technology SpecialistCommented:
Can you provide some sample data for the two tables and what your expected results should be?
0
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

sameer2010Commented:
Your query is right unless you do not want NULL employees/dept.
0
hnasrCommented:
This is a way in Access, using 2 tables a__1(aid, ...), a__2 (aid, ...)

a__1:
aID
1
2
3

a__2:
aID
2
3
4

SELECT a__1.aID, a__2.aID
FROM a__1 LEFT JOIN a__2 ON a__1.aID = a__2.aID;
union
SELECT a__1.aID, a__2.aID
FROM a__1 RIGHT JOIN a__2 ON a__1.aID = a__2.aID

Result:
a__1.aID	a__2.aID
	4
1	
2	2
3	3

Open in new window

0
holemaniaAuthor Commented:
Sorry was just making the query as simple as can be, but it gets pretty complex so that's why I did a nested query since the 2nd query consists of joining a few other tables.

Was able to achieve the request by putting both queries into 2 temp tables and then doing a full outer join with the tables.

Reason why I want to pull items that doesn't match the other temp table (nested query) is so that we can see departments that haven't been used.  Instead of generating 2 separate report, users was asking if we can incorporate both into 1 report.

Thanks for all the suggestions.
0
hnasrCommented:
Welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.