I have a table1 (records 3), and table2 (records 3). Where i have field name in both. Now i want to make a result from those two table, which will show me both table records and take only one if there is duplicate. So my expected output records will contain 5 rows not 6 rows. How do i do that?
Example:
table1: table2:
+-------------------------+ +--------------------------------+
| Name | Name
--------------------------- +---------------------------------
| A | | C |
| B | | D |
| C | | E |
My Expected output is:
+-------------------------+
| Name | ID
---------------------------
| A | 1 table1
| B | 2 table1
| C | 3 table2 or table1 (no unique)
| D | 4 table2
| E | 5 table2
I tried this:
SELECT
name as name
FROM
table1
UNION
SELECT
anothernamename as name
FROM
table2
WHERE
name like '%C%'
Error: #1054 - Unknown column 'name' in 'where clause'
Follow up: Union with Where clause + extra extra large requirement.
SELECT * FROM
(
(
SELECT * FROM table1
WHERE ...
ORDER BY ...
LIMIT ...
)
UNION
(
SELECT * FROM table2
WHERE ...
ORDER BY ...
LIMIT ...
)
) as t
WHERE ...
ORDER BY ...