Export query from database from both table with rule-CodePudding

I've tables: products and product_details

Currently I have query to export data from both tables:

SELECT products.id, products.slug, products.sku, products.price, products.vat_rate, products.stock, products.brand, products.ean, products.gender, product_details.title, product_details.description
FROM products, product_details
WHERE products.id = product_details.id;

due to the fact that it is an export of products from two tables to XML, so the data must be consistent and correspond to the same product from table1 and from table 2. (query need check the same ID from both tables"

So this is rule:

WHERE products.id = product_details.id

But I need to add one extra rule and export only records from table products where column stock is = 1 or more than 1 and then JOIN other data from the same product ID from table product_details

any solution ?

CodePudding user response：

Let's start by rewriting your query so it uses standard joins, and table aliases for brevity:

SELECT p.id, p.slug, p.sku, p.price, p.vat_rate, p.stock, p.brand, p.ean, p.gender, 
    pd.title, pd.description 
FROM products p
INNER JOIN product_details pd ON p.id = pd.id

Here, ON p.id = pd.id is the join condition between the two tables, that ensures that only matching records from both tables are returned.

But I need to add one extra rule and export only records from table products where column stock is = 1 or more than 1 and then JOIN other data from the same product ID from table product_details

Now you can add your additional filtering criteria in the WHERE clause:

SELECT p.id, p.slug, p.sku, p.price, p.vat_rate, p.stock, p.brand, p.ean, p.gender, 
    pd.title, pd.description 
FROM products p
INNER JOIN product_details pd ON p.id = pd.id
WHERE p.stock >= 1