-1

I am trying to join multiple tables in MySQL, here is the query:

SELECT p.post_title
     , p.post_content
     , p.guid
     , i.guid
     , m.meta_value
  FROM twypl_posts p
  JOIN twypl_posts i 
    ON i.ID = p.post_parent 
  JOIN twypl_postmeta m 
    ON p.ID = m.post_id
 WHERE p.post_status = "publish" 
   AND p.post_type   = "product"
   AND i.post_type   = "attachment"
   AND m.meta_key IN ("_product_attributes", "_sku", "_price")

So basically I have 2 tables, twypl_posts and twypl_postmeta, but I want to join 3 tables, 2 twypl_posts (one with only "product" post_type and one with only "attachment" post_type) and a twypl_postmeta table. They are all connected via a foreign key ID (twypl_posts), post_parent AND post_id respectively. twypl_posts has a post_parent for "attachment" post_types which link to the parent product (twypl_posts with post_type "product").

When I run the above query it doesn't return any results (no query errors).

My SQL has gotten a little rusty after using frameworks that have sophisticated DRM. Any help would be appreciated.

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Ali Elkhaiat
  • 57
  • 1
  • 9

1 Answers1

0

I think you made a mistake there : INNER JOIN twypl_posts i ON p.post_parent = i.ID It should be INNER JOIN twypl_posts i ON p.ID = i.post_parent Since i is the attachment

Ianis
  • 1,165
  • 6
  • 12