CODEWITHSUNDEEP
phpmysql
DMande
DMande

Reputation: 341

combine two different queries into one

I have the following query:

    SELECT DISTINCT id, title
    FROM      
    ((SELECT 
        DISTINCT offers.id AS id, offers.title AS title

    FROM offers

    INNER JOIN categories
    ON offers.category=categories.title

    WHERE categories.title="Fashion clothes"

    GROUP BY offers.id
    ORDER BY offers.id)
    UNION ALL
    (SELECT 
        DISTINCT offers.id AS id, offers.title AS title

    FROM offers

    INNER JOIN cities
    ON offers.city=cities.title

    WHERE cities.title="Australia"

    GROUP BY offers.id
    ORDER BY offers.id)) as subquery

I would like to fetch from the table offers the rows that have category=Fashion clothes and city=Australia but when I use Union it returns all the rows . I don't know how to make it work. If anyone can help i would appreciate it.

Upvotes: 1

Views: 107

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you want both, then you can use aggregation and a having clause:

SELECT o.id, o.title
FROM offers o
WHERE o.category = 'Fashion clothes' AND o.city = 'Australia'
GROUP BY o.id, o.title
HAVING COUNT(*) = 2
ORDER BY o.id;

If you can have duplicates in the offers table, then you need COUNT(DISTINCT o.category) in the HAVING clause instead of COUNT(*).

Upvotes: 0

evsheino
evsheino

Reputation: 2277

You don't need a union for this. Just join all the tables and have both conditions in you where clause:

SELECT 
DISTINCT offers.id AS id, offers.title AS title

FROM offers
INNER JOIN categories
ON offers.category=categories.title
INNER JOIN cities
ON offers.city=cities.title

WHERE categories.title="Fashion clothes" AND cities.title="Australia"
ORDER BY offers.id

As noted by RubahMalam you don't even need the joins as you are joining the tables by title, so the query can be simplified to:

SELECT 
DISTINCT offers.id AS id, offers.title AS title

FROM offers

WHERE offers.category="Fashion clothes" AND offers.city="Australia"
ORDER BY offers.id

However, it would probably be best to have separate unique id's in all your tables and use those to join them in your queries, but that's another story.

Upvotes: 2

Hotdin Gurning
Hotdin Gurning

Reputation: 1819

You just need :

SELECT id,title
FROM offers
WHERE category = "Fashion clothes" OR city = "Australia"
GROUP BY id,title
ORDER BY offers.id

You don't even need INNER JOIN for this. And as patrickh003 said, you don't even need GROUP BY if id is unique column.

Upvotes: 1

Related Questions