Portal Home > Knowledgebase > Articles Database > MySQL Joins
MySQL Joins
| Posted by riscphree, 12-16-2007, 03:10 PM |
| I'm trying to perform a join in mysql on two tables.
My tables:
That's not the complete profile_data table, but thats all that I'm concerned with.
What I want to do is pull the friendimg for all the friends belonging to user $username (in the friends table).
|
| Posted by dnswatch, 12-16-2007, 03:26 PM |
| I'll take a wild stab at this one (could be wrong);
SELECT username
FROM friends
LEFT JOIN profile_data
ON friends.username = profile_data.username
|
| Posted by Xeentech, 12-16-2007, 03:38 PM |
| That would work, but depends on how you want it to act when there is no matching profile_data row.. A LEFT JOIN would have a row for every friend even if they don't have a profile_data row, and would have a NULL value for the img. (duno if this is a problem for you though)
Try:
Without the LEFT or RIGHT options JOIN will only match when there are rows from both tables involved..
|
| Posted by masfenix, 12-16-2007, 03:40 PM |
| I work with MSSQL and .net, so i dont know whether this is supported in mySQL.
But you should add a constraint ( a foreign key ) to the username in your profile data table
|
| Posted by riscphree, 12-16-2007, 03:42 PM |
| Yeah, NULL values wouldn't work. I'll give what you guys posted a shot!
|
| Posted by dnswatch, 12-16-2007, 03:45 PM |
| Hi Xeentech,
I'm still learning the joins myself...
That plus I have only used one join (left) on my old site.
Thanks for correcting me!
|
| Posted by Xeentech, 12-16-2007, 04:08 PM |
| Not really correcting you as such.. depends on how riscphree wants it to work I guess. LEFT JOIN does tend to come up most often though.
|
| Posted by Codelphious, 12-16-2007, 04:33 PM |
| riscphree,
I'm not aware of the requirements for the information you're trying to store here, but I would strongly caution against using 'text' as the data type to store items such as KEYs as you've done here, as you'll take a pretty big performance hit. That is, assuming 'user_id' is a key.
I'd also recommend adding the 'user_id' key (again, assuming that is a key) to the friends table as such:
TABLE `friends` (
`user_id` text NOT NULL,
`friend_user_id` text NOT NULL,
`friend_status` text NOT NULL,
`friend_type` text NOT NULL
)
Notice I've replaced the 2 names 'username' and 'friendname' with user_ids. Each of these ids now maps to a designated row in the profile_data table.
|
| Posted by riscphree, 12-16-2007, 04:34 PM |
| Oh yeah, text I'm using just for simplicity sake, its not production. Same thing with keys.
|
Add to Favourites 
Print this Article
Also Read
