Get all usernames of table 1 which has "sport" in hobbies from table 2?

Question

I have 2 tables. Table1 has a column named "username". Table2 has a column named "hobbies.. i need to select all the usernames from table 1 which have "sport" in their hobbies from table2, using SQL

Answer 1

Saean,

It's hard to tell you exactly without looking at your exact attempt. The first solution I gave you was for the schema I quickly mocked up. Going off your last message it looks like your schema is:

create table `Table1` (
`Id` int (11),
`Username` varchar (192)
);
insert into `Table1` (`Id`, `Username`) values('1','Kim');
insert into `Table1` (`Id`, `Username`) values('2','Lily');
insert into `Table1` (`Id`, `Username`) values('3','Ryan');

create table `table2` (
`Id` int (11),
`Hobbies` varchar (192)
);
insert into `table2` (`Id`, `Hobbies`) values('1','sport');
insert into `table2` (`Id`, `Hobbies`) values('2','sport');
insert into `table2` (`Id`, `Hobbies`) values('3','reading');

Which means this query would achieve what you are looking for:

SELECT t1.username
FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.Id = t2.Id
WHERE t2.Hobbies
LIKE "%sport%";

You can view/try this on SQLFiddle, which gives the results you expect: http://sqlfiddle.com/#!9/91cc1f/2/0

Things to be aware of with this:

In real life you would want to add primary keys, protection against the same username being used multiple times etc.

The LIKE will match anything containing 'sport', so if someones hobby was "Riding Public Transport" it would also be a hit. If there is only one hobby per usename you could change the WHERE claus to ( WHERE t2.Hobbies = "sport").

Answer 2

You need to be a little more specific with regards to your table setup, as this may not suit your situation:

TABLE 1 (users)
id - int 11, pk, not null, auto inc
username - varchar 32, not null

TABLE 2 (hobbies)
id - int 11, pk, not null, auto inc
userid - int 11, not null
hobby - varchar 64, not null

Query:

SELECT users.username
FROM users
LEFT JOIN hobbies
ON users.id = hobbies.user_id
WHERE hobbies.hobby
LIKE "%sport%"
GROUP BY users.username

Answer 3

Fab! 👍

Answer 4

The both queries give me the column "username" but its empty, whete it should give me 2 rows with usernames that have sport

Table1
Id - Username
1 - Kim
2 - Lily
3 - Ryan

Table2
Id - Hobbies
1 - sport
2 - sport
3 - reading

So, the query should give me
Username
Kim
Lily

But it gives
Username

Nothing under it

Answer 5

Ooh thank youuu sooo much it finally worked!!! 💙💙💙💙