mysql query on 2 tables with count

Question

I have 2 tables. One is called artist. Here is the table structure:

artistID lastname firstname nationality dateofbirth datedcease

The other table is called work

workId title copy medium description artist ID

What is the SQL query to list the details of any works of art (including artist who created the work) that have more than one copy recorded in the database?

Mahmoud Gamal · Accepted Answer · 2012-09-23 09:55:05Z

1

Try this:

SELECT 
  w.copy, w.title, w.description, w.medium, 
  a.firstname + ' ' + a.lastname AS 'Artist created the work'
FROM artists a
INNER JOIN
(
    SELECT * 
    FROM work 
    WHERE artistID IN
    ( 
        SELECT artistID
        FROM work 
        GROUP BY artistID
        HAVING COUNT(*) > 1 
    )
) w ON a.artistID = w.artistID

Here is a demo in SQL Fiddle

edited Sep 23, 2012 at 9:55

answered Sep 23, 2012 at 9:26

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql query on 2 tables with count

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related