sql query question

vahidreza · #1 (**permalink**) 06-01-09, 16:49

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 ?

gvee · #2 (**permalink**) 06-01-09, 19:37

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

vahidreza · #3 (**permalink**) 06-02-09, 02:11

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 !

r937 · #4 (**permalink**) 06-02-09, 07:21

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

vahidreza · #5 (**permalink**) 06-03-09, 02:37

thanx, it seems to work .

Internet Services

Web Development

Digital Marketing

Consumer Tech