How can I turn 2 queries into one in SQL?-CodePudding

Is it possible to turn this query into one line/query ?

//to get the product_id
SELECT * FROM `products` WHERE sku = "a-12AB"

//Then do this after having product_id
SELECT * FROM `shopify_export_product_ids` WHERE product_id = "23467"

CodePudding user response：

s bioth tables share productid, you can join them stil SELECT * is a bad szyle, as you usually you don't need all the coumns

so try to avoid it

SELECT sh.*,pr.* FROM `shopify_export_product_ids` sh INNER JOIN 
`products` pr ON sh.product_id= pr.product_id 
WHERE pr.sku = "a-12AB" and sh. product_id = "23467"

CodePudding user response：

Considering products has a column product_id and it's a primary key there and that product_id in shopify_export_product_ids is the foreign key, containing the values form the products.product_id, you can join the tables:

SELECT shopi.*
  FROM products prd
       INNER JOIN shopify_export_product_ids shopi ON shp.product_id = prd.product_id
 WHERE prd.sku = 'a-12AB'

the function of this will be to look up the product_id in products and match the value corresponding to sku = "a-12AB" and then retrieve the results from shopify_export_product_ids, corresponding to that product_id.

CodePudding user response：

You could use a UNION, so your code will look like this:

(SELECT *
    FROM `products`
    WHERE sku = "a-12AB")
UNION
(SELECT *
    FROM `shopify_export_product_ids`
    WHERE product_id = "23467")

Edit: like Stu mentionned, the structure of both tables needs to be the same, otherwise your final table will have NULL-values.

CodePudding user response：

If there is a foreign key that links the two tables, you can do something like

SELECT * FROM products p, shopify_export_product_ids s WHERE p.sku = "a-12AB" AND s.product_id = "23467" AND s.product_id = p.product_id;