filter out John

[Sjoerd Mulder]

Hi all,

I have a database storing persons and the groups they belong to. I therefore
created three tables, a group table, person table and join table.

I have for example the next tables:

GROUP(id,gname)
1 volunteer
2 worker
3 sick

PERSON(id,pname)
1 John
2 Pete
3 Steve

JOIN_PERS_GROUP(id, pname, gname)
1 John, sick
2 John, worker
3 Pete, volunteer
4 Steve, sick

Query:
SELECT name FROM person
LEFT JOIN join_pers_group USING pname
LEFT JOIN group USING gname
WHERE gname!='sick'

Returns
2 John, worker
3 Pete, volunteer

while John is also sick. How can I filter out John?

Thanks in advance,
Sjoerd

[J.O. Aho]

Sjoerd Mulder wrote:

Hi all,

I have a database storing persons and the groups they belong to. I therefore
created three tables, a group table, person table and join table.

I have for example the next tables:

GROUP(id,gname)
1 volunteer
2 worker
3 sick

PERSON(id,pname)
1 John
2 Pete
3 Steve

JOIN_PERS_GROUP(id, pname, gname)
1 John, sick
2 John, worker
3 Pete, volunteer
4 Steve, sick

Query:
SELECT name FROM person
LEFT JOIN join_pers_group USING pname
LEFT JOIN group USING gname
WHERE gname!='sick'

Returns
2 John, worker
3 Pete, volunteer

while John is also sick. How can I filter out John?

Thanks in advance,
Sjoerd

But John is worker too, so you get the working John on your list. You have
either decide if John is sick or not, he can't be both and as long as he
appears on both groups, he will appear on both "worker" and "sick" querries.

I think your database is built up wrongly and I would have suggested:

group(id,gname)
1 volunteer
2 worker
3 UNKNOWN

person(id,pname)
1 John
2 Pete
3 Steve

ability(id,aname)
1 working
2 off duty
3 sick

join_tables(pid, gid, aid) /* the combination of pid and gid is the "primary
key" */
1 2 3
2 1 1
3 3 3

SELECT person.pname FROM join_tables
LEFT RIGHT person ON join_tables.pid=person.id
LEFT JOIN ability ON join_tables.aid=ability.id
WHERE ability.aname !='sick'

(think it should work and give the following result)

Peter

//Aho

[J.O. Aho]

Sjoerd Mulder wrote:

Hi all,

I have a database storing persons and the groups they belong to. I therefore
created three tables, a group table, person table and join table.

I have for example the next tables:

GROUP(id,gname)
1 volunteer
2 worker
3 sick

PERSON(id,pname)
1 John
2 Pete
3 Steve

JOIN_PERS_GROUP(id, pname, gname)
1 John, sick
2 John, worker
3 Pete, volunteer
4 Steve, sick

Query:
SELECT name FROM person
LEFT JOIN join_pers_group USING pname
LEFT JOIN group USING gname
WHERE gname!='sick'

Returns
2 John, worker
3 Pete, volunteer

while John is also sick. How can I filter out John?

Thanks in advance,
Sjoerd

But John is worker too, so you get the working John on your list. You have
either decide if John is sick or not, he can't be both and as long as he
appears on both groups, he will appear on both "worker" and "sick" querries.

I think your database is built up wrongly and I would have suggested:

group(id,gname)
1 volunteer
2 worker
3 UNKNOWN

person(id,pname)
1 John
2 Pete
3 Steve

ability(id,aname)
1 working
2 off duty
3 sick

join_tables(pid, gid, aid) /* the combination of pid and gid is the "primary
key" */
1 2 3
2 1 1
3 3 3

SELECT person.pname FROM join_tables
LEFT RIGHT person ON join_tables.pid=person.id
LEFT JOIN ability ON join_tables.aid=ability.id
WHERE ability.aname !='sick'

(think it should work and give the following result)

Peter

//Aho

[Sjoerd Mulder]

