Empty Table Ruins Query
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?
[775 byte] By [
justravis] at [2007-11-9 21:10:56]
# 1 Re: Empty Table Ruins Query
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" <dev-archive@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?
Jonas at 2007-11-11 23:52:00 >
# 2 Re: Empty Table Ruins Query
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
# 3 Re: Empty Table Ruins Query
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" <dev-archive@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?
Q*bert at 2007-11-11 23:54:04 >
# 4 Re: Empty Table Ruins Query
What you're looking for is a LEFT JOIN!
"justravis" <dev-archive@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?
# 5 Re: Empty Table Ruins Query
"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" <dev-archive@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?
>
# 6 Re: Empty Table Ruins Query
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
# 7 Re: Empty Table Ruins Query
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" <dev-archive@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?
>>
>
# 8 Re: Empty Table Ruins Query
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)
# 9 Re: Empty Table Ruins Query
"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
PerL at 2007-11-12 0:00:08 >
# 10 Re: Empty Table Ruins Query
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
