Locker Room Help

[fabbricino]

Funny title.

I am looking at a problem that I can solve with Oracle by using a out join. But I am a little stuck with mysql.

I have a course "ABC"

I have a total of 15 students in my class.

8 have registered for the course "ABC" and 7 haven't as of yet.

I am a little confused with the DB Logic to solve this.

Thanks for you help.

[platinum]

Can you post your table structure?

[fabbricino]

This is going to be a little more complex then suggested above, but ok.

#
# Table structure for table `ss_client_list`
#

CREATE TABLE ss_client_list (
person_id bigint(16) NOT NULL default '0',
account_id bigint(16) NOT NULL default '0',
PRIMARY KEY (person_id,account_id)
) TYPE=MyISAM;

#
# Dumping data for table `ss_client_list`
#

INSERT INTO ss_client_list VALUES (2, 1);
INSERT INTO ss_client_list VALUES (3, 1);
INSERT INTO ss_client_list VALUES (4, 1);
INSERT INTO ss_client_list VALUES (5, 1);
# --------------------------------------------------------

#
# Table structure for table `ss_permissions`
#

CREATE TABLE ss_permissions (
file_id bigint(16) NOT NULL default '0',
account_id bigint(16) NOT NULL default '0',
client_id bigint(16) NOT NULL default '0',
read char(1) NOT NULL default 'N',
over_write char(1) NOT NULL default 'N',
download char(1) NOT NULL default 'N',
upload char(1) NOT NULL default 'N',
rename char(1) NOT NULL default 'N',
delete char(1) NOT NULL default 'N',
move char(1) NOT NULL default 'N',
PRIMARY KEY (file_id,account_id,client_id)
) TYPE=MyISAM;

#
# Dumping data for table `ss_permissions`
#

INSERT INTO ss_permissions VALUES (1, 1, 2, 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
INSERT INTO ss_permissions VALUES (1, 1, 4, 'N', 'N', 'N', 'N', 'N', 'N', 'N');
INSERT INTO ss_permissions VALUES (0, 1, 2, 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
INSERT INTO ss_permissions VALUES (0, 1, 4, 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
INSERT INTO ss_permissions VALUES (1, 1, 3, 'N', 'N', 'N', 'N', 'N', 'N', 'N');
# --------------------------------------------------------

#
# Table structure for table `ss_person`
#

CREATE TABLE ss_person (
person_id bigint(16) NOT NULL auto_increment,
user_id text NOT NULL,
password tinytext NOT NULL,
first_name mediumtext NOT NULL,
last_name mediumtext NOT NULL,
email mediumtext NOT NULL,
address mediumtext NOT NULL,
city mediumtext NOT NULL,
stateorprovince mediumtext NOT NULL,
postalcode mediumtext NOT NULL,
country mediumtext NOT NULL,
companyname mediumtext NOT NULL,
title mediumtext NOT NULL,
workphone mediumtext NOT NULL,
workextension mediumtext NOT NULL,
homephone mediumtext NOT NULL,
mobilephone mediumtext NOT NULL,
faxnumber mediumtext NOT NULL,
credit_card text NOT NULL,
PRIMARY KEY (person_id)
) TYPE=MyISAM;

#
# Dumping data for table `ss_person`
#

INSERT INTO ss_person VALUES (1, 'lfabbric', '4924d867c3513ac4d196d704394030c9', 'Landon', 'Fabbricino', 'lfabbric@smartshare.ca', '', '', '', '', '', '', '', '', '', '', '', '', '');
INSERT INTO ss_person VALUES (2, 'dbelanger', '4924d867c3513ac4d196d704394030c9', 'Diane', 'Belanger', 'dvb80@hotmail.com', '', '', '', '', '', '', '', '', '', '', '', '', '');
INSERT INTO ss_person VALUES (3, 'mdias', '4924d867c3513ac4d196d704394030c9', 'Meghan', 'Dias', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
INSERT INTO ss_person VALUES (4, 'mbelanger', '4924d867c3513ac4d196d704394030c9', 'Michelle', 'Belanger', 'belcomm@shaw.ca', '', '', '', '', '', '', '', '', '', '', '', '', '');
INSERT INTO ss_person VALUES (5, 'ktaylor', 'ktaylor', 'Karen', 'Taylor', '', '', '', '', '', '', '', '', '', '', '', '', '', '');



Now the one query I use to show all users with permission would be.

SELECT a.first_name, a.last_name
FROM ss_person a, ss_permissions b, ss_client_list c
WHERE a.person_id = b.client_id
AND a.person_id = c.person_id
AND b.account_id = c.account_id
AND b.file_id = 1
AND b.account_id = 1

I am looking for all people who is in the client list, but does not have permissions.

I believe I need to do an outer join.

[r937]

SELECT a.first_name, a.last_name
FROM ss_person a
left outer
join ss_permissions b
on a.person_id = b.client_id
left outer
join ss_client_list c
on a.person_id = c.person_id
and b.account_id = c.account_id
where b.file_id = 1
and b.account_id = 1

[Zenith]

table 1: courses
- courseid
- coursename
- (more fields)

table 2: persons
- personid
- personname
- (more fields)

table 3: personscoursers
- pkid
- personid
- courseid

when person registers to a course X, you add a row to to table 3: id of the course and the person. When you want to know which persons have registered you can do it like this:

select persons.personid, personcourses.courseid from persons left outer join personscourses on persons.personid = personscoursers.personid

It'd list personid + courseid from persons that have reqistered and personid + 0 meaning not registered yet. You can always change "left outer join" to "inner join" when you only get a list of people that have registered.

-Z-

[fabbricino]

Thanks r937.

I tried

SELECT a.first_name, a.last_name
FROM ss_person a
left outer
join ss_permissions b
on a.person_id = b.client_id
left outer
join ss_client_list c
on a.person_id = c.person_id
and b.account_id = c.account_id
where b.file_id = 1
and b.account_id = 1

But it did not work. It showed all people registered to a course.
It showed ID, 2,3,4. All I want to see is ID 5.

Thanks again for all of your help

[r937]

okay, sorry, i overlooked one part of your original problem -- "looking for all people who is in the client list, but does not have permissions"

replace the WHERE clause in your query with this one:

where b.client_id is null

rudy

[fabbricino]

I must be missing something. I replaced the query, I am now returning nothing.

I am sorry to be a burdan.

[r937]

you replaced the query with what?

[fabbricino]

SELECT a.first_name, a.last_name
FROM ss_person a
left outer
join ss_permissions b
on a.person_id = b.client_id
left outer
join ss_client_list c
on a.person_id = c.person_id
and b.account_id = c.account_id
where b.client_id = null
and b.account_id = 1

[r937]

where b.client_id = null
and b.account_id = 1

b.client_id will be null whenever there is a person without a permission

in fact, all columns from b will be null in that case

therefore, if b.client_id is null, so will b.account_id, and therefore it won't be 1, so that's why you get no results

rudy

[fabbricino]

So then their is no way to determine all people who do not have permission to a file.

Well thanks for the help.

[r937]

no, there is a way

b.client_id will be null whenever there is a person without a permission

if you test for that, you get ss_person rows without a matching ss_permissions row

if b.account_id = 1 is a required condition (you never explained why you stuck that back in there), then move it into the ON clause of the join

rudy