Ok, this database was an example. The point is that I have members of an
organisation, active in all sort of groups. So one member can be member of
many many groups and another member is just member of the organisation, but
not of any group.

So maybe my example was not so well chosen, but I have to stick with this
database setup.

Sjoerd

"J.O. Aho" <us**@example.net> wrote in message
news:bq*************@ID-130698.news.uni-berlin.de...

Sjoerd Mulder wrote:

Hi all,

I have a database storing persons and the groups they belong to. I therefore created three tables, a group table, person table and join table.

I have for example the next tables:

GROUP(id,gname)
1 volunteer
2 worker
3 sick

PERSON(id,pname)
1 John
2 Pete
3 Steve

JOIN_PERS_GROUP(id, pname, gname)
1 John, sick
2 John, worker
3 Pete, volunteer
4 Steve, sick

Query:
SELECT name FROM person
LEFT JOIN join_pers_group USING pname
LEFT JOIN group USING gname
WHERE gname!='sick'

Returns
2 John, worker
3 Pete, volunteer

while John is also sick. How can I filter out John?

Thanks in advance,
Sjoerd
But John is worker too, so you get the working John on your list. You have
either decide if John is sick or not, he can't be both and as long as he
appears on both groups, he will appear on both "worker" and "sick"

querries.
I think your database is built up wrongly and I would have suggested:

group(id,gname)
1 volunteer
2 worker
3 UNKNOWN

person(id,pname)
1 John
2 Pete
3 Steve

ability(id,aname)
1 working
2 off duty
3 sick

join_tables(pid, gid, aid) /* the combination of pid and gid is the "primary key" */
1 2 3
2 1 1
3 3 3

SELECT person.pname FROM join_tables
LEFT RIGHT person ON join_tables.pid=person.id
LEFT JOIN ability ON join_tables.aid=ability.id
WHERE ability.aname !='sick'

(think it should work and give the following result)

Peter

//Aho

[Sjoerd Mulder]

Ok, this database was an example. The point is that I have members of an
organisation, active in all sort of groups. So one member can be member of
many many groups and another member is just member of the organisation, but
not of any group.

So maybe my example was not so well chosen, but I have to stick with this
database setup.

Sjoerd

"J.O. Aho" <us**@example.net> wrote in message
news:bq*************@ID-130698.news.uni-berlin.de...

Sjoerd Mulder wrote:

Hi all,

I have a database storing persons and the groups they belong to. I therefore created three tables, a group table, person table and join table.

I have for example the next tables:

GROUP(id,gname)
1 volunteer
2 worker
3 sick

PERSON(id,pname)
1 John
2 Pete
3 Steve

JOIN_PERS_GROUP(id, pname, gname)
1 John, sick
2 John, worker
3 Pete, volunteer
4 Steve, sick

Query:
SELECT name FROM person
LEFT JOIN join_pers_group USING pname
LEFT JOIN group USING gname
WHERE gname!='sick'

Returns
2 John, worker
3 Pete, volunteer

while John is also sick. How can I filter out John?

Thanks in advance,
Sjoerd
But John is worker too, so you get the working John on your list. You have
either decide if John is sick or not, he can't be both and as long as he
appears on both groups, he will appear on both "worker" and "sick"

querries.
I think your database is built up wrongly and I would have suggested:

group(id,gname)
1 volunteer
2 worker
3 UNKNOWN

person(id,pname)
1 John
2 Pete
3 Steve

ability(id,aname)
1 working
2 off duty
3 sick

join_tables(pid, gid, aid) /* the combination of pid and gid is the "primary key" */
1 2 3
2 1 1
3 3 3

SELECT person.pname FROM join_tables
LEFT RIGHT person ON join_tables.pid=person.id
LEFT JOIN ability ON join_tables.aid=ability.id
WHERE ability.aname !='sick'

(think it should work and give the following result)

Peter

//Aho

[J.O. Aho]

Sjoerd Mulder wrote:

Ok, this database was an example. The point is that I have members of an
organisation, active in all sort of groups. So one member can be member of
many many groups and another member is just member of the organisation, but
not of any group.

