Database Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Codewalkers Forums > Other Technologies > Database Help
Reload this Page

MySQL table joins

Discuss MySQL table joins in the Database Help forum on Codewalkers.
MySQL table joins General SQL help. Help with MySQL, MSSQL, Oracle, mSQL, PostgreSQL, etc.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Codewalkers Forums Sponsor:
Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here
  #1  
Old April 12th, 2003, 04:08 AM
Anonymous Anonymous is offline
Registered User
Codewalkers God 35th Plane (22000 - 22499 posts)
  
Join Date: Apr 2007
Posts: 22,309 Anonymous User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 24
MySQL table joins
I have these tables:

"user"
-------------
id int
email varchar

"group_tbl"
------------
id int
owner_id int
name varchar

"group_list"
---------------
gid int
uid int


user.id is the unique numeric id of each user in this system
user.email is the full email address of each user in this system

group_tbl.id is the unique numeric id of each group in this system
group_tbl.owner_id is the id of the user responsible for this group
group_tbl.name is the full name for each group in this system

group_list.gid is a numeric group id
group_list.uid is a numeric user id


The user table lists everybody; the "group_tbl" lists all the groups that people can belong to; and, the "group_list" table shows who belongs to what group. You can belong to any or all groups or none. The group_list table is initially empty until members are added.

I'm writing an app to allow an authorized person to manage the users and groups. In the group management part under membership I can have the admin pick the group he wants to work with. This works.

The hard part is that I want to show the admin two lists of users. The first list [the one kicking me hard] is a list of ALL users that are NOT the owner OR otherwise a member of this particular group. The second list are the current members [easy].

Owners are not [currently] listed as "members" of their groups.

The result will be a web form where you can pick non-members to add to the group and vice-versa. I just can't seem to generate the list of non-members.

I'm using a very recent version of MySQL
Reply With Quote
  #2  
Old April 13th, 2003, 05:45 AM
zombie zombie is offline
Codewalkers Intermediate (1500 - 1999 posts)
  
Join Date: Apr 2007
Location: serbia
Posts: 1,876 zombie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 3
RE: MySQL table joins
this will list users NOT in $group

php Code:
Original - php Code 
  1.  
  2. // get group id from somewhere
  3. $group=3;
  4.  
  5. mysql_query("SELECT u.*  
  6.     FROM user u
  7.     LEFT JOIN group_list l 
  8.     ON u.id = l.uid 
  9.     AND l.gid = $group
  10.     WHERE l.uid IS NULL
  11. ");
  12.  
  13. // ... 
  14.  

Reply With Quote
  #3  
Old April 14th, 2003, 01:58 PM
Anonymous Anonymous is offline
Registered User
Codewalkers God 35th Plane (22000 - 22499 posts)
  
Join Date: Apr 2007
Posts: 22,309 Anonymous User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 24
RE: MySQL table joins
Hey! this is great, thank you!

A question though. The LEFT JOIN is pulling in all the non-group members including the group owner. Is it possible to get a list of all NON-members that doesn not in clude the owner? Or, would it be easier to include the owner in the membership list?

Dean...K...
Reply With Quote
  #4  
Old April 15th, 2003, 07:29 AM
Scorgit Scorgit is offline
Codewalkers Newbie (0 - 499 posts)
  
Join Date: Apr 2007
Posts: 2 Scorgit User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
RE: MySQL table joins
Also a big thanx on behalf of me! I came across exactly the same problem with two lists a couple of weeks ago. Nobody could help me, newsgroups, forums, collegues, you name it. A big hurray for the codewalkers forum (and especially for zombie ;-) )!

Tom
Reply With Quote
  #5  
Old April 15th, 2003, 11:20 AM
zombie zombie is offline
Codewalkers Intermediate (1500 - 1999 posts)
  
Join Date: Apr 2007
Location: serbia
Posts: 1,876 zombie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 3
RE: MySQL table joins
but that part is easy.. you realy should try to do smth yourself..

anyway, just add another condition in the where clausole:

php Code:
Original - php Code 
  1.  
  2. // get group id from somewhere
  3. $group=3;
  4. // and get owner id of that group
  5. $owner=4;
  6.  
  7. mysql_query("SELECT u.*  
  8.     FROM user u
  9.     LEFT JOIN group_list l
  10.     ON u.id = l.uid
  11.     AND l.gid = $group
  12.     WHERE l.uid IS NULL
  13.     AND u.id <> $owner
  14. ");



Reply With Quote
Reply

Viewing: Codewalkers Forums > Other Technologies > Database Help > MySQL table joins

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway.