JOIN returns too many results

Question

This question is related to:

Another unknown column name error in ON clause

My actual query:

    SELECT  c.title AS title
      , c.introtext AS body
      , c.state AS state
      , c.created AS created
      , c.created_by AS uid
      , c.modified AS modified
      , c.modified_by AS modified_uid
      , c.published AS published
      , c.published_by AS published_uid
      , jc.title AS category
    FROM  jos_content AS c
    INNER JOIN jos_categories AS jc 
    ON c.sectionid = jc.section 
    WHERE c.sectionid = 4

I want to get content by section and with category names of this content.

I know that in jos_content table rows with section of id 4 is 8000, but this query returns me arount 177k rows.

I try change INNER JOIN to LEFT etc and use DISTINCT but it won't help

Tables columns:

jos_content:    

id, title, introtext, state, created, created_by etc


jos_categories:

  id, section (id of sections, names doesn't naming convention IMO, its Joomla 1.5 db BTW), title

jos_sections:
  id, title

What I want to get is:

jos_content.title (etc) with jos_categories_name of selected section

Is there an actual category column in the content table? You're finding _all categories in the same section as the content_ per row, not just the actual category related to the content. In other words, if the section has 100 categories, you'll get 100 times the rows you expect. — Joachim Isaksson, Aug 11 '12 at 08:15
Have you tried building up the query slowly to work out where the error comes in? So try `SELECT * FROM jos_content AS c WHERE c.sectionid = 4` and see how many results that gives. What you have shouldn't be able to give any more results than that. — TimD, Aug 11 '12 at 08:16
You probably have multiple categories with the same section id `4` — Nir Alfasi, Aug 11 '12 at 08:19
@alfasin Yes category belongs to section, and this section has 20 categories — keram, Aug 11 '12 at 08:38

score 1 · Answer 1 · answered Aug 11 '12 at 08:31

Due to lack of information, I can't tell you how to write your query, but I can tell you why you're getting the wrong result on the existing one.

If this is the content of your tables (simplified);

jos_content:    id      sectionid      title
                 1              4      Content 1 
                 2              4      Content 2

jos_categories  id        section      title
                 1              4      Category 1
                 2              4      Category 2

...and you run your query (simplified)...

SELECT  c.title AS title
     , jc.title AS category
FROM  jos_content AS c
INNER JOIN jos_categories AS jc 
ON c.sectionid = jc.section 
WHERE c.sectionid = 4

...you'll get 4 rows (the number of categories in section 4 times the number of content items in section 4) as a result.

title        category
Content 1    Category 1
Content 2    Category 1
Content 1    Category 2
Content 2    Category 2

The reason is that you have nothing in the query binding the content to a category, you're querying via section which gives you many possible answers to your query.

Thanks for explanation. I know why but it kills me anyway, it's migration script so I just try without join at all, sad — keram, Aug 11 '12 at 08:39

score 0 · Answer 2 · answered Aug 11 '12 at 08:19

or , try this script.Group by [Content] table in [SectionId] setup:

SELECT  c.title AS title
      , c.introtext AS body
      , c.state AS state
      , c.created AS created
      , c.created_by AS uid
      , c.modified AS modified
      , c.modified_by AS modified_uid
      , c.published AS published
      , c.published_by AS published_uid
      , jc.title AS category
    FROM  jos_content AS c
    INNER JOIN jos_categories AS jc 
    ON c.sectionid = jc.section and c.sectionid=4
    gorup by c.sectionid

JOIN returns too many results

2 Answers2