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.