Duplicate select in two tables

Question

Duplicate select in two tables

Navigation

#1 by (2 votes)
#2 by (0 votes)

2

I'm doing a system in PHP, but I'm stopped because I can not find the solution to a challenge.

I have 2 tables, one call objetos and another call gavetas . I have several names of drawers registered in the gavetas table and I need to know which ones have objects inside them.

I'm trying to make a select where regardless of whether or not to have an object inside the drawer, bring all the records of the gavetas table and the ones that have object, display the name of the object on the side. And when you have (Pen) inside the drawer PHP prints the (found) word next to the object name. I've tried INNER JOIN / LEFT JOIN / RIGHT JOIN , but I'm not getting the desired result.

Tables:

**Gavetas**

id
nome-gaveta

**Objetos**

id
nome-gaveta
nome-objeto

Example

Gaveta 1 / Caneta - Encontrado
Gaveta 2 /
Gaveta 3 /
Gaveta 4 /
Gaveta 5 / Borracha

php sql

asked by anonymous 16.08.2017 / 19:02

2 answers

Is it possible to know in which routes.Map () the call of an Action crashed? ASP.NET MVC5 Laravel - Relationship ManyToMany extra columns

score 2 · Answer 1

You should use LEFT JOIN to search the second table even if there are no records. After that concatenate the result, if you want one line per drawer:

SELECT g.nome_gaveta,
       CONCAT(GROUP_CONCAT(o.nome_objeto SEPARATOR ', '),
       (SELECT ' - Encontrado'
          FROM objetos o2
         WHERE o2.nome_objeto = 'Caneta'
         LIMIT 1)) AS objetos
  FROM gavetas g
       LEFT JOIN objetos o ON o.nome_gaveta = g.nome_gaveta

If you want one line per object the query will be as follows:

SELECT g.nome_gaveta,
       CONCAT(o.nome_objeto,
       CASE o.nome_objeto
         WHEN 'Caneta' THEN ' - Encontrado'
         ELSE ''
       END) AS nome_objeto
  FROM gavetas g
       LEFT JOIN objetos o ON o.nome_gaveta = g.nome_gaveta

score 0 · Answer 2

Another solution, using CASE rather than sub-select:

SELECT
    g.nome_gaveta,
    GROUP_CONCAT((
      CASE o.nome_objeto
        WHEN 'Caneta' THEN CONCAT(o.nome_objeto, ' - Encontrado')
        ELSE o.nome_objeto
      END
    ) SEPARATOR ', ') AS nome_objeto
FROM
    gavetas AS g
    LEFT JOIN objetos AS o ON g.nome_gaveta = o.nome_gaveta
GROUP BY
    g.id

Fiddle: link