[SOLVED] joining two tables at interesting points

[ultrus]

Hello,

Getting a query made that combines the number of visits and purchases on a website is giving me a headache. I'm having difficulties joining a couple tables by the same on-the-fly created columns "ym" (year month). How would you do this? Here is what I have so far:

 

Table: visits

id (auto incremented)

ip (user's ip for use in separate unique visit query unrelated to this post)

time (timestamp e.g. 2008-05-11 22:11:13)

 

Table: purchases

id (auto incremented)

buyer_id (provided by payment processor, unrelated to this post)

time (same format as visits table)

 

To get number of visits per month, this query brings great success:

SELECT DATE_FORMAT(time, '%b %Y') ym, DATE_FORMAT(time, '%c') month, DATE_FORMAT(time, '%Y') year, COUNT(*) as hits FROM visits GROUP BY ym

 

I could probably get the number of purchases per month like this:

SELECT DATE_FORMAT(time, '%b %Y') ym, DATE_FORMAT(time, '%c') month, DATE_FORMAT(time, '%Y') year, COUNT(*) as hits FROM purchases GROUP BY ym

 

How would I join the two queries into one?  ??? This is probably easier than I'm making it out to be. It's probably time for sleep. 

 

Thank you much in advance.

[fenway]

Join how?

[ultrus]

Well, now that I think about it, the question now is: Why join?

 

I found another way around my problem by doing a second, separate query. It works well in the formatting of my page. Thanks for the feedback. I think It's time to sleep now.