MySQL joins

benbox

Hey all,

I'm having some trouble understanding joins in mysql.. I've never done them before, but I can see the usefulness of them so I'm trying to work out how to use them.. but I'm not having much luck!

I basically have 3 tables:

groups (id, name)
users (id, user)
group_members (group_id, user_id)

Where basically group members is a link table between groups and users..

What I'm trying to do is input a group_id and return a list of users in that group..

So, in English.. select all from group_members where group_id = input_id, and return a list of usernames from table users where the user_ids in group_members are the id's in the users table..

I hope that isn't too much of a mouthful!

This is what I have so far, but it's completely wrong~

SELECT
group_members.user_id AS user_id, users.user AS user_name

FROM
group_members, users

LEFT JOIN groups
	ON user_groups.id = 2 

;

Any help would be grealy appreciated 😃

Lars_Berg

SELECT users.id,
            users.user AS user_name
  FROM users
 where users.id in
  (select user_groups.user_id
       from user_groups
     where user_groups.id = 2)

benbox

Sweet, thanks for that!

I've got another question though, how can I return users which don't have a record group_members table?

Lars_Berg

SELECT users.id,
            users.user AS user_name
  FROM users
 where not exists
  (select *
       from groups_members
     where group_members.group_id = 2
        and users.id = group_members.used_id)

I mixed up the table and column names in the previous query, hopefully I got it right this time.

benbox

Ok, cool.. slowly getting somewhere now 😃

What I'm actually trying to do is make an advanced search option, where my script compiles a query and runs just one at the end. So far, it's like 50% working.. but I'm just building it up slowly, making sure it works as I go along..

Below is an example of a query that's working (where a username and a group is selected.. works fine, returning just the one result for each user, unlike the case underneath it.)

SELECT tms_users.id AS user_id, tms_users.user AS user_name, tms_users.level AS user_level, tms_users.login_suspend AS user_suspend
FROM tms_users, tms_user_group_members
WHERE tms_users.user LIKE '%Admin%'
AND tms_user_group_members.group_id =2
AND tms_user_group_members.user_id = tms_users.id
ORDER BY user ASC

However.. below is the sql to search for users that aren't in a group. It works to some extent, however returns a repeated row in the query result which repeats the same number as the records in the users table itself.. for each member that doesn't have a group..

So at the moment, I have 8 users in the database, 2 don't have a group. These 2 are returned sucessfully, but 8 times each! How can I stop this from happening with the below SQL?

SELECT tms_users.id AS user_id, tms_users.user AS user_name, tms_users.level AS user_level, tms_users.login_suspend AS user_suspend
FROM tms_users, tms_user_group_members
WHERE NOT 
EXISTS (

SELECT tms_users.id
FROM tms_user_group_members
WHERE tms_user_group_members.user_id = tms_users.id
)
ORDER BY user ASC

Thank you VERY much for the help, I am learning quite a bit from this.. but still not sure where to advance to each time..

benbox

SELECT tms_users.id AS user_id, tms_users.user AS user_name, tms_users.level AS user_level, tms_users.login_suspend AS user_suspend
FROM tms_users
WHERE NOT 
EXISTS (

SELECT tms_users.id
FROM tms_user_group_members
WHERE tms_user_group_members.user_id = tms_users.id
)
ORDER BY user ASC

Oh, nevermind.. I just altered something in my code, and it worked.. couldn't quite work it out at first (what it was that was doing it).. but then I realised..

I had FROM tms_users, tms_user_group_members
instead of just FROM tms_users

Although, is this something to do with the JOIN LEFT with those 2 tables so if both were in the FROM bit.. it would still only have the same effect as the 2nd?

Cheers 😃

fasmy98

i have my query to display the data

SELECT tabletest.ID_A, kategori.KATEGORI, bahagian.bahagian, program.program, jawatan.jawatan, bil_jawatan.post, bil_jawatan.bil_06, bil_jawatan.bil_07, bil_jawatan.keterangan, jawatan.kod_gaji, jawatan.kod_skim, bil_jawatan.ID_B
FROM bil_jawatan, (((tabletest INNER JOIN bahagian ON tabletest.bahagian = bahagian.id_bhgn) INNER JOIN kategori ON tabletest.kategori = kategori.ID_KATEGORI) INNER JOIN program ON tabletest.program = program.id_program) INNER JOIN jawatan ON tabletest.kod_jawatan = jawatan.kod_jawatan
WHERE (((tabletest.ID_A)=394) AND ((bil_jawatan.ID_😎=394))

I have question, why the output took quite long time to execute?I dont really know the problem because of the query or the joining query make my output delay.