Find 0 records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Adrienne Boswell - 02 Feb 2008 20:21 GMT

MySQL -
Here are the tables:
Principles - id, word
Donors - calmonth, donor

Data:
Principles --
1, respect
2, honor
3, trustworthiness
4, loyalty

Donors --
1, John Doe
2, Mary Doe
3, Bob Doe

I need to find out which ids in Principles are NOT already taken by
donors. I would like to use the result in a select list, so the user
can only choose those words that are currently available. In this
example, Loyalty is the only one available.

I know this is probably dead simple and I'll slap my head, but right
now, I am blank.

Thanks in advance.

Signature

Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Reply to this Message

Bob Barrows [MVP] - 02 Feb 2008 21:48 GMT

> MySQL -
> Here are the tables:
[quoted text clipped - 20 lines]
> I know this is probably dead simple and I'll slap my head, but right
> now, I am blank.

Think "outer join"

select p.id, p.word from Principles p left outer join Donors d
on p.id=d.id
where d.id is null

Signature

Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Reply to this Message

Adrienne Boswell - 03 Feb 2008 01:25 GMT

>> MySQL -
>> Here are the tables:
[quoted text clipped - 26 lines]
> on p.id=d.id
> where d.id is null

Thank you, Bob. I used the first one. Worked perfectly!

Signature

Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Reply to this Message

Bob Lehmann - 02 Feb 2008 21:50 GMT

Weird that 'donors.calmonth' relates to 'priciples.id, but.......

Don't know if this works in MySQL -
select distinct id, word
from principles p
where p.id not in (select calmonth from donors)

Bob Lehmann

> MySQL -
> Here are the tables:
[quoted text clipped - 22 lines]
>
> Thanks in advance.

Reply to this Message

Adrienne Boswell - 03 Feb 2008 01:15 GMT

Gazing into my crystal ball I observed "Bob Lehmann"
<nospam@dontbotherme.zzz> writing in news:O6$RfUeZIHA.1164
@TK2MSFTNGP02.phx.gbl:

>> MySQL -
>> Here are the tables:
[quoted text clipped - 31 lines]
>
> Bob Lehmann

I love you, Bob!

Actually, the principles relate to the 12 principles of Character and
Ethics, and a word is assigned for each month. Donors can signup to
sponsor a month for the annual Art Calendar [http://ethicsproject.org].

:x Happy Valentines (early)

Signature

Adrienne Boswell at Home
Arbpen Web Site Design Services
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Reply to this Message

Webmaster Forum / ASP / Database Access / February 2008

Find 0 records