How can I JOIN two wp_posts?

I have a problem with table JOIN SQL (CPT/Pods). For many hours I tried, but no result. Ex.

studio (post_title, city, date, song->)
song (post_title, date, band->)

SELECT
wp_posts.ID AS studio_ID, wp_posts.studio_title AS studio_title,
studio.meta_value as city,
wp_posts.ID AS song_ID, wp_posts.song_title AS song_title, song.meta_value
as date
FROM wp_posts
LEFT JOIN wp_postmeta AS studio
ON (wp_posts.ID = studio.post_id AND studio.meta_key=’city’)
LEFT JOIN wp_postmeta AS song
ON (wp_posts.ID = song.post_id AND song.meta_key=’date’)
WHERE wp_posts.post_type = ‘studio’ — or ‘post’ or ‘song’
— AND studio.meta_value = “.get_the_ID().”

1) Bands have Nirvana on one post, Pearl Jam on another post etc.
I could unfortunately get all studio on all the bands, ex. Nirvana, Pearl Jam, Soundgarden etc on one post. Can you tell me to show one band, not all bands, on one post?

2) I get primary fields, ie. wp_posts.ID AS studio_ID and wp_posts.studio_title AS studio_title.
But I can get no Custom Fields, ie. studio.meta_value as studio: $post->studio_title; is zero and none. Can you tell me to show custom fields, i.e. city/date?

Could you please tell me to JOIN SQL?

I rather have content-band.php, ex:

song.post_title – studio.post_title – studio.date

Big Long Now – Reciprocal Recording Studios – 1988
Stain – Music Source Studios – 1989
In Bloom – Sound City Studio Smells – 1989
Dive – Smart Studios – 1990
Serve the Servants – Pachyderm Studio – 1991

etc

Read more here:: How can I JOIN two wp_posts?

Leave a Reply

Your email address will not be published. Required fields are marked *