Mysql – JOIN 2 tables, and query data from them 3 times

join;MySQLselect

Basically I have 2 tables: post_meta and posts.

post_meta has a post_id, key and value:

post_id  key      value
6343     make     Chevy
6343     model    Volt
6794     make     Chevy
6794     model    Silverado

posts has a post_id and content:

post_id  content
5434     Hello World!
6343     Chevy Volt review
6794     The New Silverado

What I need to do is pull the model based on the make (pull all unique models with the make "Chevy"). The database isn't very big so it doesn't need to be a real fast query. Basically the plan in my mind was to get the model of every post_id that has the post_meta.make "Chevy".

SELECT post_id FROM postmeta
WHERE key = 'make'
AND value = 'Chevy'

then take the post_id

SELECT DISTINCT value FROM postmeta
WHERE post_id = {an array of post_id's}

something to that extent.

Best Answer

Given your table structure, this should extract what you are looking for:

SELECT DISTINCT p1.value
FROM postmeta p1
INNER JOIN postmeta p2 ON p1.post_id = p2.post_id
WHERE p2.key = 'make'
    AND p2.value = 'Chevy'
    AND p1.key = 'model';

Related Solutions

Mysql – Joining two tables and query them

Assuming that value is a numeric data type:

SELECT 
    A.name
    , A.dateOfBirth

FROM
    artist A
    JOIN exhibitions.picture P
        ON A.name = P.name

WHERE
    P.Value > 50
    AND A.dayOfDeath is NULL;

Mysql – Index optimization

I think that the best for your tag and posttag tables is:

ok for the tagid as a primary key
add an index on your tag field (this cover your second query)
add two index on posttag table like INDEX(postid,tagid) and INDEX(tagid,postid) (this cover your first and second query)

So:

CREATE TABLE `posts` (
  `postid` mediumint(9) NOT NULL AUTO_INCREMENT,
  `post` varchar(255) NOT NULL,
  PRIMARY KEY (`postid`)
);

CREATE TABLE `posttags` (
  `postid` mediumint(9) NOT NULL,
  `tagid` mediumint(9) NOT NULL,
  PRIMARY KEY (`postid`,`tagid`),
  UNIQUE KEY `tagid_postid_UQ` (`tagid`,`postid`),
  FOREIGN KEY (postid)
    REFERENCES posts (postid),
  FOREIGN KEY (tagid)
    REFERENCES tags (tagid)
);

CREATE TABLE `tags` (
  `tagid` mediumint(9) NOT NULL AUTO_INCREMENT,
  `tag` char(30) NOT NULL,
  PRIMARY KEY (`tagid`),
  UNIQUE KEY `tag_UQ` (`tag`)
);

And for your query:

mysql> explain select p.* from tags as t    inner join posttags as pt on t.tagid = pt.tagid   inner join posts as p on p.postid = pt.postid  where t.tag =  ?;
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref            | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref    | PRIMARY,tag   | tag     | 32      | const          |    1 | Using where; Using index |
|  1 | SIMPLE      | pt    | ref    | postid,tagid  | tagid   | 3       | test.t.tagid   |    1 | Using index              |
|  1 | SIMPLE      | p     | eq_ref | PRIMARY       | PRIMARY | 4       | test.pt.postid |    1 |                          |
+----+-------------+-------+--------+---------------+---------+---------+----------------+------+--------------------------+


explain select p.* from posts as p    inner join posttags as pt on pt.postid = p.postid   inner join tags as t on t.tagid = pt.tagid  where p.postid = ?;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | p     | const  | PRIMARY       | PRIMARY | 4       | const         |    1 |             |
|  1 | SIMPLE      | pt    | ref    | postid,tagid  | postid  | 4       | const         |    2 | Using index |
|  1 | SIMPLE      | t     | eq_ref | PRIMARY       | PRIMARY | 3       | test.pt.tagid |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+

Best Answer

Related Solutions

Mysql – Joining two tables and query them

Mysql – Index optimization

Related Question