0

i have two identical tables:

+-------------+------------+--------+--------+
| domain      | date       | score  | source |
+-------------+------------+--------+--------+

The table contains a domain name, (us)date, a score (float) and a source (like source A or source B). I need always the information from yesterday for an report.

But the same domain can be in Table A and Table B with a differed score. If that happens, i need only the higher score. If the score is identical, it doesn't matter which source is used.

A short example:

Table A:

+-------------+------------+--------+----------+
| example.org | 2018-12-12 | 0.98   | source A |
+-------------+------------+--------+----------+
+-------------+------------+--------+----------+
| mydomain.org| 2018-12-12 | 5.09   | source A |
+-------------+------------+--------+----------+

Table B:

+-------------+------------+--------+----------+
| example.org | 2018-12-12 | 2.11   | source B |
+-------------+------------+--------+----------+
+-------------+------------+--------+----------+
| company.com | 2018-12-12 | 9.77   | source B |
+-------------+------------+--------+----------+

In this example, i need the domain example.org from Table B:
example.org, 2.11 mydomain.com, 5.09 company.com, 9,77

I have tried it with union and joins but i have not received a correct result.

Does anyone have an idea?

Thank you for your efforts.

  • Please tag your MySQL version. – McNets Jul 30 '18 at 9:51
2

You don't need a source or a date in resultset at all. If so, you may use the simplest

SELECT domain, MAX(score) score
FROM (SELECT domain, score FROM tableA /* WHERE date = @date */
      UNION ALL
      SELECT domain, score FROM tableB /* WHERE date = @date */ ) dummy
GROUP BY domain

or (maybe more optimal)

SELECT domain, MAX(score) score
FROM (     SELECT domain, MAX(score) score 
           FROM tableA 
           /* WHERE date = @date */ 
           GROUP BY domain
      UNION ALL
           SELECT domain, MAX(score)
           FROM tableB 
           /* WHERE date = @date */ 
           GROUP BY domain 
     ) dummy
GROUP BY domain
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.