CODEWITHSUNDEEP
mysqlselectjoinunion
PutraKg
PutraKg

Reputation: 2246

Retrieve data from two mysql table and return single result

I have two mysql tables such as

countriesRanking table

country, clicks
-------  -----
0         657  
1         3300
2         9050

clickTable table

id, name,    clicks,  deviceid, country
-- ------    ------   --------  -------
0   player1  33       uniqueid1  0
1   player2  665      uniqueid2  2 
2   player3  88       uniqueid3  18
3   player4  5000     uniqueid4  2

I want to retrieve a click value from countriesRanking table where the country is specified and the click value from clickTable where the deviceid is specified.

Something like

SELECT clicks FROM clicksTable WHERE deviceid='$deviceid' UNION SELECT clicks FROM countriesRanking WHERE country='$country'

So if deviceid is uniqueid4 and country is 2, this will return

5000, 9050

Upvotes: 1

Views: 243

Answers (4)

Patrick Guimalan
Patrick Guimalan

Reputation: 1010

I think your query is correct but you have misspelled your Table Name clickTable. You wrote clicksTable instead.

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

SELECT ct.clicks fromClicks, cr.clicks fromCountry
FROM clicksTable ct 
LEFT JOIN countriesRanking cr ON ct.country = cr.country 
WHERE ct.deviceid='uniqueid4' AND ct.country = 2;

Check this link SQL FIDDLE DEMO

OUTPUT

| FROMCLICKS | FROMCOUNTRY |
----------------------------
|       5000 |        9050 |

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

I want to retrieve a click value from countriesRanking table where the country is specified and the click value from clickTable where the deviceid is specified.

Like this:

SELECT clicks 
FROM clicksTable 
WHERE deviceid = 'uniqueid4' 
UNION ALL
SELECT clicks 
FROM countriesRanking 
WHERE country = 2;

SQL Fiddle Demo

This will give you:

| CLICKS |
----------
|   5000 |
|   9050 |

Upvotes: 0

naveen
naveen

Reputation: 1068

try this 

SELECT clicks FROM clicksTable ct
join countriesRanking cr on cr.country=ct.country

WHERE deviceid='$deviceid' and cr.country='$country'

Upvotes: 0

Related Questions