MySQL Query - Creating a subquery from two tables

I have two tables:

autoCoupons
CouProducts

Both have the column brand.

I want all the duplicate brands from AutoCoupons with the source shoprite.com from CouProducts.

Here is what I was trying

select * from `couProducts` where `brand` in (select 'brand' from `autoCoupons` where `service` = "Shoprite.com");

This is not working.  Can anyone help?
chrispreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
You need to use apostrophes.
select * from `couProducts` where `brand` in (select 'brand' from `autoCoupons` where `service` = 'Shoprite.com');

Open in new window

The very same query can be written using INNER JOIN
select c.* from `couProducts` c INNER JOIN `autoCoupons` a ONwhere a.`brand` = c.'brand' 
where a.`service` = 'Shoprite.com';

Open in new window

However, the question is not clear. What do you mean by:
I want all the duplicate brands
Can you elaborate? Can you show the sample data and the desired result?
0
Ray PaseurCommented:
This is not working.  Can anyone help?
How do you know it is not working?  How are you determining what error is occurring?
0
chaauCommented:
Found the error. In the sub-query you used apostrophes around 'brand' instead of a back-quote symbol `:
select * from `couProducts` where `brand` in (select 'brand' from `autoCoupons` where `service` = 'Shoprite.com');
It needs to be:
select * from `couProducts` where `brand` in (select `brand` from `autoCoupons` where `service` = 'Shoprite.com');

Open in new window

Otherwise, the subquery will return the word "brand" for every row:
brand
brand
brand
brand

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

chrispreAuthor Commented:
I tried this and it did run but got

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.3850 sec )

I know there are duplicates in both tables..... So there must be something wrong with what I am requesting.....
0
chrispreAuthor Commented:
So both the query I posted and the query from Chaau do work, but I must be missing something else, like I said I know there are duplicates.
0
chaauCommented:
Have you tried my last query.
BTW, in my original INNER JOIN query I have used your 'brand' instead of `brand` (cut-n-paste typo). The correct INNER JOIN query should be:
select c.* from `couProducts` c INNER JOIN `autoCoupons` a ON a.`brand` = c.`brand` 
where a.`service` = 'Shoprite.com';

Open in new window

0
chrispreAuthor Commented:
Ha ha ha ha ok found the problem.  The scraped data going into the database had spaces before and after brand name.  Thank you both for replying
0
chrispreAuthor Commented:
Well it is only now returning 2 results where there should be 4, oye
0
chaauCommented:
Is it possible that the "Shoprite.com" is recorded using different cases? Like "Shoprite.com" and "shoprite.com"? If it is then you need to consider this:
select * from `couProducts` 
where `brand` in 
   (select `brand` from `autoCoupons` where LOWER(`service`) = 'shoprite.com');

Open in new window

Similarly, if the brands do not match 100%, use this:
select * from `couProducts` 
where LOWER(TRIM(`brand`)) in 
   (select LOWER(TRIM(`brand`)) from `autoCoupons` where LOWER(`service`) = 'shoprite.com');

Open in new window

0
chaauCommented:
As I said before, sample data and the desired result would help to fix the problem in one go
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.