Insert data from custom posttype in custom posttype WordPress
<p>I'm not really familiar with this so here is my question.</p>

<p>I have a custom posttype for books and I got one of lessons.
I can add books, I can add lessons. Whenever I'm at the admin panel to add a new lesson, I can add books to it. Multiple lessons can connect to the same book, a book can be placed in more than one lesson.</p>

<p><strong>As I can see in my database:</strong></p>

<p><strong>wp_post:</strong></p>

<pre><code>ID       post_title     post_content        post_type    
95       titletest      bladieblabla        lpr_lesson   
97       titletest2     a lot of content    lpr_lesson   
216      hellotest                          lpr_book
276      hellotest2                         lpr_book
</code></pre>

<p><strong>wp_postmeta:</strong></p>

<pre><code>meta_id  post_id   meta_key       meta_value
897      216       _lpr_book_url  https://en.wikibooks.org/1
911      276       _lpr_book_url  https://en.wikibooks.org/2
985      276       _lpr_lesson    97
[..]     [..]      [..]           [..]
1001     216       _lpr_lesson    95
1002     276       _lpr_lesson    95
</code></pre>

<p>And that works fine (<strong>remember; I've not so much experience with this</strong>). I can connect books and lessons with each other in the admin panel. I also see it (<code>thumbnail</code>, <code>title</code> etc)</p>

<p>What I want; If select a lesson on the page, it will open and show me that lesson. So that means the title and content of that post. But I want it to also show the matching book(s).</p>

<p>I use an existing <strong>plugin</strong> (LearnPress, <code>old version 0.9.20</code> or something) what I used as a base.</p>

<p>So what I want: I want all post_id's in the table wp_postmeta which have the meta_value matching the <strong>post_id</strong> of the current page.</p>

<p>Lets say the current lesson page has the post_id of 95. Than I want to get back the post_id's 216 and 276 in the <strong>wp_postmeta</strong> table. So I can use those id's so I can use ID 216 first to get the URL and the thumbnail. And after that to retrieve the data from 276, etc. (And maybe the title someday if I understand it all but that it probably too hard for me now)</p>

<p>It's really complicated for me and I'm just stuck. And it's really, really bugging me (it's 04:45 right now lol)
I can retrieve the ID of the lesson if I do <code>$echo $lesson_id</code>;
But I just have no clue how to get the matching data to show the books.</p>

<p>The part of the code that works to get the data of the lesson is here <a href="http://pastebin.com/PERv6g5L" rel="nofollow">http://pastebin.com/PERv6g5L</a></p>

<p>Of course I want a solution to get this done, but if possible I also want a (small) explanation how and why so I learn to understand how it all works.</p>

<p><strong>I know I (probably?) should use something like:</strong></p>

<pre><code>$books = $wpdb-&gt;get_results(
    "
    SELECT          pm.meta_value
    FROM            $wpdb-&gt;posts        AS p
    INNER JOIN      $wpdb-&gt;postmeta     AS pm  ON p.ID = pm.post_id
    WHERE           mp.meta_key = _lpr_lesson
    OR              mp.meta_key = _lpr_book_url             
    "
    )
);
</code></pre>

<p>And than some code to display it on the screen.
But, something else. I'm just really confused.</p>

<p>I'll send cookies and chocolate to the one who can help me out.</p>

<p><strong><em>EDIT EDIT EDIT:</em></strong></p>

<p>The answer of milan kyada didn't work out for me.</p>

<p>I figured out that I can retrieve the lesson id by $lesson_id. With this lesson id I can search meta_value for corresponding books. This also means I only need to use the table <strong>wp_postmeta</strong>.</p>

<p>So I search meta_value for $lesson_id and than I want corresponding post_id (of books: 216 for example). Then I try to use those post_id(s) to get the meta_value by filtering on that post_id with the meta_key _lpr_book_url or _thumbnail_id. So I get 2 results: The URL and the thumbnail id.</p>

<p>So what i tried:</p>

<pre><code>$books = $wpdb-&gt;get_results(
    "
    SELECT      meta_value
    FROM        $wpdb-&gt;postmeta
    WHERE       (meta_key = '_lpr_book_url'  OR  meta_key = '_thumbnail_id')
    AND         post_id = (
        SELECT      post_id
        FROM        $wpdb-&gt;postmeta
        WHERE       meta_value = $lesson_id
        GROUP BY    meta_value
    )
"
);
</code></pre>

<p>Returns:</p>

<pre><code>Array ( [0] =&gt; stdClass Object ( [meta_value] =&gt; https://www.brinkman-uitgeverij.nl/lesmateriaal/backend-programmeren ) [1] =&gt; stdClass Object ( [meta_value] =&gt; 272 ) )
</code></pre>

<p>This doen't work. It returns only the first book it finds, not the others. If I use GROUP BY post_id I don't get any result at all.</p>

<p>If I use this:</p>

<pre><code>$books_id = $wpdb-&gt;get_results(
    "
    SELECT      pm.post_id
    FROM        $wpdb-&gt;postmeta  AS pm
    WHERE       pm.meta_value = $lesson_id
    "
);

print_r($books_id);
</code></pre>

<p>Returns: </p>

<pre><code>Array ( [0] =&gt; stdClass Object ( [post_id] =&gt; 216 ) [1] =&gt; stdClass Object ( [post_id] =&gt; 276 ) )
</code></pre>

<p>I get 1 or more book id's corresponding the lesson. So I want the rest:</p>

<pre><code>$books = $wpdb-&gt;get_results (
    "
    SELECT      meta_value
    FROM        $wpdb-&gt;postmeta
    WHERE       post_id = 216
    AND         (meta_key = '_lpr_book_url'  OR  meta_key = '_thumbnail_id')
    "
);
</code></pre>

<p>returns:</p>

<pre><code>Array ( [0] =&gt; stdClass Object ( [meta_value] =&gt; https://en.wikibooks.org/1 ) [1] =&gt; stdClass Object ( [meta_value] =&gt; 272 ) )
</code></pre>

<p>But as you can see. I used hardcoded 216. If I use $book_id instead, It doesn't work. </p>

<p>How can I fix this? Or merge the two tables as one? </p>