CODEWITHSUNDEEP
mysqljoinleft-joininner-join
Steeve
Steeve

Reputation: 443

mysql query join/inner join

i have two tables in my mysql i want to extract the results based on combined query for both tables. i tried join as well as inner join but no success the structure of

tableA is

id   userid   topic

1     34       love
3     64       friendship
35    574      romance
32    253      games
95    633      football
54    26       cricket
648    63      music

tableB is

id    location     username
34      Australia    krkrff
64      india        dieiei
574     pakistan     frkfrf
253     japan        frfffrk
633     india        ifirf
26      Australia    riiri
63      Australia    frffjrr

Please note that in tableA userid and in TableB id is same .both reflect the data of same users.i want to display tableA data by filtering location column from tableB. suppose that i want to display topic of tableB and the users belongs to Australia then it should give output :love cricket music

you can see in tableB that 34,26 & 63 belongs to Australia so the output is like that. if the location is india then outpput will be

friendship and football.please tell how to write sql query.

Upvotes: 4

Views: 29008

Answers (4)

Mohammad Saberi
Mohammad Saberi

Reputation: 13176

Try this:

SELECT tableA.topic FROM tableA JOIN tableB
ON tableA.userid = tableB.id
WHERE tableB.location = 'Australia';

Upvotes: 1

Rohan Durve
Rohan Durve

Reputation: 15

Could you please elaborate on what you mean by "tableA userid and in TableB id is same", because they are visible different such that we cannot use them as keys.

As I understand it however, you'd prolly want to do a JOIN and then a SELECT only for the TOPIC.

So, your query should be:

SELECT t1.topic from table1 t1 JOIN tableB t2 on t2.id = t1.id WHERE t2.location = 'Australia'

Upvotes: 1

Gabriel Santos
Gabriel Santos

Reputation: 4974

SELECT a.topic, b.topic FROM tableA a, tableB b WHERE a.id = b.id AND b.location = 'Australia'

Upvotes: 1

Paul Bellora
Paul Bellora

Reputation: 55233

The following should select what you're describing:

select a.topic
from tableA a
join tableB b on b.id = a.userid
where b.location = 'Australia' -- or whichever location you filter on

which is equivalent to:

select a.topic
from tableA a
join tableB b on b.id = a.userid and b.location = 'Australia'

Upvotes: 8

Related Questions