1

OK, so this one has had me ripping my hair out for hours. I feel like there is something obvious I am overlooking.

I have 2 tables, service and brand

service
-------
id
brand

brand
-----
id
brandName

So service.brand can be any of these: 

Blank 
"Other" 
Integer (matches brand.id) 
String (matches brand.brandName)
String (Not Blank, Not Other, Not brand.brandName)

I'm trying to write a query that will pull up the correct brandname from the brand table, and if the value of service.brand is not in brand.id or brand.brandName, then display whatever is there.

So far I got everything working except it wouldn't pull up the record if service.brand was Not Blank, Not Other, Not in brand.id, Not in brand.brandName. (which I'm calling OtherThanOther from here on out).

Now my latest attempt pulls up mostly correct, but the OtherThanOther field gets pulled up many times, like if total records is 40, OtherThanOther is the same record almost 20 times. HELP!

My latest attempt..

select 
    s.*, b.brandName as bname 
from 
    service s, brand b 
where 
    s.brand = b.brandName 
or 
    s.brand = b.id 
or 
    s.brand = 'Other' 
or 
    s.brand = ''
or
    (       
        s.brand not in (select brandName from brand)
        and
        s.brand not in (select id from brand)
        and
        s.brand != 'Other'
        and
        s.brand != ''
    )

Sample Table Data

service
-------
1 5
2 Dell
3 SomeRandom
4 
5 Other

brand
-----

1 HP
2 Gateway
3 Dell
4 Compaq
5 Toshiba

my query results.. 

(service.id, service.brand, brand.id, brand.brandName, bname)
-------------------------------------------------------------
1 5 5 Toshiba Toshiba
2 Dell 3 Dell Dell
3 SomeRandom, brand.id, brand.brandName, brand.brandName
3 SomeRandom, brand.id, brand.brandName, brand.brandName
3 SomeRandom, brand.id, brand.brandName, brand.brandName
3 SomeRandom, brand.id, brand.brandName, brand.brandName
3 SomeRandom, brand.id, brand.brandName, brand.brandName
4 '', null, null, null
5 Other, null, null, null

I need it to just pull SomeRandom once, group by won't work because there may be multiple fields with the same value as SomeRandom. Any help is much appreciated.

Thanks!

Improve this question
3
  • join two tables and Use CASE Commented Mar 9, 2015 at 12:49
  • Can you post an answer of how that would work? Commented Mar 9, 2015 at 12:54
  • Check my answer and tell me if that will work for you. Commented Mar 9, 2015 at 13:08

4 Answers 4

Reset to default
1 
select 
    s.*, 
    CASE 
      WHEN b_id.brandName IS NOT NULL THEN b_id.brandName
      WHEN b.brandName IS NOT NULL THEN b.brandName
      ELSE s.brand 
    END as bname 
from 
    service s
LEFT JOIN brand b_id 
ON  CAST(s.brand AS UNSIGNED) = b_id.id
LEFT JOIN brand b
ON  s.brand = b.brandName
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Sorry I don't understand what 'enter code here' means in your answer.
:-) I have no idea either. @NotoriousPet0 check update. just remove that from query :-)
0

You are using an old style inner join:

from service s, brand b 
where s.brand = b.brandName or ...

More clearly written like:

from service s
inner join brand b on s.brand = b.brandName or ...

In the newer form, the problem immediately stands out. An inner join filters out unmatched rows. Use a left join instead:

from service s
left join brand b on s.brand = b.brandName or ...

Now you'll get all services, even if no matching row was found in the brand table.

Improve this answer

1 Comment

Inner and Left join pull up the same results.
0

First thing - use JOIN!

select s.*, b.brandName as bname 
from service s 
    LEFT JOIN brand b ON s.brand = b.brandName or s.brand = b.id

Then you can add your sensemaking conditions - for example WHERE NOT s.brand IS NULL

These condotions of you are leading to the result in which your table rows are multiplied with each other:

s.brand = 'Other' 
or 
    s.brand = ''
or
     (       
        s.brand not in (select brandName from brand)
        and
        s.brand not in (select id from brand)
        and
        s.brand != 'Other'
        and
        s.brand != ''
    )
Improve this answer

4 Comments

When this "ON s.brand = b.brandName or s.brand = b.id" doesn't have a match, it gives me a record for every entry in the brand table. so if 17 different records in brand table, then I get 17 dupes (well, brandname ends up as brand.brandName for the row so its 17 different values, but its' all on the same initial service table record)
i told you to then add a sensemaking condition. btw this wont give you every record from brand table. left join gives you every record from service and only matching from brand.
I did everything just like you told me. Left join wont' work then based on what you are saying because sometimes it won't be in brand table.
you're missing the basically understanding for SQL. if you want more help provide an sql fiddle with your data structure and data
0

You should JOIN 2 tables with multiple conditions. Something like this: 

SELECT s.*, b.brandName AS bname
FROM service AS s
INNER JOIN brand AS b
ON s.brand = b.brandName OR 
   s.brand = b.id OR 
   s.brand = 'Other' OR 
   s.brand = '' OR  
   (s.brand NOT IN (SELECT brandName FROM brand) AND
   s.brand NOT IN (SELECT id FROM brand) AND 
   s.brand != 'Other' AND
   s.brand != '')
Improve this answer

2 Comments

It throws any errors? Edited, try to use INNER JOIN
Try with INNER JOIN, If it not works I will make edit

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.