Doubt select in two tables

Asked

Viewed 80 times

2

I’m making a system in PHP, but I’m stuck because I can’t find the solution to a challenge.

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

I’m trying to make a select where whether or not you have an object inside the drawer, bring all the records from the table gavetas and those that have object, is displayed the name of the object on the side. And when you have ( Pen ) inside the drawer PHP prints the word ( found ) 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
  • 2

    Have you tried a select using full Outer Join? select from table a full Outer Join table b on a.id = b.id, if possible post the select q vc is trying to do.. hugs

  • 1

    What the SGBD used?

2 answers

2

You should wear a LEFT JOIN to search in the second table even if there are no records. After this concaten the result, if you want one row 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 a query will be the following:

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

0

Another solution, using CASE instead of 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: https://www.db-fiddle.com/f/rNZT8bSFPrcajmW89TZ6J2/0

Browser other questions tagged php sql

You are not signed in. Login or sign up in order to post.