So maybe my example was not so well chosen, but I have to stick with this
database setup.

Then you would need a organisation table, where you include all the
organisations a person can be member of (sick isn't a organisation, it's a status)

Then you need a join-table for organisation-person where the
organisation-id-number and person-id-number together forms a primary key (a
person shouldn't be able to belong to the same organisation twice, s/he is a
member or not).

You have a table for the different statuses a person can be in (sick, working,
on vaction, off duty, dead), this one needs it's own join-table where the
person-id-number and status-id-number together is the primary key for the table.

Then you have the possibility that a person is volenteer/worker and in theory
a person can be a worker in one of your organisations while a volenteer in
another, so I would save the "membershiptype" id-number together with the the
two other keys in organisation-person join table, this shouldn't be part of a
primary key, as I do not see the possibility to be both volenteer and worker
for a organisation.

So if you have similare problems as in your example, you really do have flaws
in your database design, I think I would sit down with a paper and pen and
think over what things are needed in the tables.

Start first with one table where you have all the data that is needed to be
saved about a person, things that a person can have multiple status of as
beloning to an organisation, you have to divide into two new tables, a join
table whcih has the primary keys from the two other tables as it's own primary
key and then the "description" table with a primary key and description/name.

Everything that can have one isntance, but from a limited amount of options
(like working/off duty) you can create a "description" table and the primary
key from that table is saved in the main table (person table).

When you have broken down the table into a hord of tables, then start to look
which things you need to know often, you may get quite complicated table
joins, in those cases it can be smarter to have some data accessble in another
table too, so the number of joins in a query aren't too many.

For a lot better tips on database design, I suggest you take a look at the
Oracle online documentation and their downloadable documentation, they are
quite long to read, but usefull.
//Aho

[J.O. Aho]

Sjoerd Mulder wrote:

Ok, this database was an example. The point is that I have members of an
organisation, active in all sort of groups. So one member can be member of
many many groups and another member is just member of the organisation, but
not of any group.

So maybe my example was not so well chosen, but I have to stick with this
database setup.

Then you would need a organisation table, where you include all the
organisations a person can be member of (sick isn't a organisation, it's a status)

Then you need a join-table for organisation-person where the
organisation-id-number and person-id-number together forms a primary key (a
person shouldn't be able to belong to the same organisation twice, s/he is a
member or not).

You have a table for the different statuses a person can be in (sick, working,
on vaction, off duty, dead), this one needs it's own join-table where the
person-id-number and status-id-number together is the primary key for the table.

Then you have the possibility that a person is volenteer/worker and in theory
a person can be a worker in one of your organisations while a volenteer in
another, so I would save the "membershiptype" id-number together with the the
two other keys in organisation-person join table, this shouldn't be part of a
primary key, as I do not see the possibility to be both volenteer and worker
for a organisation.

So if you have similare problems as in your example, you really do have flaws
in your database design, I think I would sit down with a paper and pen and
think over what things are needed in the tables.

Start first with one table where you have all the data that is needed to be
saved about a person, things that a person can have multiple status of as
beloning to an organisation, you have to divide into two new tables, a join
table whcih has the primary keys from the two other tables as it's own primary
key and then the "description" table with a primary key and description/name.

Everything that can have one isntance, but from a limited amount of options
(like working/off duty) you can create a "description" table and the primary
key from that table is saved in the main table (person table).

When you have broken down the table into a hord of tables, then start to look
which things you need to know often, you may get quite complicated table
joins, in those cases it can be smarter to have some data accessble in another
table too, so the number of joins in a query aren't too many.

For a lot better tips on database design, I suggest you take a look at the
Oracle online documentation and their downloadable documentation, they are
quite long to read, but usefull.
//Aho

[Sjoerd Mulder]

Seems that my example takes me out...
Forget the whole sick/volunteer/ stuff. It's just this: Members, Groups and
the relation between them. Nobody gets sick or whatever, everybody's happy
and wants to join zero or more groups. And I want to select everybody except
the members of group A.

