CODEWITHSUNDEEP
mysqlwordpress
Deko
Deko

Reputation: 1058

MYSQL query compare 2 table values

Im in deeps waters at the moment, stressed out cause of deadline and frustration over lack of knowledge.

So i'll ask for desperate help.

This is more mysql question but since im working currently with WP ill demonstrate scenario from wp.

I hade posts with post type of "chatt" where each post has its meta that is cruicial. One of the Metas is a timestamp and another is a id for chats.

I need to get all posts with given ID (from meta_value) and where timestamp value is GREATER than the given value.

I got this so far, but im struggling to get this to work :S

   SELECT wp_postmeta.*, 
          wp_posts.* 
     FROM wp_postmeta
LEFT JOIN wp_posts 
       ON wp_posts.ID = wp_postmeta.post_id 
          AND wp_posts.post_type = 'chatt'
LEFT JOIN wp_postmeta t2 
       ON t2.meta_key = 'aw_chat_id' 
          AND t2.meta_value = 'test-chat-av-chattuser'
    WHERE wp_postmeta.post_id = wp_posts.ID 
      AND t2.meta_key = 'aw_chat_timestamp' 
      AND t2.meta_value > 2

Anyone who know how to solve this ?

(Basically, get all posts where post_type = chatt AND meta_value = 'test-chat-av-chattuser' AND meta_value > 2)

EDIT: structure

Here is how it looks like in wp_postmeta:

INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`)
VALUES
    (2212, 1458, 'aw_chat_id', 'test-chat-av-chattuser'),
    (2213, 1458, 'aw_chat_timestamp', '6'),
    (2209, 1457, 'aw_chat_id', 'test-chatt'),
    (2210, 1457, 'aw_chat_timestamp', '5'),
    (2207, 1456, 'aw_chat_id', 'test-chatt'),
    (2208, 1456, 'aw_chat_timestamp', '4'),
    (2205, 1455, 'aw_chat_id', 'test-chatt'),
    (2206, 1455, 'aw_chat_timestamp', '3'),
    (2202, 1454, 'aw_chat_id', 'test-chatt'),
    (2203, 1454, 'aw_chat_timestamp', '2'),
    (2204, 1454, 'aw_chat_answer', 'adfgsdhfgsdfhsfdh'),
    (2199, 1453, 'aw_chat_id', 'test-chatt'),
    (2200, 1453, 'aw_chat_timestamp', '1'),
    (2201, 1453, 'aw_chat_answer', 'asdfasdasdf');



meta_id post_id meta_key    meta_value
2212    1458    aw_chat_id  test-chat-av-chattuser
2213    1458    aw_chat_timestamp   6
2209    1457    aw_chat_id  test-chatt
2210    1457    aw_chat_timestamp   5
2207    1456    aw_chat_id  test-chatt
2208    1456    aw_chat_timestamp   4
2205    1455    aw_chat_id  test-chatt
2206    1455    aw_chat_timestamp   3
2202    1454    aw_chat_id  test-chatt
2203    1454    aw_chat_timestamp   2
2204    1454    aw_chat_answer  adfgsdhfgsdfhsfdh
2199    1453    aw_chat_id  test-chatt
2200    1453    aw_chat_timestamp   1
2201    1453    aw_chat_answer  asdfasdasdf

And this is how the wp_posts look like:

INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`)
VALUES
    (1458, 0, '2012-05-22 11:15:59', '0000-00-00 00:00:00', 'Chatuser chat !?', 'qwedq', '', 'draft', 'open', 'closed', '', '', '', '', '2012-05-22 11:15:59', '0000-00-00 00:00:00', '', 0, 'http://localhost/?post_type=chatt&p=1458', 0, 'chatt', '', 0),
    (1457, 18, '2012-05-22 10:51:27', '2012-05-22 09:51:27', 'asdgasdgfsdfg', 'asdgfasdg', '', 'publish', 'open', 'closed', '', 'asdgfasdg', '', '', '2012-05-22 10:51:27', '2012-05-22 09:51:27', '', 0, 'http://localhost/?post_type=chatt&p=1457', 0, 'chatt', '', 0),
    (1456, 1, '2012-05-22 10:48:13', '0000-00-00 00:00:00', 'sadfasdfasdfasdf', 'asdfasdfasdf', '', 'draft', 'open', 'closed', '', '', '', '', '2012-05-22 10:48:13', '0000-00-00 00:00:00', '', 0, 'http://localhost/?post_type=chatt&p=1456', 0, 'chatt', '', 0),
    (1455, 1, '2012-05-22 10:38:47', '2012-05-22 09:38:47', 'ghkjhlkhkjhkj', 'jhgfjhfjh', '', 'trash', 'open', 'closed', '', 'jhgfjhfjh', '', '', '2012-05-22 10:38:47', '2012-05-22 09:38:47', '', 0, 'http://localhost/?post_type=chatt&p=1455', 0, 'chatt', '', 0),
    (1454, 1, '2012-05-22 10:37:04', '2012-05-22 09:37:04', 'random text', 'awegfw', '', 'publish', 'open', 'closed', '', 'asdasd', '', '', '2012-05-22 10:37:04', '2012-05-22 09:37:04', '', 0, 'http://localhost/?post_type=chatt&p=1454', 0, 'chatt', '', 0),
    (1453, 1, '2012-05-22 01:27:02', '2012-05-22 00:27:02', 'asdfasdfasdf', 'asdfasdf', '', 'publish', 'open', 'closed', '', 'asdfasdf', '', '', '2012-05-22 01:27:02', '2012-05-22 00:27:02', '', 0, 'http://localhost/?post_type=chatt&p=1453', 0, 'chatt', '', 0);



    ID  post_author post_date   post_date_gmt   post_content    post_title  post_excerpt    post_status comment_status  ping_status post_password   post_name   to_ping pinged  post_modified   post_modified_gmt   post_content_filtered   post_parent guid    menu_order  post_type   post_mime_type  comment_count
