simple SQL script

Question

could someone show me the sytax for this query??

"Select all the information about each artist who does not have works listed by the gallery".

(the key value is ArtistID)

the query refers to 2 tables:

CREATE TABLE listofartists (
  ArtistID       INT(11)  NOT NULL AUTO_INCREMENT,
  LastName       CHAR(25) NOT NULL,
  FirstName     CHAR(25) NOT NULL,
  Nationality   CHAR(30) DEFAULT NULL,
  DateDeceased   INT(11)  DEFAULT NULL,
  DateOfBirth   INT(11)  DEFAULT NULL,
  CONSTRAINT ListOfArtists_PK PRIMARY KEY (ArtistID)
);


CREATE TABLE work (
  WorkID      INT           NOT NULL AUTO_INCREMENT,
  Title       CHAR(35)      NOT NULL,
  Copy        CHAR(12)      NOT NULL,
  Medium      CHAR(35)      DEFAULT NULL,
  Description VARCHAR(1000) DEFAULT 'Unknown provenance',
  ArtistID    INT           NOT NULL,
  PRIMARY KEY (WorkID),
  UNIQUE KEY WorkAK1 (Title,Copy),
  FOREIGN KEY ArtistFK (ArtistID)
    REFERENCES listofartists (ArtistID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
);

yes you should try first and let us know how close you got. Also the table name `listofartists` is weird. Artist or Artists will be much better name. — Saher Ahwal, Dec 31 '14 at 22:53
Check out this tutorial on joining tables, the answer should come easily. http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins — hofan41, Dec 31 '14 at 22:53
@hofan41, Nice. Never thought of that excluding joins before. — Doon, Dec 31 '14 at 22:56
possible duplicate of [MySQL: Finding rows that don't take part in a relationship](http://stackoverflow.com/questions/544094/mysql-finding-rows-that-dont-take-part-in-a-relationship) — FuzzyTree, Dec 31 '14 at 23:15

score 2 · Accepted Answer · edited Dec 31 '14 at 23:37

2

For that you have to first select all artist_id from work and then check if these ids are not in artist table.

SQL FIDDLE

select * from artist where id not in(select artist_id from work)

edited Dec 31 '14 at 23:37

Saher Ahwal

9,015
32
84
152

answered Dec 31 '14 at 23:08

singhakash

7,891
6
31
65

1

it is nice you provide the FIDDLE but in case for some reason the link goes bad or anything it is always better to write the answer here so no link dependency. Thanks – Saher Ahwal Dec 31 '14 at 23:36

score 0 · Answer 2 · answered Dec 31 '14 at 22:54

0

Select * 
  from listofartists a 
    where not exists(select * from work w where w.artistid = a.artistid)

answered Dec 31 '14 at 22:54

ps2goat

8,067
1
35
68

LucasY · Answer 3 · 2014-12-31T23:07:38.437

0

SELECT 
     *
FROM 
     listofartists
WHERE 
     ArtistID NOT IN(
     SELECT 
          DISTINCT ArtistID
     FROM
          work)

Maybe not the most efficient, but it'll do.

edited Dec 31 '14 at 23:07

answered Dec 31 '14 at 22:55

LucasY

64
10

My answer has been edited to reflect the change. Thanks for the catch! As for the necessity of Distinct, I agree, it's not necessary, but it won't hurt, and it may improve performance if the 'works' table is very large. – LucasY Dec 31 '14 at 23:08

Saher Ahwal · Answer 4 · 2014-12-31T23:35:31.827

You need a left excluding Join:

SELECT *
FROM listofartists as a 
LEFT JOIN work as b
ON a.ArtistID = b.ArtistID
WHERE b.ArtistID IS NULL

the left JOIN will cause all results on left table to appear in the result so the artists with no work will still be there but the right table will have a NULL value for ArtistID since it is not there.

This is probably the cleanest way to do it if you don't want nested SQL statements like the other provided answers

simple SQL script

4 Answers4

SQL FIDDLE