Select with JOIN

Question

How do I limit this query to listing only one image record?

I have the table of real estate and the table of photos, the table of real estate has several records, and each record has several photos, but when I run the select, it takes into account the amount of photos that the property has, I need to be shown only one photo per property

SELECT * FROM imovel JOIN imagem ON id_imagem_imovel = id_imovel ORDER BY id_imovel DESC

The structure:

CREATE TABLE `imagem` (
  `id_foto` int(11) UNSIGNED NOT NULL,
  `id_imagem_imovel` int(11) NOT NULL,
  `foto` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



INSERT INTO `imagem` (`id_foto`, `id_imagem_imovel`, `foto`) VALUES
(12, 6, '6_383_27_01_2017_1485520949.jpg'),
(13, 7, '7_396_27_01_2017_1485521179.jpg'),
(14, 7, '7_403_27_01_2017_1485521179.jpg'),
(15, 7, '7_410_27_01_2017_1485521179.jpg'),
(16, 7, '7_417_27_01_2017_1485521179.jpg'),
(17, 7, '7_424_27_01_2017_1485521179.jpg'),
(18, 5, '5_429_27_01_2017_1485522171.jpeg'),
(20, 1, '1_434_27_01_2017_1485523016.jpeg'),
(21, 1, '1_435_27_01_2017_1485523016.jpeg'),
(22, 1, '1_436_27_01_2017_1485523016.jpeg'),
(23, 1, '1_437_27_01_2017_1485523016.jpeg'),
(24, 4, '4_441_27_01_2017_1485523040.jpg');



CREATE TABLE `imovel` (
  `id_imovel` int(11) NOT NULL,
  `nome_proprietario` varchar(150) NOT NULL,
  `telefone_proprietario` varchar(255) NOT NULL,
  `endereco_imovel` varchar(255) NOT NULL,
  `id_tipo` int(11) NOT NULL,
  `id_cidade` int(11) NOT NULL,
  `id_bairro` int(11) NOT NULL,
  `finalidade` enum('Alugar','Comprar') NOT NULL,
  `dormitorios` int(11) DEFAULT NULL,
  `suites` int(11) DEFAULT NULL,
  `banheiros` int(11) DEFAULT NULL,
  `garagem` int(11) DEFAULT NULL,
  `area` int(11) DEFAULT NULL,
  `valor` decimal(10,2) DEFAULT NULL,
  `descricao` text,
  `banner` tinyint(1) NOT NULL DEFAULT '0',
  `destaque` tinyint(1) NOT NULL DEFAULT '0',
  `dt_cadastro` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `imovel` (`id_imovel`, `nome_proprietario`, `telefone_proprietario`, `endereco_imovel`, `id_tipo`, `id_cidade`, `id_bairro`, `finalidade`, `dormitorios`, `suites`, `banheiros`, `garagem`, `area`, `valor`, `descricao`, `banner`, `destaque`, `dt_cadastro`) VALUES
(1, '', '', '', 1, 1, 1, 'Alugar', 3, 1, 2, 2, 100, '250000.00', 'Primeiro imóvel cadastrado.', 0, 1, '2017-01-23 19:30:37'),
(4, 'MARCOS', '', 'Rua , 286', 3, 1, 3, 'Comprar', 1, 1, 1, 1, 0, '1500.00', 'Descrição aqui', 1, 1, '0000-00-00 00:00:00'),
(5, 'PAULO ', '31989820602', 'Rua , 286', 3, 1, 4, 'Comprar', 3, 2, 1, 2, 0, '5222.00', 'Descrição aqui', 1, 1, '0000-00-00 00:00:00'),
(6, 'MARCOS PAULO ', '31989820602', 'Rua , 286', 3, 1, 4, 'Comprar', 3, 2, 1, 2, 0, '5222.00', 'Descrição aqui', 1, 1, '0000-00-00 00:00:00'),
(7, 'MARCOS PAULO ', '31989820602', 'Rua , 286', 3, 1, 4, 'Comprar', 3, 2, 1, 2, 0, '5222.00', 'Descrição aqui', 1, 1, '0000-00-00 00:00:00');

Answer 1

Use limit, thus:

SELECT *
FROM imovel JOIN imagem ON id_imagem_imovel = id_imovel
ORDER BY id_imovel DESC
LIMIT 1

I think if you want to show multiple properties you can use GROUP by:

SELECT *
FROM imovel JOIN imagem ON id_imagem_imovel = id_imovel
GROUP by id_imovel
ORDER BY id_imovel DESC

So that properties without photo are listed, change JOIN for LEFT JOIN:

SELECT *
FROM imovel LEFT JOIN imagem ON id_imagem_imovel = id_imovel
GROUP by id_imovel
ORDER BY id_imovel DESC

As explained in:

• What is the difference between INNER JOIN and OUTER JOIN?

Answer 2

You can make a JOIN from a subselect:

SELECT * FROM imovel 
JOIN 
(
    SELECT DISTINCT id_imagem_imovel FROM imagem 
    GROUP BY id_imagem_imovel
) AS imagem ON imagem.id_imagem_imovel = imovel.id_imovel 
ORDER BY imovel.id_imovel DESC

In this way, it will group the images by id of imovel and thereafter apply the JOIN