?
Solved

SQL Server Complex join

Posted on 2014-08-15
2
Medium Priority
?
299 Views
Last Modified: 2014-08-19
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.
0
Comment
Question by:SharonBernal
2 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1600 total points
ID: 40264473
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
 
LVL 14

Assisted Solution

by:Russell Fox
Russell Fox earned 400 total points
ID: 40265236
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question