mysql left join repeating rows

Question

I am working on a database of dictionary with 4 tables designed like

words
wordid | lemma

senses
wordid | synsetid

synsets
synsetid | definition

samples
synsetid | sample

So I use query below to get all sense definitions and samples

if(isset($searchterm)){ 

            echo "<b>".$searchterm."</b><hr>"; 

            // QUERY TO FIND SENSES 
            $senses_query="SELECT   
                words.lemma,words.wordid,  
                senses.wordid,senses.synsetid, 
                synsets.synsetid, synsets.definition, 
                samples.synsetid, samples.sample 
            FROM 
                words  
            LEFT JOIN 
                senses ON words.wordid=senses.wordid 
            LEFT JOIN 
                synsets ON senses.synsetid=synsets.synsetid 
            LEFT JOIN 
                samples ON senses.synsetid=samples.synsetid 
            WHERE 
                words.lemma REGEXP '^$searchterm$'"; 

            $senses_query_result = mysql_query($senses_query) OR die("sense alamadım.<br>".mysql_error()); 
            $num=mysql_num_rows($senses_query_result); 


            while($sensesFound = mysql_fetch_array($senses_query_result )){ 

                echo "&nbsp; "     . $sensesFound['lemma'] .    ""; 
                echo "&nbsp; "     . $sensesFound['definition'] . ""; 
                echo "&nbsp; "     . $sensesFound['sample'] .     ""; 
                echo "<br>"; 

            } 

    }

The problem is: if there are more than one sample of a definition it repeats like this.

definition 1 sample 1
definition 1 sample 2
definition 2 sample 1
definition 2 sample 2
definition 2 sample 3
e.t.c

I like to have rows like

definition 1 sample 1 sample 2
definition 2 sample 1 sample 2 sample 2

I tried group_concat on select but it concats all samples of all definitions into single.

Is it possible with mysql query or should I use php to handle this kind of work.

Thanks in advance

Why do you use Regular expressions and not LIKE ?

ypercubeᵀᴹ
– ypercubeᵀᴹ

2011-06-21 11:50:59 +00:00
Commented Jun 21, 2011 at 11:50 — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jun 21, 2011 at 11:50

Dan Simon · Accepted Answer · 2011-06-20 14:06:25Z

1

You can use GROUP_CONCAT and then in php use explode on the column to split up the individual samples.

Also, with GROUP_CONCAT make sure to use a delimiter not used in the sample text.

answered Jun 20, 2011 at 14:06

Dan Simon

13.2k3 gold badges52 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mustafa Over a year ago

The problem with GROUP_CONCAT is: When I use it SELECT GROUP_CONCAT(samples.sample SEPERATOR '' ) it concats samples (of all definitions) into one string. But I want to CONCAT sampels of definiton 1 into one string, samples of definiton 2 into another string and so on ... But more than using CONCAT I like to learn if it is possible to write a single query - without CONCAT - that will give the results I want

ypercubeᵀᴹ · Accepted Answer · 2011-06-21 11:59:51Z

0

You want all samples (in one row) for every definition.

Therefore, use GROUP_CONCAT(samples.sample) and also GROUP BY senses.wordid, senses.synsetid:

(I guess senses.wordid, senses.synsetid is the primary key of table senses)

        $senses_query="

        SELECT   
            words.lemma, words.wordid,  
            senses.wordid, senses.synsetid, 
            synsets.synsetid, synsets.definition, 
            COUNT(samples.synsetid) AS numberOfSamples
            GROUP_CONCAT(samples.sample SEPARATOR ', ') AS samples
        FROM 
            words  
        LEFT JOIN 
            senses ON words.wordid=senses.wordid 
        LEFT JOIN 
            synsets ON senses.synsetid=synsets.synsetid 
        LEFT JOIN 
            samples ON senses.synsetid=samples.synsetid 
        WHERE 
            words.lemma REGEXP '^$searchterm$'

        GROUP BY senses.wordid, senses.synsetid
        ";

edited Jun 21, 2011 at 11:59

answered Jun 21, 2011 at 11:43

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

1 Comment

mustafa Over a year ago

@ypercupe @Dan @Rakesh I thank you all. Using explode helped to achieve the result I want.

Rakesh Sankar · Accepted Answer · 2011-06-21 11:35:05Z

0

No, you cannot get the way you want from a single query, you have to use GROUP_CONCAT to fetch all the rows (repeating in the join query) as single row.

Use GROUP_CONCAT separated by , and like @Dan said, you can explode (",", $result) to get it in an array.

For ex:

SELECT GROUP_CONCAT(samples.sample SEPERATOR ',' ) .....

The above query will give the results like

definition 1 sample-1,sample-2
definition 2 sample-1,sample-2,sample-3
.....

answered Jun 21, 2011 at 11:35

Rakesh Sankar

9,4255 gold badges45 silver badges67 bronze badges

Collectives™ on Stack Overflow

mysql left join repeating rows

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related