MySQL query JOIN with 2 tables and multiple entries

Question

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?

I recommend you to properly structure your database rather than query. Add Author and Title columns to tableB — umka
– umka, Commented Jun 15, 2015 at 18:31
@umka This is actually the way Wordpress stores data for posts, in the wp_posts table and wp_postmeta table. — steeped
– steeped, Commented Jun 15, 2015 at 18:33

Pascamel · Accepted Answer · 2015-06-15 18:34:49Z

2

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

edited Jun 15, 2015 at 18:34

answered Jun 15, 2015 at 18:32

Pascamel

9536 gold badges18 silver badges28 bronze badges

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

10 Comments

steeped Over a year ago

This is actually the way Wordpress stores data for posts, in the wp_posts table and wp_postmeta table. This isn't my dat model unfortunately.

Pascamel Over a year ago

there is probably a reason behind this choice then :) let me know if that query works for you.

steeped Over a year ago

PhpMyAdmin is giving me the following error: #1054 - Unknown column 'author.value' in 'field list'. There is no table called author... is that why?

Pascamel Over a year ago

in this case, author is an alias, not a table. in your model, Value is noted with a capital V and your error message indicates you wrote a lowercase v, maybe this is the reason of this error

steeped Over a year ago

Same issue with a capital V. So the above statement was my compressed version of a Wordpress table... I can show you the real query I'm using.. it's:

|

Collectives™ on Stack Overflow

MySQL query JOIN with 2 tables and multiple entries

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related