CODEWITHSUNDEEP
mysql
IlludiumPu36
IlludiumPu36

Reputation: 4304

Join on field with comma delimited string

I want to join two tables based on comma seperated keys contained in one field. So the two tables are 'topic' and 'link' and topic contains the field 'links' which contains the comma seperated IDs of records in the table link. Is this possible?

Upvotes: 0

Views: 606

Answers (2)

IlludiumPu36
IlludiumPu36

Reputation: 4304

Thanks for this. After looking around a bit more I decided to create a third table called topic_lnks which contain seperate fields for the topic ID and the link ID. This will make the join much easier to handle. In my case I use:

if(isset($_POST['submit'])){

        $query = "INSERT INTO topic (topic_pk,title,topic,majors,sub_discipline_fk,author_fk,created)
          VALUES ('','$title','$topic','$majors_string','$sub_discipline','$author_pk',NOW())";
        $result = mysql_query($query, $connection) or die(mysql_error());
        if($result){
            $topic_pk = mysql_insert_id();
        }

        foreach($links as $link){
                    $query_links = "INSERT INTO topic_links (topic_link_pk,topic_fk,link_fk)
          VALUES ('','$topic_pk','$link')";
        $result_links = mysql_query($query_links, $connection) or die(mysql_error());
        }
        if($result_links){
            $message = "- The topic '" . $title . "' has been created";
        }
}

Then I can just query the topic_links table getting links based on the field 'topic_fk'.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

While you should be more descriptive with your question (it would be nice to have table definitions, sample data, desired output), @AaronBertrand showed me this earlier today on how to join tables on comma separated lists:

SELECT DISTINCT T.Id, L.Link 
FROM Topic T
   JOIN Link L ON CONCAT(',',T.Links,',') LIKE CONCAT('%', L.Link,'%')

Here is some condensed SQL Fiddle to use as an example.

Good luck.

Upvotes: 3

Related Questions