Duplicate rows displayed in a query using a join operation
I am writing this request
SELECT aw_offers_v3.id,aw_offers_v3.name
FROM aw_offers_v3,aw_countries_v3,aw_categories_v3
WHERE aw_offers_v3.id = aw_countries_v3.id
AND aw_offers_v3.id = aw_categories_v3.id
AND ( aw_offers_v3.api_key = 'a2a9e134bcb7935ecb6a1d989110650c016c91f17835d733aebd113255b4abb1' )
AND ( aw_offers_v3.network_id = 'http://api.hasoffers.com/v3/Affiliate_Offer.json' )
AND ( aw_countries_v3.country = 'Albania' OR aw_countries_v3.country = 'Armenia' )
AND ( aw_categories_v3.category = 'Casual' )
I am getting this result
|--------------------------------------|
| id | name |
|--------------------------------------|
| 11105 | APP 3 |
|----------------|---------------------|
| 11107 | APP 4 |
|----------------|---------------------|
| 11105 | APP 3 |
|--------------------------------------|
| 11107 | APP 4 |
|----------------|---------------------|
I don't know why I am getting this duplicate result. There is no duplicate data in the actual tables.
Here's my aw_offers_v3
|----------------------------------------------------------|
| id | name | Desc |
|----------------------------------------------------------|
| 391 | APP 1 | A |
|----------------|---------------------|-------------------|
| 234 | APP 2 | B |
|----------------|---------------------|-------------------|
| 11105 | APP 3 | bb |
|----------------------------------------------------------|
| 11107 | APP 4 | thut |
|----------------|---------------------|-------------------|
Here's my aw_countries_v3
|----------------------------------------------------------|
| internal_id | country | id |
|----------------------------------------------------------|
| 1 | Albania | 11105 |
|----------------|---------------------|-------------------|
| 2 | Armenia | 11105 |
|----------------|---------------------|-------------------|
| 3 | Argentina | 11105 |
|----------------------------------------------------------|
| 4 | Albania | 11107 |
|----------------|---------------------|-------------------|
| 5 | Armenia | 11107 |
|----------------|---------------------|-------------------|
| 6 | Andola | 11107 |
|----------------------------------------------------------|
Here's my aw_categories_v3
|----------------------------------------------------------|
| internal_id | category | id |
|----------------------------------------------------------|
| 1 | Android | 11105 |
|----------------|---------------------|-------------------|
| 2 | Casual | 11105 |
|----------------|---------------------|-------------------|
| 3 | Sports | 11105 |
|----------------------------------------------------------|
| 4 | Gamimg | 11107 |
|----------------|---------------------|-------------------|
| 5 | Casual | 11107 |
|----------------|---------------------|-------------------|
| 6 | Sports | 11107 |
|----------------------------------------------------------|
+3
source to share
1 answer
Try the following:
SELECT DISTINCT a.id, a.name
FROM aw_offers_v3 a
INNER JOIN aw_countries_v3 b ON a.id = b.id
INNER JOIN aw_categories_v3 c ON a.id = c.id
WHERE a.api_key = 'a2a9e134bcb7935ecb6a1d989110650c016c91f17835d733aebd113255b4abb1' AND
a.network_id = 'http://api.hasoffers.com/v3/Affiliate_Offer.json' AND
b.country IN ('Albania', 'Armenia') AND c.category = 'Casual'
+2
source to share