>>Mike wrote:
>>
>>>On Jul 10, 11:18 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>>
>>>>Mike wrote:
>>
>>>>>On Jul 10, 10:40 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
>>
>>>>>>Mike wrote:
>>
>>>>>>>On Jul 10, 9:59 am, David Portas
>>>>>>><REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
>>
>>>>>>>>On 10 Jul, 14:39, Mike <gongweig..._at_gmail.com> wrote:
>>
>>>>>>>>>tables:
>>>>>>>>>1. publication(pubid, title)
>>>>>>>>>2. book(pubid, date)
>>>>>>>>>3. journal(pubid, date)
>>
>>>>>>>>>purpose: find all publication titles for books or journals.
>>
>>>>>>>>>solution 1: use set "union"
>>
>>>>>>>>>select title
>>
>>>>>>>>>from publication, ( (select pubid from book) UNION (select pubid from
>>
>>>>>>>>>journal)) bj
>>>>>>>>>where publication.pubid = bj.pubid
>>
>>>>>>>>>solution 2: use "or" operator in the where clause
>>
>>>>>>>>>select title
>>
>>>>>>>>>from publication, book, journal
>>
>>>>>>>>>where publication.pubid = book.pubid or publication.pubid =
>>>>>>>>>journal.pubid
>>
>>>>>>>>>This example is taken from some lecture notes about sql. The notes
>>>>>>>>>said the solution 2 was wrong.
>>
>>>>>>>>>Anyone knows why ?
>>
>>>>>>>>Let me pose two more questions for you. What are the keys for each
>>>>>>>>table? What is the difference between ALL and DISTINCT?
>>
>>>>>>>>If you can answer those questions then you may be on the way to the
>>>>>>>>answer you are looking for.
>>
>>>>>>>>--
>>>>>>>>David Portas- Hide quoted text -
>>
>>>>>>>>- Show quoted text -
>>
>>>>>>>The keys for all the 3 tables are "pubid". "ALL" means to allow
>>>>>>>duplicated records, and "DISTINCT" will remove the duplicated ones.
>>
>>>>>>>But I still don't get it.
>>
>>>>>>>Could you give more hints ?
>>
>>>>>>In the second case, if a book id matches a publication id, how many
>>>>>>journals will match the WHERE clause? Remember TRUE OR FALSE = ____
>>>>>>Similarly if a journal id matches the a publication id, how many books
>>>>>>will match the WHERE clause? Remember FALSE OR TRUE = ____
>>
>>>>>>I will let you fill in the blanks.
>>
>>>>>>Consider as well the nullary cases. What happens to the result of the
>>
>>>>>>FROM clause if there are no books at all or if there are no journals at all?- Hide quoted text -
>>
>>>>>>- Show quoted text -
>>
>>>>>In the second case, if a book id matches a publication id, how many
>>>>>journals will match the WHERE clause?
>>
>>>>>0
>>
>>>>>Similarly if a journal id matches the a publication id, how many books
>>>>>will match the WHERE clause?
>>
>>>>>0
>>
>>>>>>Consider as well the nullary cases.
>>
>>>>>This is the case which fails the solution 2, I think.
>>
>>>>It is one case and one mechanism by which solution 2 fails. Go back
>>>>where I left the blanks and fill them in. Then re-read the paragraph
>>>>they appear in.- Hide quoted text -
>>
>>>>- Show quoted text -
>>
>>>My answer for your previous question.
>>
>>>>In the second case, if a book id matches a publication id, how many
>>
>>>journals will match the WHERE clause?
>>
>>>If a book id matches a publication id, 0 journal will match the WHERE
>>>clause.
>>
>>I disagree. No journals will match "publication.pubid = journal.pubid",
>>but that is only part of the WHERE clause. More than zero journals will
>>match the where clause. How many?

All of them.

>>TRUE OR X = ____
>>
>>X OR TRUE = ____

>
> both are TRUE.

Regardless of X. In your where clause, X is the restrict condition for
books in one case and for journals in the other case.

As you mentioned elsewhere, you can get rid of the duplicates using
DISTINCT, but this only works when |B| > 0, |J| > 0 and the select list
contains only columns from P. Those requirements severely limit the
opportunity for generalizing the method.
Received on Tue Jul 10 2007 - 22:18:04 CEST