SQL Joining another table based on the ID of the initial table

3 comments

1. Join on both the id and the meta_key to select your desired value. If you want values that go with other keys in the meta table as well, one way is to join multiple times:

   SELECT bt.paid_date, bt.term, bt.pet_id,
       bn.meta_value as bidname,
       bo.meta_value as bidother
   FROM bid_tag bt
   INNER JOIN wp_postmeta bn on bn.id = bt.pet_id and bn.meta_key = 'bid_name'
   INNER JOIN wp_postmeta bo on bo.id = bt.pet_id and bo.meta_key = 'bid_other'
   WHERE bt.active = 1
   

   If the values won’t necessarily be present in the meta table, use a LEFT OUTER JOIN instead and it will return null for whatever is missing.

   Log in to Reply

2. Here’s how your query would go:

   SELECT B.meta_key, B.meta_value, A.pet_id
   FROM bid_tag A
   JOIN wp_postmeta B
   ON B.id = A.pet_id;
   

   Log in to Reply

3. there’re some ways to use join:
   1)use inner join or equally just join:

   select 
     --some columns
   from bid_tag
   join wp_postmeta on
     bid_tag.pet_id = wp_postmeta.id
   where
     wp_postmeta.meta_key = 'bid_name'
   

   The result will have only that records which pet_id exists in wp_postmeta. The other will be omitted. And if wp_postmeta have a couple of records with the same id (and exists pet_id equal this id) then you’ll get a couple records with this pet_id (BUT with different meta_key and meta_value).
   2)use left join:

   select 
     --some columns
   from bid_tag
   left join wp_postmeta on
     bid_tag.pet_id = wp_postmeta.id
   where
     wp_postmeta.meta_key = 'bid_name'
   

   In this case behavior will be the same except one thing – even if there aren’t any id for a pet_id the result will contain record from wp_postmeta with this pet_id. But meta_key and meta_value in this case will be null.

   Log in to Reply