基本 SQL 子查询 [英] Basic SQL sub-query

问题描述

我有一张名为 products 的表格.基本上我需要在这样的各个字段中使用相同的单词进行搜索，这样可以正常工作.

I've got a table called products. Basically I need to search using the same word within various fields like this which works fine.

SELECT
    `id`,
    `product-id`,
    `country`,
    `name`,
    `description`,
    `branch`,
    `stock`,
    `price`
FROM
    `products`
WHERE
    `name` LIKE "%car%"
OR `description` LIKE "%car%"
OR `branch` LIKE "%car%"
OR `product-id` LIKE "%car%"

问题是现在我想要一个不同的查询.我只想显示来自特定国家/地区的所有汽车，以及其他字段.所以如果我运行这个查询

The problem is that now I want a different query. I'd like to show all the cars from an specific country only, plus the additional fields. So if I run this query

SELECT
    DISTINCT(b.`id`) id,
    a.id,
    a.`product-id`,
    a.`country`,
    a.`name`,
    a.`description`,
    a.`branch`,
    a.`stock`,
    a.`price`
FROM
    `products` as a,
(
    SELECT
        *
    FROM
        `products`
    WHERE
        `country` = "Canada"
    LIMIT 0,
    10
)AS b
WHERE
    a.`id` = b.`id`
OR  b.`product-id` LIKE "%car%"
OR  b.`name` LIKE "%car%"
OR  b.`branch` LIKE "%car%"
OR  b.`description` LIKE "%car%"

LIMIT 0, 10

我从多个国家/地区获得结果，我做错了什么?

I get results from more than one country, what am I doing wrong?

提前致谢

推荐答案

你只需要将你的条件分组，

you just need to group your conditions,

SELECT  ...
FROM    ...
WHERE   (a.`id` = b.`id`) AND
        (  
            b.`product-id` LIKE "%car%" OR  
            b.`name` LIKE "%car%" OR  
            b.`branch` LIKE "%car%" OR  
            b.`description` LIKE "%car%"
        )

这篇关于基本 SQL 子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！