joining two tables issue 8

Question

I have problem with this join...

Here are my 2 tables, adds and adds_filters

adds_filters structure:

addfilterid
addid
filterid

  data the table look like this:
  addfilterid     addid     filterid    
  1               31        65
  2               31        158
  3               32        109
  4               32        145 



adds structure

addid
addtitle
addtext



 data the table look like this:
 addid    addtitle     addtext
 31       test         test
 32       test2        test2  

One add can have many filters. I want to get all filters that are in the adds_filters and display the results from table adds. I have the following query...

SELECT * , af.filterid, af.addid
FROM `adds` AS a
INNER JOIN `adds_filters` AS af ON a.addid = af.addid
LIMIT 0 , 30

In the table adds, i have 9 records. The above query returns 11 results. I would like to get 9 records and if one add have more then one filter, to be displayed in the same row...hos can i do that?

Regards, John

Answer 1

You might try this.

maybe it gets close to your wishes.

TRANSFORM Count(adds_filters.filterId) AS sumOffilterId
SELECT adds.addid, adds.addtitle, adds.addtext
FROM adds INNER JOIN adds_filters ON adds.addid = adds_filters.addid
GROUP BY adds.addid, adds.addtitle, adds.addtext
PIVOT adds_filters.filterId;

addid addtitle addtext 65 109 145 158

31 test test 1 1

32 test2 test2 1 1

Answer 2

Looking at your query 11 records appears tobe the correct result because one add has more then one filter. So if every add has at least one filter and some of them two or more the result would be more then 9 rows.

This is my explanation but maybe you would like to accomplish something else?

My question why do you expect 9 rows?

Answer 3

use

SELECT a.* , GROUP_CONCAT(DISTINCT af.filterid)
FROM `adds` AS a
JOIN `adds_filters` AS af ON a.addid = af.addid
GROUP BY a.addid
LIMIT 0 , 30