How to select distinct IDs from a table that matches two similar columns?

Question

I have this table of data:

+-----+-----------+------------------------------------+---------+
| ID  | post_type | name                               | term_id |
+-----+-----------+------------------------------------+---------+
| 278 | supplier  | Heating                            |      15 |
| 282 | supplier  | Central Heating                    |      16 |
| 278 | supplier  | Biomass                            |      17 |
| 278 | supplier  | Ground Source Heat Pumps           |      18 |
| 278 | supplier  | Passive Solar                      |      19 |
| 282 | supplier  | Air Source Heat Pumps              |      21 |
| 278 | supplier  | Air Conditioning                   |      22 |
| 278 | supplier  | Boilers                            |      23 |
| 277 | supplier  | Lighting                           |      25 |
| 277 | supplier  | LED's                              |      26 |
| 282 | supplier  | Halogen                            |      28 |
| 277 | supplier  | CFL                                |      29 |
| 282 | supplier  | Sustainable Construction Materials |      31 |
| 282 | supplier  | Plaster                            |      33 |
| 282 | supplier  | Floors                             |      37 |
| 282 | supplier  | Water                              |      38 |
| 282 | supplier  | Showers & Baths                |      43 |
| 278 | supplier  | Cooling                            |      44 |
| 278 | supplier  | Refrigeration                      |      46 |
| 282 | supplier  | Passive Design                     |      47 |
| 278 | supplier  | Chillers                           |      48 |
| 282 | supplier  | Renewable Energy                   |      49 |
| 282 | supplier  | Air Source Heat Pumps              |      53 |
| 282 | supplier  | Biomass Heating                    |      55 |
| 282 | supplier  | Biofuels                           |      57 |
| 282 | supplier  | Insulation                         |      61 |
| 282 | supplier  | Wall                               |      63 |
| 282 | supplier  | Floor                              |      64 |
| 282 | supplier  | Draught Proofing                   |      65 |
| 282 | supplier  | Energy Efficiency                  |      70 |
| 282 | supplier  | Gas Boiler Management Systems      |      71 |
| 282 | supplier  | Low Energy Lighting                |      72 |
| 282 | supplier  | Voltage Control                    |      73 |
| 282 | supplier  | Smart Meters                       |      74 |
| 282 | supplier  | Electric Heating                   |      75 |
+-----+-----------+------------------------------------+---------+

And need to extract a list of IDs that apply to BOTH a specified name (wildcard string) and a term_id (integer). So for example I'd search for the IDs that have a name LIKE '%Lighting%' and a term_id = 26...which should return ID 277.

The following query works but isn't pretty:

SELECT a.ID, d.name, d.term_id
FROM cn_posts AS a
INNER JOIN cn_postmeta AS b ON a.ID = b.post_id
INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id
INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND d.name LIKE '%Lighting%'
AND a.ID IN (
    SELECT a.ID
    FROM cn_posts AS a
    INNER JOIN cn_postmeta AS b ON a.ID = b.post_id
    INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id
    INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
    WHERE d.term_id = '26'
)
GROUP BY a.ID

I've tried the following queries but they all return no results:

SELECT DISTINCT a.ID, d.name, d.term_id
FROM cn_posts AS a
LEFT JOIN cn_postmeta AS b ON a.ID = b.post_id
LEFT JOIN cn_term_relationships AS c ON a.ID = c.object_id
LEFT JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND d.name LIKE '%Lighting%'
AND d.term_id = '26'

SELECT a.ID, d.name, d.term_id, a.post_status
FROM cn_posts AS a
JOIN cn_postmeta AS b ON a.ID = b.post_id
JOIN cn_term_relationships AS c ON a.ID = c.object_id 
JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND d.name LIKE '%Lighting%'
AND d.term_id = '26'
GROUP BY a.ID

These both return posts:

SELECT a.ID, d.name, d.term_id, a.post_status
FROM cn_posts AS a
JOIN cn_postmeta AS b ON a.ID = b.post_id
JOIN cn_term_relationships AS c ON a.ID = c.object_id 
JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND d.name LIKE '%Lighting%'

SELECT a.ID, d.name, d.term_id, a.post_status
FROM cn_posts AS a
JOIN cn_postmeta AS b ON a.ID = b.post_id
JOIN cn_term_relationships AS c ON a.ID = c.object_id 
JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND d.term_id = '26'

But when you include both the name and term_id in the query it returns nothing?

