CODEWITHSUNDEEP
mysql
CodeHealer
CodeHealer

Reputation: 431

Retrieving the column value behind a foreign key using mysql

Please I would be very glad if someone puts me through this hassle

I have two tables by name employee and borrowed

employee:
id  firstname   lastname    birthdate
1   John         Smith      1976-01-02
2   Laura        Jones      1969-09-05
3   Jane         Green      1967-07-15

borrowed
ref employeeid          book
1       1             Simply SQL
2       2             Ultimate HTML Reference
3       1             Ultimate CSS Reference
4       3             Art and Science of JavaScript

My question is this: How do i retrieve the only the name of the person that borrowed the book "Art and Science of Javascript"

This is my failed sql code.

SELECT book FROM borrowed 
JOIN employee ON employee.id=borrowed.employeeid 
WHERE borrowed.book='Art and Science of Javascript';

Thanks in advance as I anticipate your help.

Upvotes: 1

Views: 50

Answers (2)

Darshan Mehta
Darshan Mehta

Reputation: 30809

You are almost there, all you need to do is to SELECT employee.firstname and employee.lastname

This should do it:

SELECT e.firstname, e.lastname 
FROM employee e
JOIN borrowed b ON e.id=b.employeeid 
WHERE b.book='Art and Science of Javascript';

If you want both first name and last name in single column then you can use MySQL's CONCAT function, e.g.:

SELECT CONCAT(employee.firstname, ' ', employee.lastname) AS name FROM..

Update You can also use sub-query to get the same result, e.g.:

SELECT CONCAT(firstname, ' ', lastname) AS name
FROM employee WHERE id IN (
    SELECT employeeid 
    FROM borrowed 
    WHERE book = 'Art and Science of Javascript'
);

Upvotes: 1

Utsav
Utsav

Reputation: 8093

Your query looks good except the select part. 

 SELECT  firstname,lastname  FROM borrowed 
 JOIN employee ON employee.id=borrowed.employeeid 
  WHERE borrowed.book='Art and Science of Javascript';

It would be better if you use alias for table names but your current code is correct except the select clause

Upvotes: 1

Related Questions