Empty Table Ruins Query

[justravis]

My goal is to create a query that selects authors that are NOT associated
with a certain article. This would include authors that are not associated
with any articles.

Here are my tables:

AUTHOR
id
first
mid
last

ARTICLE
id
title
body

ART_AUTH (link table)
artid
authid

Here is my query thus far:
SELECT author.id, author.first, author.mid, author.last, FROM author, art_auth
WHERE author.id<>art_auth.authid OR (author.id=ar
t_auth.authid AND art_auth.artid<>$artid)

It works GREAT until the link table is empty. Then it returns 0 rows. In
that case, I would like it to just return all the authors in the authors
table. Have any sugestions???

[Jonas]

Hi

I'm new to the SQL game so here's my guess:

As far as I understand it if your many-to-many table ART_AUTH does not hold
any records, then you want to display just a list of Authors?

If so, try this:

IF NOT EXISTS (SELECT author.id, author.first, author.mid, author.last FROM
author, art_auth WHERE author.id<>art_auth.authid OR (author.id=art_auth.authid
AND art_auth.artid<>artid))
(SELECT * FROM AUTHOR)
ELSE
(SELECT author.id, author.first, author.mid, author.last FROM author,
art_auth WHERE author.id<>art_auth.authid OR (author.id=art_auth.authid AND
art_auth.artid<>artid))

From what I gather, this should display your original results, however if
ART_AUTH is empty, then SELECT * FROM AUTHOR.

Hope this helps (not sure if it will work or not!)

Thanks
Jonas


"justravis" <devx@justravis.com> wrote:
>
>My goal is to create a query that selects authors that are NOT associated
>with a certain article. This would include authors that are not associated
>with any articles.
>
>Here are my tables:
>
>AUTHOR
>id
>first
>mid
>last
>
>ARTICLE
>id
>title
>body
>
>ART_AUTH (link table)
>artid
>authid
>
>Here is my query thus far:
>SELECT author.id, author.first, author.mid, author.last, FROM author, art_auth
>WHERE author.id<>art_auth.authid OR (author.id=ar
>t_auth.authid AND art_auth.artid<>$artid)
>
>It works GREAT until the link table is empty. Then it returns 0 rows.
In
>that case, I would like it to just return all the authors in the authors
>table. Have any sugestions???

[Colin McGuigan]

justravis wrote:
> Here is my query thus far:
> SELECT author.id, author.first, author.mid, author.last, FROM author,
> art_auth WHERE author.id<>art_auth.authid OR (author.id=ar
> t_auth.authid AND art_auth.artid<>$artid)
>
> It works GREAT until the link table is empty. Then it returns 0
> rows. In that case, I would like it to just return all the authors
> in the authors table. Have any sugestions???

Instead of joining the two tables together, use WHERE NOT EXISTS:

SELECT author.id, author.first, author.mid, author.last
FROM author
WHERE NOT EXISTS (
SELECT *
FROM art_auth
WHERE art_auth.artid = $artid
AND art_auth.authid = author.id
)

This will return all authors who do not have a link to the specified
article; if the link table is empty, it will return all authors.

--
Colin McGuigan

[Q*bert]

SELECT author.id, author.first, author.mid, author.last
FROM author LEFT JOIN art_auth
WHERE art_auth.artid = NULL

Says... Gimmie all the authors who don't have a match in the art_auth Table.

"justravis" <devx@justravis.com> wrote:
>
>My goal is to create a query that selects authors that are NOT associated
>with a certain article. This would include authors that are not associated
>with any articles.
>
>Here are my tables:
>
>AUTHOR
>id
>first
>mid
>last
>
>ARTICLE
>id
>title
>body
>
>ART_AUTH (link table)
>artid
>authid
>
>Here is my query thus far:
>SELECT author.id, author.first, author.mid, author.last, FROM author, art_auth
>WHERE author.id<>art_auth.authid OR (author.id=ar
>t_auth.authid AND art_auth.artid<>$artid)
>
>It works GREAT until the link table is empty. Then it returns 0 rows.
In
>that case, I would like it to just return all the authors in the authors
>table. Have any sugestions???

[Anonymous Coward]

What you're looking for is a LEFT JOIN!

"justravis" <devx@justravis.com> wrote:
>
>My goal is to create a query that selects authors that are NOT associated
>with a certain article. This would include authors that are not associated
>with any articles.
>
>Here are my tables:
>
>AUTHOR
>id
>first
>mid
>last
>
>ARTICLE
>id
>title
>body
>
>ART_AUTH (link table)
>artid
>authid
>
>Here is my query thus far:
>SELECT author.id, author.first, author.mid, author.last, FROM author, art_auth
>WHERE author.id<>art_auth.authid OR (author.id=ar
>t_auth.authid AND art_auth.artid<>$artid)
>
>It works GREAT until the link table is empty. Then it returns 0 rows.
In
>that case, I would like it to just return all the authors in the authors
>table. Have any sugestions???

