CODEWITHSUNDEEP
mysql
Harry Lewis
Harry Lewis

Reputation: 171

How to find people who have deceased through SQL from a table?

I have two doubts:

I have a table as follows: 

AUTHOR
(
authorID int NOT NULL,
authName varchar(255) NOT NULL,
authSurname varchar(255),
authPlaceOfBirth varchar(255),
authDOB date(),
authDoD varchar(255),
PRIMARY KEY (authorID)
)

Now, I want to find the authors who have died. That is, if the value of DoD exists in the table, then they have died. How to do this? That is, a particular value in a column exists?

Something like this:

SELECT authName
FROM AUTHOR
WHERE authDoD is not NULL?

Second, I have two tables as follows:

TABLE inventory_genre
{
genreID int NOT NULL,
inventoryID int NOT NULL,
PRIMARY KEY (genreID,inventoryID)
}
TABLE INVENTORY 
{
inventoryID int NOT NULL,
title varchar(255),
wholesale int,
markup int,
qtyinStock int, 
Discount int, 
PRIMARY KEY (inventoryID)
}

I want to list all the genres that have no associated titles in the inventory. I know I have to subtract but I am not able to come up with it exactly. Please guide me in the right direction!

Upvotes: 0

Views: 805

Answers (4)

Martin
Martin

Reputation: 22760

Part 1

Think about it logically:

  • you're looking for people who are referenced by what?

    • By their death.

  • How do you know they're dead?

    • Because a certain field in the table has been filled in.

  • Can you quantify this?

    • Yes, the value of their death exists.

So, yon can construct an SQL search that looks in the table of Authors for rows (people) where the column (authDoD) value is not nothing/null.

As a note for a perfect world you should have IS NULL set in the column authDoD so that if no value is set the column row value is NULL and easier to handle in queries.

SQL (assuming column can be empty but not Null):

SELECT * FROM authors WHERE authDoD > ''

SQL (assuming column can only be null unless dead):

SELECT * FROM authors WHERE authDoD IS NOT NULL

Part 2

You want to return a negative search, a search that turns up no results, so find each genre which does not feature in any inventory table row.

While I'm about to write a longer answer for this, the answer just posted by Tobb does exactly what you need.

Also note that you should ideally have an INDEX on your ID columns to make traversing them smoother and faster.

Upvotes: 1

Tobb
Tobb

Reputation: 12215

Not sure I understand the criteria you are describing in the first question, but either

select * from author where authDOB is not null;

or 

select * from author where authDOB = 'some value that I dont know';

For the second one, you could use exists or in with a nested select:

select * from genre where id not in (select genreId from inventory_genre);

Upvotes: 2

Manav
Manav

Reputation: 1367

For the first part of your question, you could use

SELECT * FROM AUTHOR WHERE authDoD IS NOT NULL;

This would SELECT the deceased people

Upvotes: 0

Otterbein
Otterbein

Reputation: 544

Your first problem is solved, if you check for authorDoD > ''. It will evaluate to false if there's NULL or the empty string ''.

To solve your second problem you could just JOIN your tables, since the inventoryID isn't allowed to be NULL.

JOIN INVENTORY i ON (inventory_genre.inventoryID = i.inventoryID)

After that you can check the existence of an title like above with title > ''.

Upvotes: -1

Related Questions