1

I have a view and two tables. Tables one and two have the same columns, but table one is has as small number of records, and table two has old data and a huge number of records.

I have to join a view with these two tables to get the latest data from table one; if a record from the view is not available in table one then I have to select the record from table two.

How can i achieve this with MySQL?

I came to know by doing some research in internet that we can't apply full join and sub query in from clause.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
abc
  • 31
  • 1
  • 2
  • 5

3 Answers3

1

Just do a simple UNION of the results excluding the records in table2 that are already mentioned in table1:

SELECT * FROM table1
UNION
SELECT * FROM table2
WHERE NOT EXISTS (SELECT * FROM table1 WHERE table2.id = table1.id)
Peter Bowers
  • 3,063
  • 1
  • 10
  • 18
0

Something like this.

SELECT *
FROM   view1 V
       INNER JOIN (SELECT COALESCE(a.commoncol, b.commoncol) AS commoncol
                   FROM   table1 A
                          FULL OUTER JOIN table2 B
                                       ON A.commoncol = B.commoncol) C
               ON v.viewcol = c.commoncol

If you are using Mysql then check here to simulate Full Outer Join in MySQL

Community
  • 1
  • 1
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

are you trying to update the view from two tables where old record in view needs to be overwritten by latest/updated record from table1 and non existant records from table1 to be appended from table2? , or are you creating a view from two tables?

Cl_3518233
  • 25
  • 1
  • 1
  • 9
  • thanks for your replay. view is having 4 joins, not from those two tables. but mapping column is available. – abc May 26 '15 at 18:53
  • For example, view is fetching 5 records(1,2,3,4,5), those five records are may or may not be in available in table a, first we have to get the matching records from table a, if records are not found in record a then we have to fetch from table b. – abc May 26 '15 at 18:56