Join with result column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Join with result column

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-02-04 : 19:39:22

Is there a way to join 2 columns, and have a results column?

I ask this because, i have 2 tables i want to join, and i in some cases ALL that data in each table will be the same, sometimes the data will be in the right table, and not in the left, vice versa. But sometimes the data will be exactly the same in both tables, except 1 column of data.

So i wanted to know if there is a way, when i join tables together to say.

0 = both right and left table data for record are exactly the same
1 = In right table, in left table but inner values didnt match somewhere
2 = In right table but not left table
3 = in left table but not right table

thanks in advance guy!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-04 : 20:54:45

check out FULL OUTER JOIN

KH
[spoiler]Time is always against us[/spoiler]

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-02-04 : 22:21:58

does it give you a column like i was talking about?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-04 : 22:41:35

of-course not. You will need to use case statement to test the condition and return the value 0 to 3 accordingly

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-04 : 22:47:59

[code]
-- Create some sample table
DECLARE @LEFT TABLE
(
pk int,
value int
)

DECLARE @RIGHT TABLE
(
pk int,
value int
)

-- Fill the sample table with some records
INSERT INTO @LEFT (pk, value)
SELECT 0, 0 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 3, 30 UNION ALL
SELECT 5, 10

INSERT INTO @RIGHT (pk, value)
SELECT 0, 0 UNION ALL
SELECT 1, 12 UNION ALL
SELECT 2, 10 UNION ALL
SELECT 4, 30

-- Query
SELECT l.pk, l.value,
r.pk, r.value,
result = CASE WHEN l.pk = r.pk AND l.value = r.value THEN 0
WHEN l.pk = r.pk AND l.value <> r.value THEN 1
WHEN r.pk IS NULL THEN 2
WHEN l.pk IS NULL THEN 3
END
FROM @LEFT l
FULL OUTER JOIN @RIGHT r ON l.pk = r.pk

/*
pk value pk value result
----------- ----------- ----------- ----------- -----------
0 0 0 0 0
1 10 1 12 1
3 30 NULL NULL 2
5 10 NULL NULL 2
NULL NULL 2 10 3
NULL NULL 4 30 3

(6 row(s) affected)
*/
[/code]

EDIT : Added some comments

KH
[spoiler]Time is always against us[/spoiler]

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-02-04 : 23:01:45

wow very cool

so what are do these mean?
what does 0,0 mean?

SELECT 0, 0 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 3, 30 UNION ALL
SELECT 5, 10

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-02-04 : 23:35:20

not sure why you have the selects with numbers in them

could you give an example if you had 2 tables, filled with data?

thanks again

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-04 : 23:38:14

quote:
Originally posted by jjmusicpro

wow very cool

so what are do these mean?
what does 0,0 mean?

SELECT 0, 0 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 3, 30 UNION ALL
SELECT 5, 10

that just some sample data to illustrate the full outer join query

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-04 : 23:39:40

quote:
Originally posted by jjmusicpro

not sure why you have the selects with numbers in them

could you give an example if you had 2 tables, filled with data?

thanks again

I did.

The @left, @right are 2 sample table

The insert . . select . . are to fill the sample table with some sample data.

KH
[spoiler]Time is always against us[/spoiler]

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-02-04 : 23:44:44

sorry for all the messages, just not sure why i need

DECLARE @LEFT TABLE
(
pk int,
value int
)

DECLARE @RIGHT TABLE
(
pk int,
value int
)

INSERT INTO @LEFT (pk, value)
SELECT 0, 0 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 3, 30 UNION ALL
SELECT 5, 10

INSERT INTO @RIGHT (pk, value)
SELECT 0, 0 UNION ALL
SELECT 1, 12 UNION ALL
SELECT 2, 10 UNION ALL
SELECT 4, 30

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-04 : 23:46:22

quote:
Originally posted by jjmusicpro

sorry for all the messages, just not sure why i need

DECLARE @LEFT TABLE
(
pk int,
value int
)

DECLARE @RIGHT TABLE
(
pk int,
value int
)

INSERT INTO @LEFT (pk, value)
SELECT 0, 0 UNION ALL
SELECT 1, 10 UNION ALL
SELECT 3, 30 UNION ALL
SELECT 5, 10

INSERT INTO @RIGHT (pk, value)
SELECT 0, 0 UNION ALL
SELECT 1, 12 UNION ALL
SELECT 2, 10 UNION ALL
SELECT 4, 30

Nope. You don't need this. Just replace @LEFT and @RIGHT with your actual table name in the "Query" part.

KH
[spoiler]Time is always against us[/spoiler]

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-02-04 : 23:51:06

