DISTINCT key word is not working

[thara]

Hi everyone..

 

In this query, DISTINCT key word is not working. display duplicate subjects from subject table.. can anybody tell me where I have made mistake?

 

SELECT DISTINCT tutor_category_subject.subject_id, subject.subjects SEPARATOR ', ') AS subjects  
FROM tutor_category_subject
INNER JOIN subject ON tutor_category_subject.subject_id = subject.subject_id
WHERE tutor_category_subject.tutor_id = 3;

[Barand]

does that query really run without syntax error?

[thara]

Sorry..

 

I have made a mistake when I type...

 

This is my query

 

SELECT tutor_option.option_id, GROUP_CONCAT(options.option_name SEPARATOR ', ') AS option_name  
FROM tutor_option
INNER JOIN options ON tutor_option.option_id = options.option_id
WHERE tutor_option.tutor_id = 3;

[Barand]

Thara,

 

I don't really have the time to wait for you to post the actual query that is giving you the problem with "DISTINCT"

[thara]

OK.. Barand

 

When you free tell me what is the problem in "DISTINCT"

 

I tried like this with my query

 

SELECT DISTINCT tutor_category_subject.subject_id, GROUP_CONCAT(subject.subjects SEPARATOR ', ') AS subjects  
FROM tutor_category_subject
INNER JOIN subject ON tutor_category_subject.subject_id = subject.subject_id
WHERE tutor_category_subject.tutor_id = 3;

 

But there are duplicate row selecting from subject table. I want to avoid it from my query. In this case it would have same subject more time to a particular tutor under different category.

 

When you have time tell me what is the solution for this..

 

Thank you very much.

[Pikachu2000]

Post the actual query by cutting and pasting it. Not a query that is "like" the one you're having problems with.

[thara]

This is actual query that im trying...

 

SELECT DISTINCT tutor_category_subject.subject_id, GROUP_CONCAT(subject.subjects SEPARATOR ', ') AS subjects  
FROM tutor_category_subject
INNER JOIN subject ON tutor_category_subject.subject_id = subject.subject_id
WHERE tutor_category_subject.tutor_id = $teacherId";

[Barand]

OK, I tried setting up some data and running your query. The same result was also obtained without the DISTINCT

tutor_category_subject

+----------+------------+

| tutor_id | subject_id |

+----------+------------+

|        1 |          1 |

|        1 |          2 |

|        1 |          3 |

|        2 |          3 |

|        2 |          4 |

|        3 |          1 |

|        3 |          5 |

+----------+------------+

 

subject

+------------+-----------+

| subject_id | subject  |

+------------+-----------+

|          1 | Maths    |

|          2 | Physics  |

|          3 | Chemistry |

|          4 | Biology  |

|          5 | French    |

|          6 | History  |

+------------+-----------+

 

SELECT DISTINCT tutor_category_subject.subject_id, GROUP_CONCAT(subject.subject SEPARATOR ', ') AS subjects 

FROM tutor_category_subject

INNER JOIN subject ON tutor_category_subject.subject_id = subject.subject_id

WHERE tutor_category_subject.tutor_id = 1;

 

results

+------------+---------------------------+

| subject_id | subjects                  |

+------------+---------------------------+

|          1 | Maths, Physics, Chemistry |

+------------+---------------------------+

 

No duplicate rows and subjects concatenated as expected. To me though, it would make more sense with that GROUP_CONCAT() to be selecting tutor_id instead of subject_id

[Barand]

Is this the scenario you are describing where a subject appears twice with different catgories?

 

tutor_category_subject

+----------+------------+--------+

| tutor_id | subject_id | cat_id |

+----------+------------+--------+

|        1 |          1 |      0 |

|        1 |          2 |      0 |

|        1 |          3 |      0 |  same subject

|        1 |          3 |      1 |  different category

+----------+------------+--------+

 

SELECT tutor_category_subject.subject_id, GROUP_CONCAT(subject.subject SEPARATOR ', ') AS subjects 

FROM tutor_category_subject

INNER JOIN subject ON tutor_category_subject.subject_id = subject.subject_id

WHERE tutor_category_subject.tutor_id = 1;

 

results in Chemistry listed twice

+------------+-------------------------------------+

| subject_id | subjects                            |

+------------+-------------------------------------+

|          1 | Maths, Physics, Chemistry, Chemistry|

+------------+-------------------------------------+

 

If so, you can avoid this by putting the DISTINCT inside the GROUP_CONCAT

 

SELECT tutor_category_subject.subject_id, 
    GROUP_CONCAT(DISTINCT subject.subject SEPARATOR ', ') AS subjects  
FROM tutor_category_subject
INNER JOIN subject ON tutor_category_subject.subject_id = subject.subject_id
WHERE tutor_category_subject.tutor_id = 1;

[fenway]

Remember, DISTINCT is a modifier to select, not a function.

[thara]

Thank you Barand..

 

My problem is solved with your help...

So if you have a time help me to this post http://forums.phpfreaks.com/index.php?topic=362819.0

 

Thank you...