How can I JOIN two wp_posts?

by Dani   Last Updated May 15, 2019 15:08 PM

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



Related Questions


Updated December 20, 2017 09:08 AM

Updated March 25, 2019 14:08 PM

Updated March 16, 2017 18:08 PM

Updated March 13, 2017 21:08 PM

Updated November 28, 2017 22:08 PM