asked on

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?

chaau

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?

Ray Paseur

This is not working. Can anyone help?

How do you know it is not working? How are you determining what error is occurring?

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chrispre

ASKER

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.....

chrispre

ASKER

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.

chaau

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

chrispre

ASKER

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

chrispre

ASKER

Well it is only now returning 2 results where there should be 4, oye

chaau

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

chaau

As I said before, sample data and the desired result would help to fix the problem in one go