mysql - SQL不同的连接和WHERE子句可能会出错

Question

这是我的表格:

auth_keys:

id    /    keys    / user 
1     /    12345   /   1

*产品*:

id    / company    /   name
1     /  1         /   iphone
2     /  2         /   iphone

公司:

id    / name       /   mkey
1     / myComp     /   54321

这是我的 sql 请求:

SELECT 
    products.id_product AS id_product, 
    auth_keys.keys AS vkey,
    auth_keys.user AS user_id
FROM 
    auth_keys
JOIN 
    products
LEFT JOIN 
    companies ON products.company = companies.id
WHERE 
    products.name_product = "iphone"
    AND companies.mkey = "54321" 
HAVING 
    vkey = "none"

我想要的东西:

• 我通过先获取id_company然后获取name_product来获得id_products，因为我可以为两个人提供相同名称的产品不同的公司。这部分没问题。

• 我想要 user_key 值 WHERE keys = "12345"OR "NONE"，这就是我的问题。如果keys=“12345”一切正常，但如果keys=“none/nothing”我什么也得不到...

我想获得第一个结果 id_product，即使我无法匹配 user_key...

感谢您的帮助!

Answer 1

试试这个:

    SELECT 
    products.id_product AS id_product, 
    auth_keys.keys AS vkey,
    auth_keys.user AS user_id
    FROM auth_keys
    JOIN products
    LEFT JOIN companies ON products.company = companies.id
    WHERE 
    products.name_product = "iphone"
    AND (companies.mkey = "54321"or vkey = "none")

不需要“having”，因为它与“group by”子句一起使用来过滤聚合结果。