Select with JOIN

Navigation
0

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

I have the property table and the photo table, the property table has several records, and each record has several photos, but when I run select, it takes into consideration the amount of photos that the property has, I need that is shown only one photo for 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');
    
asked by anonymous 27.01.2017 / 14:49

2 answers

5

Use limit , like this: 

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

I believe that if you want to display 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

For properties without a photo to be listed, change JOIN to 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:

27.01.2017 / 14:50
0

You can make JOIN from 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 after that it will apply JOIN

    
27.01.2017 / 15:00
Handling foreign key errors in Codeigniter Is the Node server the only option to support the maximum level of requests in an application?