SQL add attribute from another table

Question

Table WORK
(WORK ID, TITLE, DESCRIPTION, ARTIST ID)

Table ARTIST
(ARTIST ID, FIRSTNAME, LAST NAME, DATE OF BIRTH, DATE OF DEATH)

Hi My problem is that I have to display the details of all works of art (including the name of the artist who created the work) that are signed. I managed to go until

Select * from Work WHERE DESCRIPTION LIKE '%Signed%'

and it displays

WorkID  TITLE   DESCRIPTION   ARTISTID

123     1455    Signed        12

However, I must display

WorkID  TITLE   DESCRIPTION   ARTISTID   FIRSTNAME  LASTNAME

123     1455    Signed        12

How do I do that? I know it has something to do with JOIN but I'm not very sure.

I don't get how you are getting your results, you are selecting * from a table where description is like 'Signed' but the description column shows a value of '1123' — dbajtr, Jun 21 '17 at 07:54
Hi i censored the problem by adding fake values and did not take that into account. Thank you for pointing it out. — Kiown, Jun 21 '17 at 07:57

Ven · Answer 1 · 2017-06-21T08:02:06.697

0

Inner Join will do or can try left join if u need to

SELECT W.*, A.FirstNAme, A.LastName from work w inner join Artist A on 
        A.Artistid 
     = W.ArtistId where A.Description LIKE '%Signed%'

edited Jun 21 '17 at 08:02

answered Jun 21 '17 at 07:55

Ven

2,011
1
13
27

Hi thanks for the answer, it works! However being new to SQL could you explain the shortcuts you used. Like how does W.* works and what is 'work w' or 'Artist A'? – Kiown Jun 21 '17 at 08:01

score 0 · Answer 2 · answered Jun 21 '17 at 08:03

Use inner join to get the records that exist in both tables

Select W.WorkID, W.TITLE, W.DESCRIPTION, W.ARTISTID, A.FIRSTNAME, A.LASTNAME
From Work W
Inner Join Artist A on W.WorkID = A.WorkID
Where Description LIKE '%Signed%'

a left outer join would get all the records in the work table and the matching ones in the artist table.

Check this post out to understand joins a bit more : JOINS

SQL add attribute from another table

2 Answers2