[Another AC]

"Anonymous Coward" <nobody@nowhere.com> wrote:
>
If you still have design flexibility, why not eliminate the link table altogether
and just carry the author ID in the article table? Or the article ID in
the author table - whatever. Nice and easy - quickie join.

>What you're looking for is a LEFT JOIN!
>
>"justravis" <devx@justravis.com> wrote:
>>
>>My goal is to create a query that selects authors that are NOT associated
>>with a certain article. This would include authors that are not associated
>>with any articles.
>>
>>Here are my tables:
>>
>>AUTHOR
>>id
>>first
>>mid
>>last
>>
>>ARTICLE
>>id
>>title
>>body
>>
>>ART_AUTH (link table)
>>artid
>>authid
>>
>>Here is my query thus far:
>>SELECT author.id, author.first, author.mid, author.last, FROM author, art_auth
>>WHERE author.id<>art_auth.authid OR (author.id=ar
>>t_auth.authid AND art_auth.artid<>$artid)
>>
>>It works GREAT until the link table is empty. Then it returns 0 rows.

>In
>>that case, I would like it to just return all the authors in the authors
>>table. Have any sugestions???
>

[Colin McGuigan]

Another AC wrote:
> If you still have design flexibility, why not eliminate the link
> table altogether and just carry the author ID in the article table?
> Or the article ID in the author table - whatever. Nice and easy -
> quickie join.

Guess: Many-to-many relationships, where articles can have multiple
authors (eg, scientific articles).

--
Colin McGuigan

[Iain Crossley]

Dear Another AC
your suggestion works well and is efficient if there is only one author per
article (just add an authorid field to the articles table) but if there are
more than 1 author to and article and more than 1 article per author then
the link table is the best way to go.
I favour the earlier method of the left join checking the articleid for null
to be the most efficient method as it removes unecessary table scans that
the other methods (if not exists etc.) would perform. just remember to index
the right fields:-)


"Another AC" <no@spam.com> wrote:
>
>"Anonymous Coward" <nobody@nowhere.com> wrote:
>>
>If you still have design flexibility, why not eliminate the link table altogether
>and just carry the author ID in the article table? Or the article ID in
>the author table - whatever. Nice and easy - quickie join.
>
>>What you're looking for is a LEFT JOIN!
>>
>>"justravis" <devx@justravis.com> wrote:
>>>
>>>My goal is to create a query that selects authors that are NOT associated
>>>with a certain article. This would include authors that are not associated
>>>with any articles.
>>>
>>>Here are my tables:
>>>
>>>AUTHOR
>>>id
>>>first
>>>mid
>>>last
>>>
>>>ARTICLE
>>>id
>>>title
>>>body
>>>
>>>ART_AUTH (link table)
>>>artid
>>>authid
>>>
>>>Here is my query thus far:
>>>SELECT author.id, author.first, author.mid, author.last, FROM author,
art_auth
>>>WHERE author.id<>art_auth.authid OR (author.id=ar
>>>t_auth.authid AND art_auth.artid<>$artid)
>>>
>>>It works GREAT until the link table is empty. Then it returns 0 rows.
>
>>In
>>>that case, I would like it to just return all the authors in the authors
>>>table. Have any sugestions???
>>
>

[E A CORLEY]

SELECT author.id, author.first, author.mid, author.last
FROM author
WHERE NOT IN
(SELECT art_auth.authid
FROM art_auth
WHERE art_auth.artid = $artid)

[PerL]

"Q*bert" <luke_Davis_76@hotmail.com> wrote:
>
>SELECT author.id, author.first, author.mid, author.last
>FROM author LEFT JOIN art_auth
>WHERE art_auth.artid = NULL
>
>Says... Gimmie all the authors who don't have a match in the art_auth Table.

Yepp, if you use Is Null, that is:
...
WHERE art_auth.artid Is NULL

Hth PerL

[Colin McGuigan]

Iain Crossley wrote:
> I favour the earlier method of the left join checking the articleid
> for null to be the most efficient method as it removes unecessary
> table scans that the other methods (if not exists etc.) would
> perform. just remember to index the right fields:-)

Now, I'm willing to be proven wrong on this, but my performance tests in
the past have shown that WHERE NOT EXISTS is superior, performance wise,
to LEFT JOIN...WHERE <Field> IS NULL.

Both require table scans, to the best of my knowledge, but WHERE NOT
EXISTS can exit once it finds a row, whereas a LEFT JOIN will cause it
to scan the entire table for matching rows.

Let's check some query plans. Yep, both require two table scans. With
one table holding ~330,000 records and the other holding ~32,000, WHERE
NOT EXISTS takes about 16 seconds, and LEFT JOIN...WHERE <Field> IS NULL
takes 54.

YMMV on that, of course.

--
Colin McGuigan