CODEWITHSUNDEEP
phpmysqlsql
user995683
user995683

Reputation:

Want to combine two sql queries for common results

Want to fetch location_id from location_context table if both of the below condition true for that particular location_id

    SELECT location_id 
    FROM location_context 
    WHERE relation_type = '2' 
    AND relation_table = 'locations' 
    AND relation_table_id = '$_GET[cat]' 
    AND is_active = '1'

And

    SELECT location_id 
    FROM location_context 
    WHERE relation_type = '1' 
    AND relation_table = 'locations' 
    AND relation_table_id = '$_GET[pro]' 
    AND is_active = '1'

Upvotes: 0

Views: 89

Answers (3)

Rimas
Rimas

Reputation: 6024

Use query with JOIN:

SELECT c1.location_id 
 FROM location_context c1
 JOIN location_context c2 ON c2.location_id = c1.location_id
 -- first query conditions
 WHERE c1.relation_type = '2' 
 AND c1.relation_table = 'locations' 
 AND c1.relation_table_id = '$_GET[cat]' 
 AND c1.is_active = '1'
 -- second query conditions
 AND c2.relation_type = '1' 
 AND c2.relation_table = 'locations' 
 AND c2.relation_table_id = '$_GET[pro]' 
 AND c2.is_active = '1'

Upvotes: 0

Joël Salamin
Joël Salamin

Reputation: 3576

Here is the query you're looking for:

SELECT L.location_id
FROM location_context L
WHERE L.relation_table = 'locations'
    AND L.is_active = '1'
    AND ((L.relation_type = '2' AND L.relation_table_id = '$_GET[cat]')
        OR (L.relation_type = '1' AND L.relation_table_id = '$_GET[pro]'))

Hope this will help you

Upvotes: 2

Ram Sharma
Ram Sharma

Reputation: 8809

Try something like this, using MySQL's IN() function:

SELECT location_id 
FROM location_context 
WHERE relation_type IN('1', '2') 
AND relation_table = 'locations' 
AND relation_table_id IN ($_GET['pro'], $_GET['cat']) 
AND is_active = '1'

Upvotes: 0

Related Questions