Melkij

select id_item from tablename where (prop = 'naz2' and val = 2) or (prop = 'naz1' and val = 1)
group by id_item
having count(0)=2

select id_item from tablename t1
join tablename t2 using(id_item)
where (t2.prop = 'naz2' and t2.val = 2) and (t1.prop = 'naz1' and t1.val = 1)

SELECT /**/, current_country.name as current_country_name, home.name as home_country_name  FROM `users` 
INNER JOIN `countries` as current_country using(`country`)
INNER JOIN `countries` as home ON home.country = users.home_country

set @i := -1;
update tablename set id = (@i := @i+1 ) order by id;

Foreign Key Usage and Error Information

You can obtain general information about foreign keys and their usage from querying the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table, and more information more specific to InnoDB tables can be found in the INNODB_SYS_FOREIGN and INNODB_SYS_FOREIGN_COLS tables, also in the INFORMATION_SCHEMA database. See also Section 13.1.17.3, “Using FOREIGN KEY Constraints”.

In addition to SHOW ERRORS, in the event of a foreign key error involving InnoDB tables (usually Error 150 in the MySQL Server), you can obtain a detailed explanation of the most recent InnoDB foreign key error by checking the output of SHOW ENGINE INNODB STATUS.

select id, coords from orders join (
select coords from orders group by coords having count(0)>1
) nonuniqcoords using(coords)

select /**/ from documents docs left join jobs on jobs.id_doc=docs.id

insert into `post_ex` (`news_id`) select `post`.`id` from `post` left join post_ex on news_id=post.id where news_id is null

insert into `post_ex` (`news_id`) select `post`.`id` from `post` where not exists (select 1 from post_ex where news_id=post.id)

SET @a:=0;
UPDATE dle_products SET top=@a:=@a+1  WHERE tt != '0' ORDER BY tt DESC, kk DESC;