sql - 从 2 个表中搜索方法

发布于 2024-12-08 16:36:02 字数 491 浏览 0 评论 0原文

我有一个网站，搜索功能非常基本，而且总是能完成它需要做的事情。我现在想将另一个表中的值合并到搜索中，这有点棘手。

我想从一个查询返回一组完整的结果，但新信息仅与我想要返回的另一个表中的数据相关（通过 id）。

我目前

SELECT      *
FROM        [TABLE]
WHERE       ( LOWER(title) LIKE '%$search%'
              OR LOWER(contents) LIKE '%$search%'
            )
            AND type = 'product'

在另一个表中有一个名为 id 的列（与其他相关的相同）和另一个元值。

我不仅想执行上面的操作，而且如果匹配从另一个表（使用 id）获取结果，还可以搜索 meta_value 数据。

如果有人理解我在说什么并且这是可能的，那么如果您能将我推向正确的方向，我将不胜感激。

原文

I have a site up and the search feature was very basic and always has done what it needs to do.
I now want to incorperate a value from another table into the search which gets a bit tricky.

I want to return a complete set of results from one query, but the new info only relates (via id) the the data in the other table which I want to return.

I currently have

SELECT      *
FROM        [TABLE]
WHERE       ( LOWER(title) LIKE '%$search%'
              OR LOWER(contents) LIKE '%$search%'
            )
            AND type = 'product'

in the other table there is a col called id (same as the other which are related) and another meta_value.

I want to not only do the operation above but also search the meta_value data, if a match get the results from the other table (using the id).

If any one understands what I am saying and it is possible then I would be muchly appreciated if you could push me in the right direction.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

焚却相思 2024-12-15 16:36:02

你的问题有点难以理解，但这是我的尝试。您似乎想要加入“其他表”中可能存在或不存在的 ID。只需使用 LEFT JOIN 即可：

SELECT      *
FROM        [TABLE]
LEFT JOIN   [OTHER_TABLE] ON [OTHER_TABLE].ID = [TABLE].ID
WHERE       (
              LOWER(title) LIKE '%$search%'
              OR LOWER(contents) LIKE '%$search%'
              OR LOWER(ISNULL([OTHER_TABLE].meta_value, '')) LIKE '%$search%'
            )
            AND type = 'product'

You're question is a bit tough to understand, but here's my stab at it. It appears that you want to join on an ID that may or may not exist in the "other table". Simply use a LEFT JOIN for this:

SELECT      *
FROM        [TABLE]
LEFT JOIN   [OTHER_TABLE] ON [OTHER_TABLE].ID = [TABLE].ID
WHERE       (
              LOWER(title) LIKE '%$search%'
              OR LOWER(contents) LIKE '%$search%'
              OR LOWER(ISNULL([OTHER_TABLE].meta_value, '')) LIKE '%$search%'
            )
            AND type = 'product'

回复收藏 0 原文

高冷爸爸 2024-12-15 16:36:02

您可以使用 LEFT JOIN 命令通过 ID 来连接两个表。然后，您可以过滤任一表中的列。

SELECT *
FROM [TABLE] t1
LEFT JOIN otherTable t2 on t1.ID=t2.ID
WHERE
  (
    LOWER(t1.title) LIKE '%$search%' 
    OR LOWER(t1.contents) LIKE '%$search%'
    OR LOWER(t2.meta_value) LIKE '%$search%'
  )
  AND t1.type = 'product'

You can join the two tables using their IDs using the LEFT JOIN command. You can then filter on columns in either table.

SELECT *
FROM [TABLE] t1
LEFT JOIN otherTable t2 on t1.ID=t2.ID
WHERE
  (
    LOWER(t1.title) LIKE '%$search%' 
    OR LOWER(t1.contents) LIKE '%$search%'
    OR LOWER(t2.meta_value) LIKE '%$search%'
  )
  AND t1.type = 'product'

回复收藏 0 原文

鹤舞 2024-12-15 16:36:02

使用上面的两个例子就可以了：

SELECT * 
FROM  `ecom_page_data` t
LEFT JOIN  `ecom_page_meta` t2 ON t.id = t2.post_id
WHERE
( 
    ( LOWER( t.title ) LIKE  '%RE9901%'
      OR LOWER( t.contents ) LIKE  '%RE9901%'   
      OR LOWER( t2.meta_value ) LIKE  '%RE9901%'
    ) 
    AND t.type = 'product' AND t2.meta_name = 'single-text-input-product-part-number'
)

非常感谢大家

This worked using the 2 examples above:

SELECT * 
FROM  `ecom_page_data` t
LEFT JOIN  `ecom_page_meta` t2 ON t.id = t2.post_id
WHERE
( 
    ( LOWER( t.title ) LIKE  '%RE9901%'
      OR LOWER( t.contents ) LIKE  '%RE9901%'   
      OR LOWER( t2.meta_value ) LIKE  '%RE9901%'
    ) 
    AND t.type = 'product' AND t2.meta_name = 'single-text-input-product-part-number'
)

Thank you all very much

回复收藏 0 原文

~没有更多了~