merge two tables with a column in comun

I have two tables

table inscripcion
------------------------------------------------------
|date         |   other fields  |
------------------------------------------------------


table visitas
------------------------------------------------------
|date         |   other fields  |
------------------------------------------------------

When I need the total of records of this tables group by date I use this querys:
SELECT date, COUNT( * ) AS total_inscripcion
FROM inscripcion
GROUP BY date

Open in new window


SELECT date, COUNT( * ) AS total_visitas
FROM visitas
GROUP BY date

Open in new window


I would like to make this querys in a single query where I receive something like this:
------------------------------------------------------------------
|date         |   total_inscripcion  |   total_visitas  |
------------------------------------------------------------------
Where date is the merge between the fields date from inscripcion and date from visitas
           total_inscripcion: total of records from inscripcion group by date
           total_visitas: total of records from visitas group by date.

I try this one:
SELECT DISTINCT t1.date, t1.total_inscripcion, t2.total_visitas
FROM (SELECT date, count( * ) AS total_inscripcion FROM inscripcion GROUP BY date)t1
LEFT JOIN (SELECT date, count( * ) AS total_visitas FROM visitas GROUP BY date)t2 ON t1.fecha = t2.date
UNION SELECT DISTINCT t2.date, t1.total_inscripcion, t2.total_visitas
FROM (SELECT date, count( * ) AS total_inscripcion FROM inscripcion GROUP BY date)t1
RIGHT JOIN (SELECT date, count( * ) AS total_visitas FROM visitas GROUP BY date)t2 ON t1.date = t2.date

Open in new window


I works, but I would like to know if somebody knows if there is a better options to do this query.


best regards
altariamx2003Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Try this

SELECT i.date, COUNT(*) AS InscripcionTotal, b.VisitasTotal 
  FROM inscripcion i
  LEFT JOIN (SELECT date AS VisitasDate, COUNT(*) AS VisitasTotal FROM visitas GROUP BY date) b
    ON i.date = b.VisitasDate
  GROUP by i.date

Open in new window

0
PortletPaulfreelancerCommented:
The issue here is that we have no idea which table has the most dates or if there are gaps of dates in either table to be concerned with, and hence a left join may produce an incomplete set of dates.

Unioning of the 2 results will resolve that problem.
SELECT
      date
    , SUM(total_visitas) AS total_visitas
    , SUM(total_inscripcion) AS total_inscripcion
FROM (
      SELECT
            date
          , 0 AS total_visitas
          , COUNT(*) AS total_inscripcion
      FROM inscripcion
      GROUP BY date
      UNION ALL
      SELECT
            date
          , COUNT(*) AS total_visitas
          , 0
      FROM visitas
      GROUP BY date
) AS g
GROUP BY date

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Julian HansenCommented:
I would go with Paul's solution
0
altariamx2003Author Commented:
thanks for take time to answer me

I appreciate it
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.