SQL Server Complex join

I have a query as follows:

select * from TBL1 inner join TBL2 where tb1.groupkey=tbl2.groupkey

I want to keep these records, however here's the problem, if groupkey is null or ZZ I need to execute the same select with a different table and where condition

select * from TBL1 inner join TBL3 where tb1.groupkey=tbl3.groupkey

Finally if this brings back duplicates I need to set groupkey to null.

I need help designing this query.
SharonBernalAsked:
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.

PortletPaulfreelancerCommented:
Sharon you really should try to demonstrate with some sample data and an expected result. Attempting to describe this through words alone will be difficult and perhaps frustrating. In fact I think I can prove that by submitting what you see below:

My initial interpretation of your words lead to some sample data and
select distinct
        t1.id
      , t1.groupkey
      , tbl1
      , tbl2
      , tbl3
from table1 t1
left join Table2 t2 on t1.groupkey = t2.groupkey
                   and t1.groupkey <> 'ZZ'
left join Table3 t3 on t1.groupkey = t3.groupkey
                   and (t1.groupkey = 'ZZ' or t1.groupkey IS NULL)
;

Open in new window

which produced:
| ID | GROUPKEY | TBL1 |   TBL2 |   TBL3 |
|----|----------|------|--------|--------|
|  1 |       G1 |    1 |      2 | (null) |
|  2 |       G1 |    1 |      2 | (null) |
|  3 |       G1 |    1 |      2 | (null) |
|  4 |       G1 |    1 |      2 | (null) |
|  5 |       G1 |    1 |      2 | (null) |
|  6 |   (null) |    1 | (null) | (null) |
|  7 |       ZZ |    1 | (null) |      3 |
|  8 |       ZZ |    1 | (null) |      3 |
|  9 |       ZZ |    1 | (null) |      3 |
| 10 |   (null) |    1 | (null) | (null) |

Open in new window

Now the real reason for generating this is to highlight that if groupkey IS NULL then you cannot join to Table3 through that field. Notice how the column [Tbl3] is null when groupkey is null.

If we use a UNION query, then we still have issues with those NULLs:
select
        t1.id
      , t1.groupkey
      , tbl1
      , tbl2 as tbl_joined
from table1 t1
INNER join Table2 t2 on t1.groupkey = t2.groupkey
                   and t1.groupkey <> 'ZZ'
where 10=10

UNION

select
        t1.id
      , t1.groupkey
      , tbl1
      , tbl3 as tbl_joined
from table1 t1
INNER join Table3 t3 on t1.groupkey = t3.groupkey
                   and (t1.groupkey = 'ZZ' or t1.groupkey IS NULL)
where 20=20
;

Open in new window

and the result (notice the NULL groupkeys have disappeared
| ID | GROUPKEY | TBL1 | TBL_JOINED |
|----|----------|------|------------|
|  1 |       G1 |    1 |          2 |
|  2 |       G1 |    1 |          2 |
|  3 |       G1 |    1 |          2 |
|  4 |       G1 |    1 |          2 |
|  5 |       G1 |    1 |          2 |
|  7 |       ZZ |    1 |          3 |
|  8 |       ZZ |    1 |          3 |
|  9 |       ZZ |    1 |          3 |

Open in new window

The sample data I used
CREATE TABLE Table1
	([ID] int, [groupkey] varchar(4), [Tbl1] int)
;
	
INSERT INTO Table1
	([ID], [groupkey], [Tbl1])
VALUES
	(1, 'G1', 1),
	(2, 'G1', 1),
	(3, 'G1', 1),
	(4, 'G1', 1),
	(5, 'G1', 1),
	(6, NULL, 1),
	(7, 'ZZ', 1),
	(8, 'ZZ', 1),
	(9, 'ZZ', 1),
	(10, NULL, 1)
;

CREATE TABLE Table2
	([ID] int, [groupkey] varchar(4), [Tbl2] int)
;
	
INSERT INTO Table2
	([ID], [groupkey], [Tbl2])
VALUES
	(1, 'G1', 2),
	(2, 'G1', 2),
	(3, 'G1', 2),
	(4, 'G1', 2),
	(5, 'G1', 2),
	(5, 'G1', 2),
	(6, NULL, 2)
;

CREATE TABLE Table3
	([ID] int, [groupkey] varchar(4), [Tbl3] int)
;
	
INSERT INTO Table3
	([ID], [groupkey], [Tbl3])
VALUES
	(7, 'ZZ', 3),
	(7, 'ZZ', 3),
	(8, 'ZZ', 3),
	(9, 'ZZ', 3),
	(10, NULL, 3)
;

Open in new window

See all this working at: http://sqlfiddle.com/#!3/d4366/6

Bottom line: please supply sample data and expected result.
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
Russell FoxDatabase DeveloperCommented:
You should be able to use a UNION ALL and separate out the two different queries. For this to work the number of columns in the 2nd query must match the number of columns in the 1st query:
select * from TBL1 inner join TBL2 where tb1.groupkey = tbl2.groupkey AND nullif(tb1.GroupKey, 'ZZ') IS NOT NULL
UNION ALL
select * from TBL1 inner join TBL3 where tb1.groupkey = tbl3.groupkey AND tb1.GroupKey IS NOT NULL

Open in new window

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
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.