Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    9

    Question Unanswered: sql query question

    Hi , to describe what i am looking for , please take a look to these 3 tables :

    groups
    =========
    g_id,g_name

    articles
    =========
    a_id,a_name

    rels
    =========
    r_id,g_id,a_id

    now i need a query to return :
    g_name - a_name

    if there is no article for any group, then a_name would be null

    i tried select from groups and rels tables, but my result set doesn't contain groups that have no articles in rels table !

    any idea ?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    First off, the rels table doesn't need r_id - a composite key of g_id and a_id will suffice

    Ok, so you want to know all groups that have no articles assigned, right?
    Code:
    SELECT groups.g_id
         , groups.g_name
    FROM   groups
     LEFT
      JOIN rels
        ON rels.g_id = groups.g_id
    WHERE  rels.g_id IS NULL
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2009
    Posts
    9
    Hi,

    thanx gvee for you answer but i think i couldn't make my question clear :
    the result i want is a select for columns g_name And a_name between these tables.

    like this :
    select g_name,a_name from rels r left join groups g on g.g_id = r.g_id left join articles a on a.a_id = r.a_id

    the problem in this sql query is : it doesn't return groups that have no articles
    , i need such query to return all groups while there is no article !

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT g_name
         , a_name 
      FROM groups AS g
    LEFT OUTER
      JOIN rels AS r 
        ON r.g_id = g.g_id 
    LEFT OUTER
      JOIN articles AS a 
        ON a.a_id = r.a_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Posts
    9
    thanx, it seems to work .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •