0

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.post_title AS studio_title, 
  studio.meta_value as city,
 wp_posts.ID AS song_ID, wp_posts.post_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?

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

Could you please tell me to JOIN SQL?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.