Estoy tratando de seleccionar varias tablas sin unirme y sin ninguna relación. todo funciona perfectamente, pero tengo un problema de que la salida resultante se repite por duplicado.

Estoy tratando de representar mediante diagramas.

`table_in`

|------|------------------|--------------|-------|
|  ID  | component_in_key |  insert_date |  type |
|------|------------------|--------------|-------|
|  1   | 123456789        |  2021-02-01  |  I    |
--------------------------------------------------

`table_request`

|------|-------------------|--------------|-------|
|  ID  | component_req_key |  insert_date |  type |
|------|-------------------|--------------|-------|
|  1   | 123456789         |  2021-02-02  |   R   |
|  2   | 123456789         |  2021-02-03  |   R   |
|  3   | 123456789         |  2021-02-04  |   R   |
---------------------------------------------------

`table_approve`

|------|-------------------|--------------|-------|
|  ID  | component_apv_key |  insert_date |  type |
|------|-------------------|--------------|-------|
|  1   | 123456789         |  2021-02-05  |   A   |
|  2   | 123456789         |  2021-02-07  |   A   |
|  3   | 123456789         |  2021-02-08  |   A   |
---------------------------------------------------

probar SQL es: SELECT * FROM table_in, table_request, table_approve WHERE table_in.component_in_key = 123456789 AS component AND table_request.component_req_key = 123456789 AND table_approve.component_apv_key= 123456789 ORDER BY table_in.insert_date AND table_request.insert_date AND table_approve.insert_date

SALIDA table_in.component_in_key se repite 3 veces.

BUSCANDO SALIDA

|------+---------------+--------------+-------+
|  ID  | component |  insert_date |  type |
-----------------------------------------------
|  1   | 123456789     |  2021-02-01  |  I    |
|  2   | 123456789     |  2021-02-02  |  R    |
|  3   | 123456789     |  2021-02-03  |  R    |
|  4   | 123456789     |  2021-02-04  |  R    |
|  5   | 123456789     |  2021-02-05  |  A    |
|  6   | 123456789     |  2021-02-07  |  A    |
|  7   | 123456789     |  2021-02-08  |  A    |
 ---------------------------------------------

Por favor, ayúdenme a hacer que esto sea PHP SQL. Estoy intentando esto durante los últimos 3 días, pero no funciona y no hay subprocesos de StackOverflow que funcionen en mi caso.

1
Satyam Singh 15 mar. 2021 a las 08:27

1 respuesta

La mejor respuesta

Creo que lo que está buscando es una consulta UNION:

SELECT ID, component_key, insert_date, type
FROM (
  SELECT ID, component_in_key AS component_key, insert_date, type
  FROM table_in
  UNION ALL
  SELECT ID, component_req_key, insert_date, type
  FROM table_request
  UNION ALL
  SELECT ID, component_apv_key, insert_date, type
  FROM table_approve
) u
WHERE component_key = 123456789
ORDER BY insert_date

Salida:

ID  component_key   insert_date     type
1   123456789       2021-02-01      I
1   123456789       2021-02-02      R
2   123456789       2021-02-03      R
3   123456789       2021-02-04      R
1   123456789       2021-02-05      A
2   123456789       2021-02-07      A
3   123456789       2021-02-08      A

Demo en db-fiddle

0
Nick 15 mar. 2021 a las 07:11