mysql - 非常困难的 SQL 查询合一(mysql)

Question

我对这个查询感到困惑。我需要在不同的表中搜索一个单词结构。我只有一个搜索框，我正在进行这三个查询以在不同的地方查找结果。我怎样才能在一个查询中完成所有这些以过滤所有内容？？？

1) 在此查询中(来自 PHP)，我使用表“programas_videos”的内连接在表“VIDEOS”的描述、表“PROGRAMAS”中的名称和描述中搜索单词“$palabra_buscar”

SELECT * FROM videos 
                 INNER JOIN programas_videos ON videos.id_video = programas_videos.id_video 
                 INNER JOIN programas ON programas_videos.id_prog = programas.id_prog 
                 WHERE programas.nombre_prog LIKE "%'.$palabra_buscar.'%" 
                 OR programas.descrip_larga LIKE "%'.$palabra_buscar.'%" 
                 OR videos.descrip_larga LIKE "%'.$palabra_buscar.'%" 
                 GROUP BY videos.id_video DESC LIMIT 10

2) 我有另一个查询正在其他表中搜索单词

SELECT * FROM videos
                   INNER JOIN contenidos1 ON videos.metadata = contenidos1.id_metadata 
                   WHERE contenidos1.Title LIKE "%'.$palabra_buscar.'%"
                   OR contenidos1.Summary_Short LIKE "%'.$palabra_buscar.'%"
                   OR contenidos1.Summary_Long LIKE "%'.$palabra_buscar.'%"
                   OR contenidos1.Genre LIKE "%'.$palabra_buscar.'%" LIMIT 10

3) 和这个查询

SELECT * FROM tableOfContents 
         WHERE tableOfContents .titulo LIKE "%'.$palabra_buscar.'%" 
         OR tableOfContents.descripcion LIKE "%'.$palabra_buscar.'%" 
         OR tableOfContents.categoria LIKE "%'.$palabra_buscar.'%" 
         GROUP BY tableOfContents.titulo  LIMIT 10

Answer 1

好的，

首先，您必须为列提供相同的名称，如下所示:

而不是使用:

SELECT * FROM videos  
SELECT * FROM tableOfContents

使用这样的东西:

SELECT name as col_name, title as col_title, image as col_image FROM videos
SELECT name as col_name, titlesss as col_title, imagesss as col_image FROM tableOfContents

您可以使用任何给定的名称作为新列名称。
这样，您就有了相同的列名，然后您可以像这样使用 union:

SELECT name as col_name, title as col_title, image as col_image, 'First_Table' as table_name FROM videos
INNER JOIN programas_videos ON videos.id_video = programas_videos.id_video 
INNER JOIN programas ON programas_videos.id_prog = programas.id_prog 
WHERE programas.nombre_prog LIKE "%'.$palabra_buscar.'%" 
OR programas.descrip_larga LIKE "%'.$palabra_buscar.'%" 
OR videos.descrip_larga LIKE "%'.$palabra_buscar.'%" 
GROUP BY videos.id_video DESC LIMIT 10
union
SELECT name as col_name, titlesss as col_title, imagesss as col_image, 'Second_Table' as table_name  FROM tableOfContents
WHERE tableOfContents .titulo LIKE "%'.$palabra_buscar.'%" 
OR tableOfContents.descripcion LIKE "%'.$palabra_buscar.'%" 
OR tableOfContents.categoria LIKE "%'.$palabra_buscar.'%" 
GROUP BY tableOfContents.titulo  LIMIT 10

我添加了 table_name 字段，以便您可以区分来自不同表的行。

您可以根据需要使用任意多个union。 here you can find the complete union syntax