Answer 1

Obviously, no single row in the table (your data sample) matches the condition of d.name LIKE '%Lighting%' AND d.term_id = '26'. There are rows matching the first part and those matching the second part, but none matching both. However, if taken as a group of rows, the ID = 277 does match the condition. Therefore, you need to introduce grouping and apply the condition, albeit slightly modified, in the HAVING clause, like this:

SELECT a.ID
FROM cn_posts AS a
INNER JOIN cn_postmeta AS b ON a.ID = b.post_id
INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id
INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
GROUP BY a.ID
HAVING COUNT(d.name LIKE '%Lighting%' OR NULL) > 0
   AND COUNT(d.term_id = '26'         OR NULL) > 0
;

Note, however, that this means you can't obtain the details like d.name or d.term alongside the IDs in the same query (but pulling other columns from a would be all right in this case). If returning the d columns in the same query is mandatory to solving your problem, you'd likely need to use the above as a derived table in an almost identical query that pulls all the data you need. The derived table would in that case be used as a means of filtering down the set of IDs to just those matching both conditions. And in that main query, the two conditions would need to be connected with OR rather than with AND, like this:

SELECT a.ID, d.name, d.term_id
FROM cn_posts AS a
INNER JOIN cn_postmeta AS b ON a.ID = b.post_id
INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id
INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND (d.name LIKE '%Lighting%' OR d.term_id = '26')
AND a.ID IN (
    /* the above query, now used just as a filter */
    SELECT a.ID
    FROM cn_posts AS a
    INNER JOIN cn_postmeta AS b ON a.ID = b.post_id
    INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id
    INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
    WHERE a.post_status = 'publish'
    GROUP BY a.ID
    HAVING COUNT(d.name LIKE '%Lighting%' OR NULL) > 0
       AND COUNT(d.term_id = '26'         OR NULL) > 0
)
;

Apparently, this would hardly be better than the query you've ended up.

Answer 2

SELECT a.ID, d.name, d.term_id 
FROM cn_posts AS a 
INNER JOIN cn_postmeta AS b ON a.ID = b.post_id 
INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id 
INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id 
INNER JOIN cn_terms AS d2 ON c.term_taxonomy_id = d2.term_id 
WHERE a.post_status = 'publish' 
AND d.name LIKE '%Lighting%' 
AND d2.term_id = '26' 

Since your conditions are on different rows, you have to join to the cn_terms twice.

Answer 3

After all the help from everybody (thankyou very much!!) I ended up just using this query which wasn't the cleanest but the only one that got the results I was after and I needed to get the job moving.

Also matches the keyword on post title and description as well as the service name.

SELECT a.ID, a.post_title, a.post_status, d.name, d.term_id
FROM cn_posts AS a
JOIN cn_postmeta AS b ON a.ID = b.post_id
JOIN cn_term_relationships AS c ON a.ID = c.object_id
JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND ( a.post_title LIKE '%Lighting%' OR a.post_content LIKE '%Lighting%' OR d.name LIKE '%Lighting%' )
AND a.ID IN (
    SELECT a.ID FROM cn_posts AS a
    INNER JOIN cn_postmeta AS b ON a.ID = b.post_id
    INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id
    INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
    WHERE d.term_id = '26' )
GROUP BY a.ID

Thanks so much for all your help. Obviously if you can figure out why the other queries didn't work or can clean up this one feel free to reply.

Dave

Answer 4

Cant this simple select will not do waht you need.

select ID, name, term_id from yourtable where name LIKE '%Lighting%' and a term_id = 26

Answer 5

SELECT a.ID, d.name, d.term_id, a.post_status
FROM wp_posts AS a
JOIN wp_postmeta AS b ON a.ID = b.post_id
JOIN wp_term_relationships AS c ON a.ID = c.object_id 
JOIN wp_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND d.name LIKE '%Halogen%'
AND d.term_id = '25'
GROUP BY a.ID

Answer 6

Did you tried this?

SELECT a.ID, d.name, d.term_id
FROM cn_posts AS a
INNER JOIN cn_postmeta AS b ON a.ID = b.post_id
INNER JOIN cn_term_relationships AS c ON a.ID = c.object_id 
INNER JOIN cn_terms AS d ON c.term_taxonomy_id = d.term_id
WHERE a.post_status = 'publish'
AND d.name LIKE '%Lighting%'
AND d.term_id = '28'
GROUP BY a.ID