SQL set value for case depending on whether or not value exists

I am looking up addresses from one table from a user based on the user’s email from another table, then take the resulting list of addresses and check if the address has an attribute that is stored on a third table. The attribute may or may not exist on the third table. If it does, I want the sql to print out, “Attribute Exists” and if it doesn’t, print out, “Attribute Doesn’t Exist”

The attribute is linked to the address by the address id and the user is linked to the address by the user id.

Here is what I have so far:

    select b.street, case
        when c.entity_id = b.entity_id and c.attribute_id = 100 then 'Attribute Exists'
        else 'Attribute Doesn't Exist'
    end as isValue
    from customer_entity as a, //Customer Details
    customer_address_entity as b, //Address Details
    customer_address_entity_int as c //Address Attribute
    where a.email = 'customeremail@example.com'
      and a.entity_id = b.parent_id

The problem I am having with this particular setup is in table c. If I include it, the 3 addresses I am attempting to get this information from loop around the same number of times as the number of attributes I have stored in table c (in this case, 10 times as there are 10 records in table c, so I get 30 results when I only want 3).

I can’t filter the results in table c as there may or may not be a match, but I want to print a result either way. I also need table c for the select case as well. If I get rid of table c, then only the three addresses I want results for show up, but then I can’t compare the values in table c.

In short, this is what I need printed out:

street          isValue
Street 1        Attribute Exists  
Street 2        Attribute Exists
Street 3        Attribute Doesn't Exist

Answer

I think your query would be easier to understand written this way:

select distinct
  b.street, 
  case 
    when c.attribute_id = 100 then 'Attribute Exists'
    else 'Attribute Doesn''t Exist'
  end as isValue
from customer_entity as a //Customer Details
  join customer_address_entity as b //Address Details
    on a.entity_id = b.parent_id
  left join customer_address_entity_int as c //Address Attribute
    on c.entity_id = b.entity_id
where a.email = 'customeremail@example.com'

You can join b with c. If c.attribute=100 it’s because the records joined, so if not this field will be always NULL. I included a distinct because of the left join with c.

Advertisement

Answer