CODEWITHSUNDEEP
phpmysqlsqljoin
steeped
steeped

Reputation: 2633

MySQL query JOIN with 2 tables and multiple entries

I'm trying to do a MySQL JOIN query to grab the content from two tables.

This is what Table A looks like:

Table A

ID  |  ISBN   |  Type
----------------------
12  | 0338566 | book
15  | 6656565 | post
16  | 9435644 | book
20  | 8525446 | book

And Table B

ID  |  tableA_id  |    Key      |  Value
---------------------------------------------
1   |     12      |    Author   |  John Doe
2   |     12      |    Title    |  Book Title 1
3   |     16      |    Title    |  Book Title 2
4   |     20      |    Author   |  John Doe
5   |     20      |    Title    |  Book Title 3

I am trying to build my SQL statement to output all book data, something like:

ISBN    |    Author    |    Title
-------------------------------------
0338566 |   John Doe   | Book Title 1
9435644 |              | Book Title 2
8525446 |   John Doe   | Book Title 3

After looking up SQL JOIN statements, this is what I came up with:

SELECT tableA.ISBN, tableB.value
FROM tableA, tableB  
WHERE tableA.ID = tableB.tableA_id AND tableA.type = “book” AND  (tableB.key = "title" OR tableB.author = "store_selector" )

The query is bringing back just 2 columns, because I'm only referencing tableB.value once, even though I need to grab both values within it (title and author).

How would I properly structure this query?

Upvotes: 1

Views: 77

Answers (1)

Pascamel
Pascamel

Reputation: 953

Maybe not the best way to solve this problem, but I would join tableB two times with aliases and pick the value as needed like this :

SELECT tableA.ISBN, author.value as Author, title.Value as Title
FROM tableA
left join tableB author 
    on tableA.ID = author.tableA_id  
    and author.key = "Author"
left join tableB title 
    on tableA.ID = title.tableA_id 
    and title.key = "Title"
where tableA.type = “book”

PS: this data model looks terrible, I'd rather update it and store titles and authors in two different tables

Upvotes: 2

Related Questions