0

I have two queries that result two result sets i need to compare both the result sets and need to display the differences between them.Hope i will get good support.Thank you.These are my queries

Query:1 

SELECT distinct c.sid_ident,c.fix_ident from corept.std_sid_leg as c INNER JOIN (SELECT sid_ident, transition_ident, max(sequence_num) seq, route_type FROM corept.std_sid_leg  WHERE data_supplier='J' AND airport_ident='KBOS' GROUP BY sid_ident,transition_ident) b ON c.sequence_num=b.seq and c.sid_ident = b.sid_ident and c.transition_ident =b.transition_ident WHERE c.data_supplier='J' and c.airport_ident='KBOS';

Query:2 

SELECT name,trans FROM skyplan_deploy.deploy_sids ON d.name=c.sid_ident WHERE apt = 'KBOS' AND name != trans;

Comparison is to be done on fields sid_ident in corept.std_sid_leg and name in skplan_deplay.deploy_sids. As Mysql does not support full outer join,I thought of using left join and right join and combine both the results.But i stuck up with this.Please help.I am getting syntax error while using left and right join.Thank you.

Code Lღver
  • 15,573
  • 16
  • 56
  • 75
user2037445
  • 161
  • 1
  • 2
  • 11
  • Provide me the syntax to combine the above two queries using left join as well as right join..so that i can union both the results – user2037445 Mar 21 '13 at 09:05
  • Using the search function is always a good start. Put your queries into subqueries and read this: http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql – fancyPants Mar 21 '13 at 09:45

1 Answers1

1

The following query should simulate a FULL OUTER JOIN in MySQL.

SELECT * 
FROM A
LEFT OUTER JOIN B
  ON A.NAME = B.NAME
WHERE B.ID IS NULL
  UNION ALL
SELECT * 
FROM B
LEFT OUTER JOIN A
  ON B.NAME = A.NAME
WHERE A.ID IS NULL;

Compare the results of the with an actual FULL OUTER JOIN in SQL Server and you'll see it works.

JodyT
  • 4,324
  • 2
  • 19
  • 31
  • what is the significance of a.id is null? – user2037445 Mar 21 '13 at 10:06
  • Thank you so much..i have been stuck with this more than 3 hrs..just by placing that is null clause i solved the issue.Really thanx a lot – user2037445 Mar 21 '13 at 10:07
  • Have a look at [this link](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) to see the difference between a regular `FULL OUTER JOIN` and the one to return the difference. – JodyT Mar 21 '13 at 10:37