CODEWITHSUNDEEP
mysql
user8680654
user8680654

Reputation:

mySQL query is not working properly

What's wrong with my query:

SELECT
    Articles.Name, 
    GroupOfArticles.Name, 
    (select GroupOfArticles.Name from GroupOfArticles
     where GroupOfArticles.ParentGroup= Articles.Group)
FROM Articles
JOIN GroupOfArticles
    ON Articles.Group = GroupOfArticles.ID

I want to show the name of the article and the name of the most basic group in the hierarchy...

databasemodel

Upvotes: 1

Views: 62

Answers (2)

Peter Abolins
Peter Abolins

Reputation: 1539

Obviously I haven't seen your schema, but I don't believe you need the sub-select at all. It appears that it would return the same as GroupOfArticles.Name does?

SELECT 
   Articles.Name, 
   GroupOfArticles.Name 
FROM 
    Articles JOIN GroupOfArticles 
        ON Articles.Group = GroupOfArticles.ID 
            AND GroupOfArticles.ParentGroup = Articles.Group

Actually, the above assumes that every article returned will be part of a specific group, and will also have a parent group which is the same. Inception?

EDIT Based on more requirements / information, here is a second version:

SELECT DISTINCT
    a.Name, 
    g.Name 
FROM 
    GroupOfArticles g INNER JOIN GroupOfArticles g1 
        ON g1.ParentGroup = g.ID
        INNER JOIN Articles a ON
            CASE 
                WHEN g.ParentGroup IS NULL THEN g.ID
                ELSE g1.ID
            END

I also created an SQLFiddle for the above.

EDIT 2 An alternative version which doesn't include JOIN OR CASE:

SELECT DISTINCT
    a.Name, 
    g.Name 
FROM 
    Articles a, GroupOfArticles g, GroupOfArticles g1
WHERE 
        g.ID <> g1.ID 
    AND g.ParentGroup IS NULL 
    AND 
    ( 
           a.Group = g.ID
        OR a.Group = g1.ID AND g1.ParentGroup = g.ID
    )

SQLFiddle

Note that in the two latest queries, you could replace the DISTINCT keyword with GROUP BY a.Name HAVING COUNT(*) > 0, If you really must use those constructs as well. Just remove DISTINCT from the top, and add the GROUP BY ... at the end.

Upvotes: 0

Carsten Massmann
Carsten Massmann

Reputation: 28196

You need to JOIN the subquery outside of the actual subquery like

SELECT a.Name, g.Name, 
FROM Articles a
INNER JOIN (select Name,ParentGroup from GroupOfArticles) g
   ON g.ID         =a.Group
  AND g.ParentGroup=a.group

The actual query shown can even be done without the subquery, like

SELECT a.Name, g.Name, 
FROM Articles a
INNER JOIN GroupOfArticles g
   ON g.ID         =a.Group
  AND g.ParentGroup=a.group

Although I doubt very much that you really want to join on both columns: ID and ParentGroup?

Upvotes: 1

Related Questions