SQL Joining 2 similar tables

Question

Hi,

I have two tables which are used to store details of different types of events. The tables are almost identical, with fields such as date, duration etc. I'm trying to perform a join on the two tables and sort them by the mutual field 'date'.

I know it would be simpler to simply add a 'type' field to a single table and store it all in a single place, unfortunately the nature of the cms I am using does not allow this.

Is there a way to perform this simply? The following query returns no results.

$sql = "SELECT * FROM Events_One a, Events_Two b WHERE a.Date > now() OR b.Date > now() ORDER BY Date ASC LIMIT ".$limit;

Answer 1

You should look at UNION statement. It does exactly what you need.

SELECT columns FROM t1
UNION
SELECT columns FROM t2

gives you one set, which you can later filter or sort by whatever you want.

Answer 2

If you have a table for each event type, with identical fields for each one, I'd say that you should reconsider your design. That's breaking normalization rules. It's a poor design because it forces you to add another table every time a new event comes along. It's better if you can add a new event by adding data, like a new type value, to an existing table.

Answer 3

If you want to join two similar tables You can use Union or union all

Union:its Like a join Command.When using the union all the selected columns need to be of the same datatype.Only distinct values are selected.

Union All :it almost like union.There is no distinct operation so it will take all the values.

select  * FROM Events_One a   WHERE a.Date > now() 
union 
select  * FROM Events_Two b   WHERE b.Date > now()