CODEWITHSUNDEEP
phpmysqlsql
Tom F
Tom F

Reputation: 453

SQL joining columns that have different content

My code:

$newResult = $conn -> query("SELECT wp_nf3_fields.id, wp_nf3_fields.parent_id, wp_postmeta.meta_key
FROM wp_postmeta
LEFT JOIN wp_nf3_fields ON wp_postmeta.meta_key = wp_nf3_fields.id
");

I'm writing a PHP file that is trying to join two database tables based on the entries in their columns. The problem is that the entries in the column wp_postmeta.meta_key are like this

_field_1
_field_2

while the column wp_nf3_fields.id entries are like this

1
2

How can I match these two? Is there a way of just cutting off the field bit from the first column so that I can compare them? I'm ok with PHP but really not very familiar with SQL so would appreciate any advice!

Upvotes: 0

Views: 89

Answers (3)

Daniel Marcus
Daniel Marcus

Reputation: 2686

...or the other way around:

FROM wp_postmeta LEFT JOIN
     wp_nf3_fields
     ON right(wp_postmeta.meta_key,charindex('_', reverse(wp_postmeta.meta_key))-1) =  wp_nf3_fields.id

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

if you string is alway equal to field you could use replace so you could manage also number with more then a digit 

  ("SELECT wp_nf3_fields.id
      , wp_nf3_fields.parent_id
      , wp_postmeta.meta_key
  FROM wp_postmeta
  LEFT JOIN wp_nf3_fields 
     ON wwp_nf3_fields.id =  replace(wp_postmeta.meta_key, '_field_', '')
  ");

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use string concatenation:

FROM wp_postmeta LEFT JOIN
     wp_nf3_fields
     ON wp_postmeta.meta_key = CONCAT('_field_', wp_nf3_fields.id);

Upvotes: 4

Related Questions