0

I'm trying to return a combined result of data from 2 tables, but need to merge the ID column from both to form one result set, the full outer join is the closest I have to returning the correct row number.

Example:

T1 

ID  A
a   s
b   s
e   s
f   s

T2 

ID  B
a   a
c   a
d   a
f   a

Result 

ID   A      B
a    s      a
b    s      NULL
c    NULL   a
d    NULL   a
e    s      NULL
f    s      a



    declare @t1 table (
                  ID varchar(1),
                  A varchar(1)
                 )
insert into @t1 values ('a','s')
insert into @t1 values ('b','s')
insert into @t1 values ('e','s')
insert into @t1 values ('f','s')

declare @t2 table (
                  ID varchar(1),
                  B varchar(1)
                 )

insert into @t2 values ('a','a')
insert into @t2 values ('c','a')
insert into @t2 values ('d','a')
insert into @t2 values ('f','a')

select * from @t1
select * from @t2
Mahesh
  • 8,694
  • 2
  • 32
  • 53
Wayne Crick
  • 11
  • 3

1 Answers1

0

If you are using MySQL; you don't have FULL JOINS in MySQL, but you can surely emulate them.

For a code SAMPLE transcribed from this SO question you have:

with two tables t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

So your query becomes:

SELECT COALESCE(t1.ID, t2.ID) As ID, t1.A As A, t2.B As B FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
UNION
SELECT COALESCE(t1.ID, t2.ID) As ID, t1.A As A, t2.B As B FROM table1 t1
RIGHT JOIN table2 t2 ON t1.id = t2.id
order by ID

It gives me correct result with your inputs as:

ID   A      B
a    s      a
b    s      NULL
c    NULL   a
d    NULL   a
e    s      NULL
f    s      a
Community
  • 1
  • 1
Tushar
  • 3,527
  • 9
  • 27
  • 49
  • 1
    Hi @Avidan, this works for me, thank you, I have not looked at COALESCE before, have learned something new.. many thanks – Wayne Crick Mar 26 '15 at 12:57
  • you know that You are basically doing full join with your query but with long way around. – Mahesh Mar 26 '15 at 13:06
  • @CoderofCode Yes Buddy. Am doing it as there is no FULL Outer JOIN in MySQL as I know. Please go through the links I posted in answer. (By the way do u have any shorter way to do it?) – Tushar Mar 26 '15 at 13:08
  • @CoderofCode By the way; I gone through your answer too. :) I guess it will work with SQL Server. It didn't work with me on mysql. – Tushar Mar 26 '15 at 13:09
  • Question was tagged with `SQL server` first the `mysql` tag was not there at the time of the writing an answer. By adding that tag OP made answer look stupid. – Mahesh Mar 26 '15 at 13:10
  • @CoderofCode Nope buddy. He had added both the tags. Please go through the edit history. So I asked him... `Which database you are using?` and as already you posted an answer for ms sql server; I choose to post fr MySQL as my answer should `not look` your copy paste :) – Tushar Mar 26 '15 at 13:14
  • @CoderofCode By the way you would not have deleted the answer. I was just searching to upvote it, as logic is correct and question has `sql server` tag too. With many deleted posts(answers) Stack Overflow can ban from posting answers :( :) – Tushar Mar 26 '15 at 13:20
  • It will not make any sense to upvote it. Due to the fact that OP wanted to for `MySql`. It will not do any good. – Mahesh Mar 26 '15 at 13:23
  • @CoderofCode Buddy answers are upvoted when those are right; no matter OP accepts those or not. Your answer was correct based on question. But now Am sorry. Right now tags are removed :( Oh.. You itself removed it.. Great guy :) and good attitude :) – Tushar Mar 26 '15 at 13:26
  • 1
    I removed the tags to match what OP wanted. The upvote doesn't matter to me. It just some lousy points.so mehh no problem there :) – Mahesh Mar 26 '15 at 13:29
  • Just to note, I'm using MSSQL 2012, next time I will be clearer on the technical details, this is my first post :) – Wayne Crick Mar 26 '15 at 13:46