SQL - Combining Queries

37
February 11, 2019, at 9:30 PM

I have two queries that do exactly as I want, however, I want to try combine these.

Query 1:

SELECT DISTINCT categories.id FROM categories 
    INNER JOIN product_categories 
        ON product_categories.category_id=categories.id 
    INNER JOIN products 
        ON product_categories.product_id=products.id 
    WHERE 
        categories.is_sub_cat='1' AND 
        categories.is_paused!='1' AND 
        products.nexus='1'

Query 2:

SELECT DISTINCT categories.top_cat FROM categories 
    INNER JOIN product_categories 
        ON product_categories.category_id=categories.id 
    INNER JOIN products 
        ON product_categories.product_id=products.id 
    WHERE 
        categories.top_cat!='0' AND 
        categories.is_paused!='1' AND 
        products.nexus='1'

Would it be possible to combine these into 1 query or should I just combine the resulting arrays in PHP after doing both queries?

Answer 1

You can use UNION to merge two queries

SELECT DISTINCT categories.id FROM categories 
    INNER JOIN product_categories 
        ON product_categories.category_id=categories.id 
    INNER JOIN products 
        ON product_categories.product_id=products.id 
    WHERE 
        categories.is_sub_cat='1' AND 
        categories.is_paused!='1' AND 
        products.nexus='1'
UNION 
SELECT DISTINCT categories.top_cat FROM categories 
    INNER JOIN product_categories 
        ON product_categories.category_id=categories.id 
    INNER JOIN products 
        ON product_categories.product_id=products.id 
    WHERE 
        categories.top_cat!='0' AND 
        categories.is_paused!='1' AND 
        products.nexus='1'
Answer 2

you can use case when

SELECT case when (categories.is_sub_cat='1' AND  categories.is_paused!='1' AND 
        products.nexus='1') then categories.id
        else categories.top_cat end  as id
    FROM categories 
    INNER JOIN product_categories 
        ON product_categories.category_id=categories.id 
    INNER JOIN products 
        ON product_categories.product_id=products.id 
Answer 3

You can combine the queries as

SELECT DISTINCT categories.id, categories.top_cat FROM categories 
    INNER JOIN product_categories 
        ON product_categories.category_id=categories.id 
    INNER JOIN products 
        ON product_categories.product_id=products.id 
    WHERE 
        ( categories.is_sub_cat='1' OR categories.top_cat!='0' ) AND 
        categories.is_paused!='1' AND 
        products.nexus='1'

Alternatively, UNION ALL might be used provided the columns categories.id and categories.top_cat are of the same data type, or able to be casted to the same data type.

Answer 4

Yes, UNION seems like a legit option. For your case it should not matter, since you are selecting from the same table, but as a side note you should know that when using UNION generally you need to have equal number of columns in your tables(that is if they were different).

Answer 5

You can do this with conditions in the WHERE clause:

SELECT DISTINCT c.id
FROM categories c INNER JOIN
     product_categories pc
     ON pc.category_id = c.id INNER JOIN
     products p
     ON pc.product_id = p.id 
WHERE p.nexus = 1 AND
      c.is_paused <> 1 AND
      (c.is_sub_cat = 1 OR
       c.top_cat <> 0 
      );

Note:

  • Table aliases make the query easier to write and to read.
  • Assuming that the columns are numbers, don't compare the values to strings. Try to avoid type conversion issues.
READ ALSO
How to import MySQL data to cloud SQL from deployment.yaml?

How to import MySQL data to cloud SQL from deployment.yaml?

I'm connecting my application with cloud SQL through KubernetesI want to know how to import data to my DB when I create the cloud SQL proxy pod

24
How do I create an effective mysql database design, where it stores different values for multiple users?

How do I create an effective mysql database design, where it stores different values for multiple users?

I am trying to make a website, and I need to make a system where it shows random questions, and if the user answers correctly, it exludes the question from the random printI need a table where one of the columns is the question number, and one is a boolean,...

21
Conditional Column in Mysql

Conditional Column in Mysql

I have an orders table which looks like this:

24
What is the difference between values 1 and 4096 of output_buffering in php.ini

What is the difference between values 1 and 4096 of output_buffering in php.ini

What does exactly the value of output_buffering ini variable mean in phpini? On our older server, it was set to 1

46