CODEWITHSUNDEEP
mysqlsql
myol
myol

Reputation: 9838

Using returned SELECT info in mySQL

In a MySQL database I have two tables linked in a join. One table contains people details and another book details. I want to search the databases for a particular author (principalWriter) and return all the co-authers (additionalWriters) they have worked with.

So I use 

SELECT 
  books.additionalWriters, 
  people.name
FROM
  books
  INNER JOIN people ON books.principalWriter = people.personID 
WHERE personID = 1;

And this returns each book the author has worked on with the additional writers ID.

However how can I then use these returned IDs to look up their respective names in the name table? Is there a single query I can do to accomplish this?

Upvotes: 0

Views: 115

Answers (3)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

The problem here is not the query itself but rather the database design. You should have this tables:

  • Writers(*ID*, name): Will store all writers (principal or not)
  • Books(*ID*, name): Will store all books
  • Writers_Books(*WriterID*, *BookID*, Principal): This will store the relationship between the writers and the books and will specify if the writer for that book is principal or not

Primary keys are surrounded by asterisks

Note: You could also remove the Principal field and add it to the Books table, but if a book happens to have to principal writers, you won't be able to solve that with that schema.

Once you update your design, the query will be much easier.

Upvotes: 2

John Woo
John Woo

Reputation: 263723

you need to join it again to peoples table. try this:

SELECT a.AdditionalWriters, c.name, b.name
FROM books a INNER JOIN people b ON
        a.PrincipalWriter = b.personID
     INNER JOIN people c ON a.additionalWriters = c.PersonID
WHERE   b.PersonID = 1

Upvotes: 1

Bobby W
Bobby W

Reputation: 875

Try the following:

SELECT people.name FROM people WHERE personID in 
(SELECT 
  books.additionalWriters, 
FROM
  books
INNER JOIN people ON books.principalWriter = people.personID 
WHERE personID = 1);

Upvotes: 0

Related Questions