3 Table Join - search and show???

itsallgood

Hi guys,

I'm having a real problem working this out.

I have 3 tables:

users:

userID	userName
1	John
2	Jack
3	Amy
4	Paul


JoinTable:

joinID	userID	tagID
1	1	1
2	1	3
3	2	5
4	3	1
5	4	1
6	4	4


tagTable:

tagID	tagText
1	Happy
2	Sad
3	Angry
4	Bored
5	Confused

I would really like to access the data in 3 ways...

1. I would like to output all the people in the "users" table, and show their relevant tags, even if they have none:

Example:

user: John
Tags: Happy,Angry,Confused

user: Paul
Tags: Happy,Bored

user: Paul
Tags: Happy,Bored

user: Jack
Tags: none

2. I would like to be able to search based on a username. Example:

IF username = "John" then show...

user: John
Tags: Happy,Angry,Confused

3. And finally, i would like to be able to do a "like" search on the tags themselves, to get the users...

IF tagText LIKE "%hap%" then show...

user: John
Tags: Happy,Angry,Confused

user: Paul
Tags: Happy,Bored

user: Amy
Tags: Happy

Does anyone think they could help please? I just cannot understand the left/right/inner joins and im just usless when it comes to sql 😕 🙁

Thanks for any help!

Regards.

itsallgood

Oh wow, i "think" i might have worked it out myself.. Is this the way any of you would do it???

SELECT users.userName, JoinTable.tagID,tagTable.tagText 
FROM users INNER JOIN (JoinTable INNER JOIN tagTable ON JoinTable.tagID = tagTable.tagID)

ON users.userID = JoinTable.userID

I'm not sure if its the best way to do it? or if it would work with more rows of data. I also dont know how to do the other parts, like the LIKE search or anything.

I'll wait for some replies before trying to answer myself 🙂 It's just making me crazy that i cannot work it out 😃

Many thanks

Bjom

Looks ok. I wouldn't call the table jointable simply because you'll run out of names when you have many "jointables". why not userTags? and the other tables in singular. just user and tag. This way you'll recognize easily what is what: plural jointable, singular datatable.

you did not quite achieve what you wanted though: you only get results when there are tags for a user. to get the ones w/o use a LEFT JOIN like below.

To filter for anything use a WHERE clause like below (just delete the part before the AND (and the AND 😉) to filter only for tags and the part behind the AND (and the AND) to just filter for a name.

you can find out about those things easily in the mysql manual - search for SELECT and for JOIN

SELECT users.userName, JoinTable.tagID,tagTable.tagText 
FROM users LEFT JOIN (JoinTable INNER JOIN tagTable ON JoinTable.tagID = tagTable.tagID)

ON users.userID = JoinTable.userID
WHERE username = 'John'
AND tagText LIKE 'hap%';

itsallgood

Bjom - thanks so much for your reply, although i cannot quite get the result i'm looking for.

I only seem to be getting the exact "tag" that i'm doing the LIKE search on.

So for example, i get this:

...tagText LIKE 'hap%'...

user: John
Tags: Happy

user: Paul
Tags: Happy

user: Amy
Tags: Happy

When i would also like to get the other tags to go with that person. All the people below have the tag "Happy", but some also have a few others.

...tagText LIKE 'hap%'...

user: John
Tags: Happy,Angry,Confused

user: Paul
Tags: Happy,Bored

user: Amy
Tags: Happy

Does this make sense?

Thanks again for the help! 🙂

johanafm

SELECT U.userName, tagTable.tagText
FROM user AS U
	INNER JOIN (SELECT JT.userID
				FROM joinTable AS JT
				INNER JOIN tagTable AS TT ON JT.tagID = TT.tagID AND TT.tagText like 'hap%'
			) AS HappyUsers ON U.userID = HappyUsers.userID
	INNER JOIN joinTable ON U.userID = joinTable.userID
	INNER JOIN tagTable ON joinTable.tagID = tagTable.tagID

The derived table HappyUsers retrieves all users that are happy (among other things), and then you join these user ids on everything else to get their names and all their tags

itsallgood

Thanks johanafm, your code seems to be really close - but i just cannot get a multiple "LIKE" to work, so for example, i cannot run like 'hap%' AND like 'Ang%' to get everyone who is both tagged "Happy and Angry"

Thanks again for all the help 🙂

johanafm

Minor change in one line

INNER JOIN tagTable AS TT ON JT.tagID = TT.tagID AND 
	(TT.tagText like 'hap&#37;' OR TT.tagText like 'Ang%')

itsallgood

I'm trying to narrow results down - so really need an "AND"

like 'hap%' AND like 'Ang%'

The OR will also return results with just "Happy", an and would hopefully return results with only "Happy" and "Angry"

I tried changing your new line from "OR" to "AND" and it always returns no results 🙁

Thanks again johanafm! This is really helping me to understand it more 🙂

Regards,

johanafm

Wether you put the field requirements in the ON clause or the WHERE clause doesn't matter. Both of them will check that all values in their specifications match IN A SINGLE ROW of data. No single column in one row of data can be more than one value, and since you look on the same column, it's impossible that they are both 'happy' and 'angry'
.
As such, you need to follow the above approach, but using one derived table matching 'happy' and one derived table matching 'angry', then INNER JOIN them together (thereby removing people found in only one of them).

itsallgood

Sorry i dont understand 🙁

Do you mean i should use the query that you wrote, then using PHP pull in the other tags in a loop?

johanafm

Break it down into one standalone query for each case first. I.e. one for "happy" and one for "angry"

happy

SELECT JT.userID
FROM joinTable AS JT
	INNER JOIN tagTable AS TT ON JT.tagID = TT.tagID AND TT.tagText like 'hap%'

angry

SELECT JT.userID
FROM joinTable AS JT
	INNER JOIN tagTable AS TT ON JT.tagID = TT.tagID AND TT.tagText like 'ang%'

Run both and verify that they provide the wanted result. Now, you have two result sets, consisting of user ids. Each id will be in either one of the tables or both. INNER JOIN requires a match in both tables, so anything else will be discarded when joining.
Step 2: join them together

SELECT hJT.userID
FROM joinTable AS hJT
	INNER JOIN tagTable AS hTT ON hJT.tagID = hTT.tagID AND hTT.tagText like 'hap%'
	INNER JOIN (
		SELECT aJT.userID
		FROM joinTable AS aJT
			INNER JOIN tagTable AS aTT ON aJT.tagID = aTT.tagID AND
									aTT.tagText like 'ang%'
	) AS angry ON angry.userID = hJT.userID

And now you have all users that are both happy and angry. Which you piece together with the rest of the data, i.e. username, just as in the first code example I wrote for getting just "happy" people.

johanafm

In case your database supports the SET datatype, and you don't have too many tags (limited to 64 iirc, but may be platform dependent), you could denormalize your tables into one single table, which would make for easier and most likely more efficient (but do profile it) queries.

CREATE TABLE users (
userID		INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
userName	VARCHAR(30),
tags			SET('happy', 'confused', 'angry'));

Then, to retrieve both happy and angry people

SELECT userName FROM users
WHERE tags & 5

Or to get people that are only happy and angry

SELECT userName FROM users
WHERE tags = 5

However, you do lose the abililty to do "like 'hap%'".