CODEWITHSUNDEEP
mysqlsql
Ibu
Ibu

Reputation: 43810

Return Yes if only one record is found

I have a table that has this structure:

table
id      | site_id
-------------------
240     | 1
240     | 2
240     | 3
320     | 1
320     | 2
421     | 1
520     | 2
-------------------

300k records

Now i am trying to write a query to return a yes or a no for each record (id).

For example if the records with id 240 only have a site_id 1 then return 'Yes', if it has 2, 3 and so on return 'No'

I am not sure how to approach it but here is a result sample:

result_table
.-----------------------.
| id        | result    |
|-----------------------|
| 240       |   No      |  -- has a site_id 1, 2 and 3
| 320       |   No      |  -- has a site_id 1 and 2
| 421       |   Yes     |  -- has a site_id 1 only
| 520       |   No      |  -- has a site_id  2
'-----------------------'

Here is the query i have so far, but it seems to be incorrect

SELECT CASE WHEN count(id) > 1 THEN 'N' ELSE 'Y' END as Result 
FROM table sm 
WHERE sm.site_id IN (1) 
AND sm.site_id NOT IN (2,3,4,5,6,7,8,9) 
AND id = 240

UPDATE

SO Here is my full query, i added the answer from @gordon

SELECT 
    m.merchant_name,
    m.merchant_id,
    ut.realname,
    a.affiliate_name,
    (select (case when count(site_id) = 1 then 'Yes' else 'No' end) as result
    from site_merchant sm
    WHERE sm.merchant_id = m.merchant_id
    group by merchant_id) as isTjoos,   -- y or no
    (select (case when count(site_id) = 2 then 'Yes' else 'No' end) as result
    from site_merchant sm
    WHERE sm.merchant_id = m.merchant_id
    group by merchant_id) as isUCOnly
--  isdlkonly   -- y or no
FROM merchant m
LEFT JOIN merchant_editor_assignment mea ON mea.merchant_id = m.merchant_id
LEFT JOIN user_table ut ON ut.user_id = mea.user_id
LEFT JOIN affiliate a ON a.affiliate_id = m.affiliate_id_default

Upvotes: 0

Views: 1468

Answers (6)

Ibu
Ibu

Reputation: 43810

Here is the solution i found. I used @Gordons query to get started and what was missing was the site_id, and the group by was not needed:

SELECT 
    m.merchant_name,
    m.merchant_id,
    ut.realname,
    a.affiliate_name,
    (select (case when count(*)>0  then 'No' else 'Yes' end) as result
    from site_merchant sm
    WHERE sm.merchant_id = m.merchant_id
    AND site_id != 1) as isTjoos,
    (select (case when count(*)> 0 then 'No' else 'Yes' end) as result
    from site_merchant sm
    WHERE sm.merchant_id = m.merchant_id
    AND site_id != 2) as isUCOnly,
    (select (case when count(*)> 0 then 'No' else 'Yes' end) as result
    from site_merchant sm
    WHERE sm.merchant_id = m.merchant_id
    AND site_id != 3) as isDLKonly
FROM merchant m
LEFT JOIN merchant_editor_assignment mea ON mea.merchant_id = m.merchant_id
LEFT JOIN user_table ut ON ut.user_id = mea.user_id
LEFT JOIN affiliate a ON a.affiliate_id = m.affiliate_id_default

Thank you for the help.

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33476

SELECT ID, CASE WHEN EXTRA > 1 THEN 'No' ELSE 'Yes' END AS Result
FROM
(SELECT ID, Sum(site_id) AS Extra
 from myTable
 GROUP BY ID
) AS Test

EDIT: I suppose this should work in MySQL. I haven't worked on it though.
The idea is to SUM up the site_id. For records with only site_id = 1, the sum will be 1.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I interpreted this question as you want ids that have only one value for site_id. I took the example in the question to be an example, with site_id = 1. To do this:

You want to use count(distinct):

select id, (case when count(distinct site_id) = 1 then 'Yes' else 'No' end) as result
from site_merchant sm
group by id

A slightly more efficient version is to use min() and max(), assuming that the site_id is never NULL:

select id, (case when min(site_id) = max(site_id) then 'Yes' else 'No' end) as result
from site_merchant sm
group by id

This is because min and max generally require a bit less processing than count(distinct).

If you want to check that the site_id is "1" and never anything else, then add the condition and min(site_id) = 1 to the when clause.

If you want to check that the site_id is 1 and there is exactly one row, then you can do:

select id, (case when count(site_id) = 1 and min(site_id) = 1 then 'Yes' else 'No' end) as result
from site_merchant sm
group by id

And, if you want to check that there is exactly one row:

select id, (case when count(site_id) = 1 then 'Yes' else 'No' end) as result
from site_merchant sm
group by id

Upvotes: 3

fthiella
fthiella

Reputation: 49049

SELECT
  it,
  CASE WHEN COUNT(CASE WHEN site_id THEN 1 END)=1
            AND COUNT(CASE WHEN site_id!=1 THEN 1 END)=0 THEN 'Yes'
       ELSE 'No'
  END
FROM sm
GROUP BY it

Please see fiddle here.

Upvotes: 1

JudgeProphet
JudgeProphet

Reputation: 1729

I would use a Having Count Statement. Something like that:

SELECT site_id
FROM site_merchant
HAVING (count(merchant_id) > 1)
GROUP BY site_id;

Upvotes: 0

Matt Ball
Matt Ball

Reputation: 359776

Your query seems overcomplicated. Just to start, why the IN(1) and NOT IN(2,3...9)? And why limit to a single ID (AND id = 240) when your "result sample" clearly doesn't want that? It does not make any sense. How about this?

SELECT CASE WHEN count(merchant_id) > 1 THEN 'N' ELSE 'Y' END as isTjoos 
FROM site_merchant
GROUP BY site_id;

Upvotes: 0

Related Questions