That's all. But I appreciate your effort in helping me out, and more I will
appreciate it when you continue doing so :)

Sjoerd
"J.O. Aho" <us**@example.net> wrote in message
news:bq*************@ID-130698.news.uni-berlin.de...

Sjoerd Mulder wrote:

Ok, this database was an example. The point is that I have members of an
organisation, active in all sort of groups. So one member can be member of many many groups and another member is just member of the organisation, but not of any group.

So maybe my example was not so well chosen, but I have to stick with this database setup.
Then you would need a organisation table, where you include all the
organisations a person can be member of (sick isn't a organisation, it's a

status)
Then you need a join-table for organisation-person where the
organisation-id-number and person-id-number together forms a primary key (a person shouldn't be able to belong to the same organisation twice, s/he is a member or not).

You have a table for the different statuses a person can be in (sick, working, on vaction, off duty, dead), this one needs it's own join-table where the
person-id-number and status-id-number together is the primary key for the table.
Then you have the possibility that a person is volenteer/worker and in theory a person can be a worker in one of your organisations while a volenteer in
another, so I would save the "membershiptype" id-number together with the the two other keys in organisation-person join table, this shouldn't be part of a primary key, as I do not see the possibility to be both volenteer and worker for a organisation.

So if you have similare problems as in your example, you really do have flaws in your database design, I think I would sit down with a paper and pen and
think over what things are needed in the tables.

Start first with one table where you have all the data that is needed to be saved about a person, things that a person can have multiple status of as
beloning to an organisation, you have to divide into two new tables, a join table whcih has the primary keys from the two other tables as it's own primary key and then the "description" table with a primary key and description/name.
Everything that can have one isntance, but from a limited amount of options (like working/off duty) you can create a "description" table and the primary key from that table is saved in the main table (person table).

When you have broken down the table into a hord of tables, then start to look which things you need to know often, you may get quite complicated table
joins, in those cases it can be smarter to have some data accessble in another table too, so the number of joins in a query aren't too many.

For a lot better tips on database design, I suggest you take a look at the
Oracle online documentation and their downloadable documentation, they are
quite long to read, but usefull.
//Aho

[Sjoerd Mulder]

Seems that my example takes me out...
Forget the whole sick/volunteer/ stuff. It's just this: Members, Groups and
the relation between them. Nobody gets sick or whatever, everybody's happy
and wants to join zero or more groups. And I want to select everybody except
the members of group A.

That's all. But I appreciate your effort in helping me out, and more I will
appreciate it when you continue doing so :)

Sjoerd
"J.O. Aho" <us**@example.net> wrote in message
news:bq*************@ID-130698.news.uni-berlin.de...

Sjoerd Mulder wrote:

Ok, this database was an example. The point is that I have members of an
organisation, active in all sort of groups. So one member can be member of many many groups and another member is just member of the organisation, but not of any group.