so i put my table names in for this part also?

FROM @LEFT l
FULL OUTER JOIN @RIGHT r ON l.pk = r.pk

so it woud look like

FROM left_table_name.l
FULL OUTER JOIN right_table_name.r ON l.pk = r.pk

?

sorry for all the questions...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-04 : 23:53:51

[code]
SELECT l.pk, l.value,
r.pk, r.value,
result = CASE WHEN l.pk = r.pk AND l.value = r.value THEN 0
WHEN l.pk = r.pk AND l.value <> r.value THEN 1
WHEN r.pk IS NULL THEN 2
WHEN l.pk IS NULL THEN 3
END
FROM left_table_name l
FULL OUTER JOIN right_table_name r ON l.pk = r.pk
[/code]

KH
[spoiler]Time is always against us[/spoiler]

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-02-05 : 00:05:23

ok i am confused, what is 1.pk, 1.value?

I have 2 tables right now

test_table_1
- id
- username
- school
- homefolder
- dn

test_table_2- id
- username
- school
- homefolder
- dn

both tables are the same

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-02-05 : 00:06:34

sorry let me fix that

test_table_1
- id
- username
- school
- homefolder
- dn

test_table_2
- id
- username
- school
- homefolder
- dn

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-02-05 : 00:13:33

Ok i think i got it working, what does this mean..

l.pk, l.value,

why wouldnt i just do test_table_1.username?

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-02-05 : 00:30:48

Ok think i got it.
How do i think only return values with a result of 0?

Can i put this into a view, and do a WHERE clause on the result column?

I tried this

SELECT	l.username, l.username,
		l.school, l.school,
		l.homefolder, l.homefolder,
		l.dn, l.dn,
		r.username, r.username,
		r.school, r.school,
		r.homefolder, r.homefolder,
		r.dn, r.dn,
		result = CASE 	
				WHEN l.username = r.username AND l.school = r.school AND l.homefolder = r.homefolder AND l.dn = r.dn THEN 0
				WHEN l.username = r.username AND l.school <> r.school OR l.homefolder <> r.homefolder OR l.dn <> r.dn THEN 1
				WHEN r.username IS NULL THEN 2
				WHEN l.username IS NULL THEN 3
				END
FROM test_table_1 l
FULL OUTER JOIN test_table_2 r ON l.username = r.username
WHERE result= 0

but it didnt like the WHERE part

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-02-05 : 00:38:53

again sorry for so many replies, how do i turn this into a view?

i tried

CREATE VIEW jf_test_result AS
SELECT	l.username,l.school,l.homefolder,l.dn,r.username,r.school,r.homefolder,r.dn,
		result = CASE 	
				WHEN l.username = r.username AND l.school = r.school AND l.homefolder = r.homefolder AND l.dn = r.dn THEN 0
				WHEN l.username = r.username AND l.school <> r.school OR l.homefolder <> r.homefolder OR l.dn <> r.dn THEN 1
				WHEN r.username IS NULL THEN 2
				WHEN l.username IS NULL THEN 3
				END
FROM test_table_1 l
FULL OUTER JOIN test_table_2 r ON l.username = r.username

but it threw and error saying i needed unique collumn names.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-05 : 01:05:13

[code]
CREATE VIEW jf_test_result AS
SELECT username = coalesce(l.username, r.username),
l_school = l.school,
l_homefolder = l.homefolder,
l_dn = l.dn,
r_school = r.school,
r_homefolder = r.homefolder,
r_dn = r.dn,
result = CASE
WHEN l.username = r.username AND l.school = r.school AND l.homefolder = r.homefolder AND l.dn = r.dn THEN 0
WHEN l.username = r.username AND l.school <> r.school OR l.homefolder <> r.homefolder OR l.dn <> r.dn THEN 1
WHEN r.username IS NULL THEN 2
WHEN l.username IS NULL THEN 3
END
FROM test_table_1 l
FULL OUTER JOIN test_table_2 r ON l.username = r.username
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-05 : 01:08:18

quote:
How do i think only return values with a result of 0?

Can i put this into a view, and do a WHERE clause on the result column?

if you want only matching result from both table then just use INNER JOIN and not FULL OUTER JOIN


select *
from   test_table_1 l
       inner join test_table_2 r on l.username = r.username
where  l.school     = r.school
and    l.homefolder = r.homefolder
and    l.dn         = r.dn

KH
[spoiler]Time is always against us[/spoiler]

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-02-05 : 20:16:23

how can i make the pk we are working on for the join, to be case specific?
I noticed if the pk from table 1 was joey and pk from table 2 was joeY they would match.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources