An SQL "Query"

[Alexander Anderson]

Dear Forum,

This is a problem in SQL.

I am wanting to get a list of all the books in our database that DON'T have any blurb entered for them.

That's to say, in the web interface, the field "Summary, etc. note" has nothing in it. Most book entries in our database do have a blurb. With such an automatically generated list, it makes it easy to go straight to them and fill them in, rather than go through every single book entry (most of which DO have a blurb) checking.

In our mySQL database, called "openbiblio", there's a table called "biblio" that has the title, author and a few other entries, including "bibid", which I believe is known as a "primary key".

Now, there's another table called "biblio_field", each row of which has the fields:

"bibid", "fieldid", "tag", "ind1_cd", "ind2_cd", "subfield_cd", "field_data"

I notice that all the field_data that is blurb always has a tag=520. (And vice-versa: whenever tag=520, the field_data is blurb.)

SO....

...How do I construct a query that only lists rows from table "biblio" where none of the matching bibids from table "biblio_field" have tag=520?

Sandy Anderson
(Utter SQL Newbie)

[Fred LaPlante]

Try this:

SELECT bibid, title, author

FROM biblio

WHERE bibid NOT IN (SELECT bibid FROM biblio_field WHERE tag=520)

Fred LaPlante, WA1DLZ

On 1/3/2018 10:38 AM, Alexander Anderson wrote:

Dear Forum,

This is a problem in SQL.

I am wanting to get a list of all the books in our database that DON'T have any blurb entered for them.

That's to say, in the web interface, the field "Summary, etc. note" has nothing in it. Most book entries in our database do have a blurb. With such an automatically generated list, it makes it easy to go straight to them and fill them in, rather than go through every single book entry (most of which DO have a blurb) checking.

In our mySQL database, called "openbiblio", there's a table called "biblio" that has the title, author and a few other entries, including "bibid", which I believe is known as a "primary key".

Now, there's another table called "biblio_field", each row of which has the fields:

"bibid", "fieldid", "tag", "ind1_cd", "ind2_cd", "subfield_cd", "field_data"

I notice that all the field_data that is blurb always has a tag=520. (And vice-versa: whenever tag=520, the field_data is blurb.)

SO....

...How do I construct a query that only lists rows from table "biblio" where none of the matching bibids from table "biblio_field" have tag=520?

Sandy Anderson
(Utter SQL Newbie)

---

An SQL "Query"

---

Sent from sourceforge.net because you indicated interest in https://sourceforge.net/p/obiblio/discussion/164978/

To unsubscribe from further messages, please visit https://sourceforge.net/auth/subscriptions/

[Jane Sandberg]

Hi Sandy,

I like using a subquery for things like that:

SELECT * FROM biblio WHERE bibid NOT IN
(SELECT DISTINCT bibid FROM biblio_field WHERE tag='520');

[Alexander Anderson]

Thankyou, Fred LaPlante and Jane Sandberg.

You both nailed it, and saved me a lot of searching and angst ("have I found them all?"). I got twenty hits, ie. empty blurbs, out of a 1000 book entries.

Thanks again,

Sandy Anderson