mysql query join/inner join

Question

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.

Solution 1

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'

Solution 2

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

Solution 3

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'

Solution 4

Try this:

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

View more solutions

Comments

[Steeve]

the column name is different for userid in tableA and id in tableB but values are same . just check that tableA userid values are appearing in tableB id column so they can be used as key

[er.irfankhan11]

@Paul Bellora, How many joins can use in a single mysql query?