You need to make Join two tables (one-to-many relationship). Everything is fine here, but I have 2 entries for one domain.

enter image description here

In principle, also not difficult

 GROUP BY `name`

But

enter image description here

As you can see after the grouping, he left the first value, but I need to leave the last, and best of all there is a date field to leave on it, which has the most recent

 SELECT `domain`.`id` AS `id`, `domain`.`dns_a` AS `dns_a`, `domain`.`name` AS `name`, `domain_info`.`keywords` AS `keywords`, `domain_info`.`keywords_dynamic` AS `keywords_dynamic`, `domain_info`.`date` AS `date`, `domain_info`.`se` AS `se`, `domain`.`published` AS `published` FROM ( domain LEFT OUTER JOIN `domain_info` ON ( ( domain.id = `domain_info`.domain_id ) ) ) GROUP BY `name`

I tried through WHERE and HAVING - it turned out, but then it does not leave those lines in which there is a domain, but there are still no statistics.

    2 answers 2

    You have a standard task of searching for the most recent records, a bit burdened by the fact that this selection is needed from the second table in join. There are several approaches, which of them works faster depends on many factors, such as the number of records in both tables, the number of records selected by the conditions of this query, and others.

    If there is a primary key in the domain_info table (suppose with the name dominfo_id), then you can try this solution:

     SELECT ... FROM domain A LEFT OUTER JOIN `domain_info` B ON B.dominfo_id= ( select dominfo_id from domain_info C where A.id = C.domain_id order by date desc limit 1 )

    Sample on sqlfiddle.com

    If there is no primary key, then the subquery should return the maximum date itself, and the ON condition will look something like this ON A.id=B.domain_id and B.date = (select max(date) ...)

    You can also try one of the approaches described in this answer .

    • I tried your example sqlfiddle.com/#!9/b876a/5 But for some reason it also does not work that way + does not leave empty rows at all ... or did I do something wrong? - MaximusFT
    • @MaximusFT Firstly, for some reason, you wrote the max () function which, without group by, collected everything up to one line. Secondly, you have not declared the primary key in the second table. It should contain both the domain id, which can be repeated, and its own primary key, which, of course, cannot be repeated (which is why it is primary). It turns out like this: sqlfiddle.com/#!9/f0f46/1/0 - Mike
    • Thank you very much! - MaximusFT

    It is enough to specify when sampling max (date) and perform grouping. Then, when grouping, the maximum value for this group will be selected by date. If it is null, it will remain null. That is, all domains will be listed. Here so SQLFiddle

     select domain.name, max(domain_info.date) from domain left join domain_info on domain.id = domain_info.domain_id group by domain.name;
    • added a column to your example VIS and this is what happened sqlfiddle.com/#!9/b876a/3 The date comes correctly, but the column VIS is not from that line ... Any thoughts? - MaximusFT