[SOLVED] LEFT JOIN Problem....

[ionik]

*** SOLVED ***

 

OK....heres my problem

 

I have the following SQL Statement

SELECT userid, username FROM users LEFT JOIN usergroups ON clan_member = 1 AND group_id RLIKE "[[:<:]]usergroup_id[[:>:]]"

 

The table stucture for each is

 

users

|- userid (int)

|- username (str)

|- usergroup_id (EX : 3,4)

 

usergroups

|- group_id (int)

|- clan_member ( either 1 or 0 , TRUE FALSE)

 

What i need to do is select all users that are currently in usergroups that have clan_members row as 1 this just selects all users in the user table...how would i do this?

 

And if you are wondering about the RLIKE "[[:<:]]usergroup_id[[:>:]] search method it works, performs a search so it will return only if that number exists in the 3,4,5 ex....so that not the problem

 

EDIT:

Marked as solved.

 

[dsmythe]

Try something like:

 

SELECT userid, username FROM users

LEFT JOIN usergroups ON users.usergroup_id = usergroups.group_id

WHERE clan_member = 1

 

-Dan-

[ionik]

no doesnt work...

[dsmythe]

What version of MySQL?

 

mysql> show create table users\G

*************************** 1. row ***************************

      Table: users

Create Table: CREATE TABLE `users` (

  `userid` int(11) DEFAULT NULL,

  `username` varchar(50) DEFAULT NULL,

  `usergroup_id` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

 

mysql> show create table usergroups\G

*************************** 1. row ***************************

      Table: usergroups

Create Table: CREATE TABLE `usergroups` (

  `group_id` int(11) DEFAULT NULL,

  `clan_member` enum('1','0') DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

 

mysql> select * from users;

+--------+----------+--------------+

| userid | username | usergroup_id |

+--------+----------+--------------+

|      1 | name1    |            1 |

|      2 | name2    |            2 |

|      3 | name3    |            3 |

|      4 | name4    |            4 |

+--------+----------+--------------+

4 rows in set (0.00 sec)

 

mysql> select * from usergroups;

+----------+-------------+

| group_id | clan_member |

+----------+-------------+

|        1 | 1          |

|        2 | 1          |

|        3 | 0          |

|        4 | 0          |

+----------+-------------+

4 rows in set (0.00 sec)

 

 

mysql> SELECT userid, username FROM users

    -> LEFT JOIN usergroups ON users.usergroup_id = usergroups.group_id

    -> WHERE clan_member = 1;

+--------+----------+

| userid | username |

+--------+----------+

|      1 | name1    |

|      2 | name2    |

+--------+----------+

2 rows in set (0.00 sec)

 

-Dan-

[ionik]

usergroup_id is not a single value it is a string of ids EX: 3,4,5 etc

[toplay]

ionik, it's never a good idea to contain more than one value in a column.

 

Anyway, you can try something like this:

 

 

SELECT u.`userid`, u.`username`

 

FROM `users` u

 

JOIN `usergroups` ug ON ug.group_id REGEXP REPLACE(u.usergroup_id, ',', '|')

 

WHERE ug.clan_member = 1

;

 

 

Good luck.

 

[ionik]

hey i found a solution to my problem...and why should i not have more than one value in a column? How else can you have a user have multiple usegroups for a user?

[toplay]

Please post that you solved the problem so forum members don't wast time trying to answer something that you've already solved.

 

You would put the information in a separate table. Your current usergroups table could be called something more appropriate like groups, group_types, or clan. Then you can use the new usergroups to really hold what users belong to what group. Example:

 

users

|- user_id

|- username

 

 

usergroups (both columns would make up the primary key of this table):

|- user_id    # ties back to users table primary key

|- group_id  # ties back to groups table primary key

 

 

groups

|- group_id

|- group_name

|- clan_member

 

 

 

It might help you to read up on database normalization before getting too far into coding:

 

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

google search:

http://www.google.com/search?hl=en&q=database+normalization

[ionik]

o ya didnt think about doing it that way thxn for the input