Combining Two tables and displaying data with count in sql php

Question

I have two tables as table1 and table2 and it contains Field1, Field2, Field3 in both tables. I need to combine and display the count of Field3 with distinct field2 value from both the tables.

ex:

Field1 Field2 Field3
1 | India  | VERIFIED
2 | US     | VERIFIED
3 | India  | VERIFIED

I need the output like count of Field2 with the Field2 Value where Field3 = verified

India (2) US (1)

I dont have any idea about join statement and i am beginner in SQL PHP. Any help appreciated.

Answer 1

SELECT Field2, count(Field2)
FROM (SELECT * FROM table1  UNION ALL SELECT * FROM table2) AS t
WHERE t.Field3 = 'VERIFIED'
GROUP BY Field2

UNIONcombines two sql query results into one. You could also apply your filter during the two queries.

But having two tables with exact the same field indicates bad databse design - You might read a bit about database normalization for getting a clue of good relational database design.

UPDATE: regarding the fiddle by paqogomez I identified a small problem. I needed to add the keyword ALL to avoid SQl treating my results as Sets and remvoing double entries (if two entries from both tables are exact the same) by default the DISTINCT keyword is applied.

Answer 2

You can achieve this result by using only 1 table by trying somthing lke this:-

SELECT COUNT(*) FROM TABLE1 WHERE FIELD3 = 'VERIFIED' GROUP BY FIELD2;

This might be helpful to you.

Answer 3

SELECT count(Field2) AS total,Field2
FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) AS t
WHERE t.Field3='VERIFIED'
GROUP BY t.Field2;