Using mySQL

[blackrobot]

I'm a beginning Joomla coder and as you'll be able to tell, a beginning mySQL coder.

I'm coding a module that uses group jive, but that doesn't come into play much for this question -- it's more of a basic mySQL difficulty.

There are 3 tables in the database that I want to draw from: gj_category, gj_groups, and gj_users.

What I want to do is select the id from the row in gj_category where its category is equal to 'mycat'.
Then, I want to select the id's from the gj_groups table where the categories are equal to 'gj_category.id' at 'mycat'
Finally, I want to select the ids from the gj_users table where the user's group id's are equal to 'gj_groups.id' I got from the last statment

Currently, I'm using a few mySQL calls to do this in separate queries, and then looping the last one to get each user who has that specific group_id. I know that there is a way to use JOIN and AS statements in order to make it a more efficient query. I apologize if this is a tad bit confusing, thank you in advance for the help!

[macbloke]

What I want to do is select the id from the row in gj_category where its category is equal to 'mycat'.
Then, I want to select the id's from the gj_groups table where the categories are equal to 'gj_category.id' at 'mycat'
Finally, I want to select the ids from the gj_users table where the user's group id's are equal to 'gj_groups.id' I got from the last statment

Without knowing your table structure, its hard to fathom exactly but something along the lines of:

Code: Select all

SELECT c.*, g.*, u.* FROM #__gj_category AS c LEFT JOIN #__gj_groups AS g ON g.categoryid = c.id LEFT JOIN #__gj_users AS u ON u.groupid = g.id WHERE c.categoryname = '$mycat'

should see you OK. It will probably need a bit of twiddling.

StuG

[Rogue4ngel]

If you're using mysql 4.1 or greater, it also supports subselects.. which may suit the purpose as well.