Merge and sort data from two tables

[guvenck]

Hi, I have two (maybe three) tables. In general, they have the same fieldnames.

Table A:
> ID, title, day, month

Table B:
> ID, title, day, month, year

For Table A data, the year value is the current year: $year = date("Y");

I would like to merge two tables' data and display them sorted according to date.

How can I do that?

[raf]

is there some specific reason why you don't use a date column instead of three columns?

anyway, for your current design, use

(SELECT ID, title, day, month, YEAR(Now()) FROM tablea) UNION (SELECT ID, title, day, month, year FROM tableb) ORDER BY year ASC, month ASC, day ASC

[guvenck]

Hi raf, thanks for the reply. Never heard of UNION before. Heard of JOIN but not UNION yet. What are the restrictions to use UNION? Exact count of columns?

date column:
I make queries for rows that fit to current day, month, year or a specific day, month, year. I make calculations and update these rows. Don't know if I can make the calculations using a single date column.

[raf]

What are the restrictions to use UNION? Exact count of columns?

UNION will just create one resultset out of the resultsets from the selects, and will then allow you to further process it (like sorting it or whatever)
The only restriction is that each resultset needs to have the same number of columns (--> each selectstatement should have the same number of fields). But you can alsways 'fill up' the resultset of one of the selects with a fixed value or a blank, like

(SELECT ID, title, day, month, 1) FROM tablea) UNION (SELECT ID, '', day, month, year FROM tableb) ORDER BY year ASC, month ASC, day ASC


date column:
I make queries for rows that fit to current day, month, year or a specific day, month, year. I make calculations and update these rows. Don't know if I can make the calculations using a single date column.
You can extract the day, month, year from a date using the build in MySQL datefunctions --> http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

[guvenck]

What if I have such two tables:

Table A:
> ID, title
1, Mother's Day
2, Father's Day

Table B:
> ID, day, month, year
1, 14, 5, 2006
2, 18, 6, 2006
1, 13, 5, 2007
2, 17, 6, 2007
1, 11, 5, 2008
2, 15, 6, 2008

and want to pull the titles that fit to current year and month?

[raf]

then you need to join the two tables. for instance like this

SELECT tablea.title tableb.month, tableb.year FROM tablea INNER JOIN tableb ON tablea.ID=tableb.ID

[guvenck]

Is there no way to do it without a join?

[raf]

Is there no way to do it without a join?
Why? are you a joinofoob ?

There are always other ways, but a join will probably be the easiest, most performant, most dynamic, quickest to implement and maintenancefree way.