So maybe my example was not so well chosen, but I have to stick with this database setup.
Then you would need a organisation table, where you include all the
organisations a person can be member of (sick isn't a organisation, it's a

status)
Then you need a join-table for organisation-person where the
organisation-id-number and person-id-number together forms a primary key (a person shouldn't be able to belong to the same organisation twice, s/he is a member or not).

You have a table for the different statuses a person can be in (sick, working, on vaction, off duty, dead), this one needs it's own join-table where the
person-id-number and status-id-number together is the primary key for the table.
Then you have the possibility that a person is volenteer/worker and in theory a person can be a worker in one of your organisations while a volenteer in
another, so I would save the "membershiptype" id-number together with the the two other keys in organisation-person join table, this shouldn't be part of a primary key, as I do not see the possibility to be both volenteer and worker for a organisation.

So if you have similare problems as in your example, you really do have flaws in your database design, I think I would sit down with a paper and pen and
think over what things are needed in the tables.

Start first with one table where you have all the data that is needed to be saved about a person, things that a person can have multiple status of as
beloning to an organisation, you have to divide into two new tables, a join table whcih has the primary keys from the two other tables as it's own primary key and then the "description" table with a primary key and description/name.
Everything that can have one isntance, but from a limited amount of options (like working/off duty) you can create a "description" table and the primary key from that table is saved in the main table (person table).

When you have broken down the table into a hord of tables, then start to look which things you need to know often, you may get quite complicated table
joins, in those cases it can be smarter to have some data accessble in another table too, so the number of joins in a query aren't too many.

For a lot better tips on database design, I suggest you take a look at the
Oracle online documentation and their downloadable documentation, they are
quite long to read, but usefull.
//Aho

[J.O. Aho]

Sjoerd Mulder wrote:

Seems that my example takes me out...
Forget the whole sick/volunteer/ stuff. It's just this: Members, Groups and
the relation between them. Nobody gets sick or whatever, everybody's happy
and wants to join zero or more groups. And I want to select everybody except
the members of group A.

In that case, I suggest you give an example how your databse really looks like.
//Aho

[J.O. Aho]

Sjoerd Mulder wrote:

Seems that my example takes me out...
Forget the whole sick/volunteer/ stuff. It's just this: Members, Groups and
the relation between them. Nobody gets sick or whatever, everybody's happy
and wants to join zero or more groups. And I want to select everybody except
the members of group A.

In that case, I suggest you give an example how your databse really looks like.
//Aho

[Sjoerd Mulder]

Members with (id, name, adress, etc)
Groups (id, name, description)
jointable (memberid, groupid)

That's it.

So a member can be part of several groups. Now I want to know how I can
select EVERY MEMBER EXCEPT the members in a specified group. I now have two
queries: select all members, and select all members who are member of group
x. I compare the two results in PHP. I'm wondering how I can do this in
MySQL without upgrading to 4.1 (where you can use subqueries, nested select
statements etc)

Sjoerd

"J.O. Aho" <us**@example.net> wrote in message
news:bq*************@ID-130698.news.uni-berlin.de...

Sjoerd Mulder wrote:

Seems that my example takes me out...
Forget the whole sick/volunteer/ stuff. It's just this: Members, Groups and the relation between them. Nobody gets sick or whatever, everybody's happy and wants to join zero or more groups. And I want to select everybody except the members of group A.
In that case, I suggest you give an example how your databse really looks

like.

//Aho

[Sjoerd Mulder]

Members with (id, name, adress, etc)
Groups (id, name, description)
jointable (memberid, groupid)

That's it.

So a member can be part of several groups. Now I want to know how I can
select EVERY MEMBER EXCEPT the members in a specified group. I now have two
queries: select all members, and select all members who are member of group
x. I compare the two results in PHP. I'm wondering how I can do this in
MySQL without upgrading to 4.1 (where you can use subqueries, nested select
statements etc)

Sjoerd

"J.O. Aho" <us**@example.net> wrote in message
news:bq*************@ID-130698.news.uni-berlin.de...

Sjoerd Mulder wrote:

Seems that my example takes me out...
Forget the whole sick/volunteer/ stuff. It's just this: Members, Groups and the relation between them. Nobody gets sick or whatever, everybody's happy and wants to join zero or more groups. And I want to select everybody except the members of group A.
In that case, I suggest you give an example how your databse really looks

like.

//Aho

[Kevin Thorpe]

Sjoerd Mulder wrote:

Members with (id, name, adress, etc)
Groups (id, name, description)
jointable (memberid, groupid)

That's it.

So a member can be part of several groups. Now I want to know how I can
select EVERY MEMBER EXCEPT the members in a specified group. I now have two
queries: select all members, and select all members who are member of group
x. I compare the two results in PHP. I'm wondering how I can do this in
MySQL without upgrading to 4.1 (where you can use subqueries, nested select
statements etc)

If you really must then you will have to do two joins:

select members.*,g1.name,g1.description,g2.name
from members
left join groups as g1
where g1.memberid = members.id
left join groups as g2
on g2.memberid = members.id
and g2.groupid = 'whatever group'

Then discard any rows with g2.name set. It's still dirty, but that's a
function of your database structure.

[Kevin Thorpe]

Sjoerd Mulder wrote:

Members with (id, name, adress, etc)
Groups (id, name, description)
jointable (memberid, groupid)

That's it.

So a member can be part of several groups. Now I want to know how I can
select EVERY MEMBER EXCEPT the members in a specified group. I now have two
queries: select all members, and select all members who are member of group
x. I compare the two results in PHP. I'm wondering how I can do this in
MySQL without upgrading to 4.1 (where you can use subqueries, nested select
statements etc)

If you really must then you will have to do two joins:

select members.*,g1.name,g1.description,g2.name
from members
left join groups as g1
where g1.memberid = members.id
left join groups as g2
on g2.memberid = members.id
and g2.groupid = 'whatever group'

Then discard any rows with g2.name set. It's still dirty, but that's a
function of your database structure.

[Sjoerd Mulder]

Dirty a function of my database structure? Do you mean that there is a
better setup for this kind of information? What structure would you suggest?
Sjoerd
"Kevin Thorpe" <ke***@pricetrak.com> wrote in message
news:3f***********************@news.easynet.co.uk. ..

Sjoerd Mulder wrote:

Members with (id, name, adress, etc)
Groups (id, name, description)
jointable (memberid, groupid)

That's it.

So a member can be part of several groups. Now I want to know how I can
select EVERY MEMBER EXCEPT the members in a specified group. I now have two queries: select all members, and select all members who are member of group x. I compare the two results in PHP. I'm wondering how I can do this in
MySQL without upgrading to 4.1 (where you can use subqueries, nested select statements etc)

If you really must then you will have to do two joins:

select members.*,g1.name,g1.description,g2.name
from members
left join groups as g1
where g1.memberid = members.id
left join groups as g2
on g2.memberid = members.id
and g2.groupid = 'whatever group'

Then discard any rows with g2.name set. It's still dirty, but that's a
function of your database structure.

[Sjoerd Mulder]

Dirty a function of my database structure? Do you mean that there is a
better setup for this kind of information? What structure would you suggest?
Sjoerd
"Kevin Thorpe" <ke***@pricetrak.com> wrote in message
news:3f***********************@news.easynet.co.uk. ..

Sjoerd Mulder wrote:

Members with (id, name, adress, etc)
Groups (id, name, description)
jointable (memberid, groupid)

That's it.

So a member can be part of several groups. Now I want to know how I can
select EVERY MEMBER EXCEPT the members in a specified group. I now have two queries: select all members, and select all members who are member of group x. I compare the two results in PHP. I'm wondering how I can do this in
MySQL without upgrading to 4.1 (where you can use subqueries, nested select statements etc)

If you really must then you will have to do two joins:

select members.*,g1.name,g1.description,g2.name
from members
left join groups as g1
where g1.memberid = members.id
left join groups as g2
on g2.memberid = members.id
and g2.groupid = 'whatever group'

Then discard any rows with g2.name set. It's still dirty, but that's a
function of your database structure.

[Kevin Thorpe]

Sjoerd Mulder wrote:

Dirty a function of my database structure? Do you mean that there is a
better setup for this kind of information? What structure would you suggest?
Sjoerd

If membership of one group supercedes any other memberships then it
should more correctly be a status and marked by a field on the member
record.

However, what you want to do might require such a complex approach. It
really depends on your requirements. Withoug a more complete
understanding of your application I couldn't really offer a solution.

[Kevin Thorpe]

Sjoerd Mulder wrote:

Dirty a function of my database structure? Do you mean that there is a
better setup for this kind of information? What structure would you suggest?
Sjoerd

If membership of one group supercedes any other memberships then it
should more correctly be a status and marked by a field on the member
record.

However, what you want to do might require such a complex approach. It
really depends on your requirements. Withoug a more complete
understanding of your application I couldn't really offer a solution.