1458    0   2012-05-22 11:15:59 0000-00-00 00:00:00 Chatuser chat !?    askwlfoef askwlfoef     draft   open    closed                  2012-05-22 11:15:59 0000-00-00 00:00:00     0   http://localhost/?post_type=chatt&p=1458    0   chatt       0
1457    18  2012-05-22 10:51:27 2012-05-22 09:51:27 asdgasdgfsdfg   asdgfasdg       publish open    closed      asdgfasdg           2012-05-22 10:51:27 2012-05-22 09:51:27     0   http://localhost/?post_type=chatt&p=1457   0   chatt       0
1456    1   2012-05-22 10:48:13 0000-00-00 00:00:00 sadfasdfasdfasdf    asdfasdfasdf        draft   open    closed                  2012-05-22 10:48:13 0000-00-00 00:00:00     0   http://localhost/?post_type=chatt&p=1456    0   chatt       0
1455    1   2012-05-22 10:38:47 2012-05-22 09:38:47 ghkjhlkhkjhkj   jhgfjhfjh       trash   open    closed      jhgfjhfjh           2012-05-22 10:38:47 2012-05-22 09:38:47     0   http://localhost/?post_type=chatt&p=1455   0   chatt       0
1454    1   2012-05-22 10:37:04 2012-05-22 09:37:04 asd2ewfefasdf   askwlfoef askwlfoef     publish open    closed      askwlfoef-askwlfoef         2012-05-22 10:37:04 2012-05-22 09:37:04     0   http://localhost/?post_type=chatt&p=1454   0   chatt       0
1453    1   2012-05-22 01:27:02 2012-05-22 00:27:02 asdfasdfasdf    asdfasdf        publish open    closed      asdfasdf            2012-05-22 01:27:02 2012-05-22 00:27:02     0   http://localhost/?post_type=chatt&p=1453   0   chatt       0

Upvotes: 0

Views: 194

Answers (1)

Matt Gibson
Matt Gibson

Reputation: 14959

Unless you want NULL values to be included, LEFT JOIN is unecessary.

Based on your last sentence and comment:

    SELECT chat.*, 
           timestamp.*,
           wp_posts.* 
      FROM wp_posts
INNER JOIN wp_postmeta chat
        ON wp_posts.ID = chat.post_id 
           AND chat.meta_key = 'aw_chat_id'
INNER JOIN wp_postmeta timestamp
        ON wp_posts.ID = timestamp.post_id 
           AND timestamp.meta_key = 'aw_chat_timestamp'
     WHERE wp_posts.post_type = 'chatt'
       AND timestamp.meta_value > 2
       AND chat.meta_value = 'test-chat-av-chattuser'

Upvotes: 1

Related Questions