SQL Join question

Question

I have 3 tables

1. Links
   Link ID
   Link Name
   GroupID (FK into Groups)
   SubGroupID (FK into Subgroups)
   
   

2. Groups
   GroupID
   GroupName
   

3. SubGroup
   SubGroupID
   SubGroupName
   GroupID (FK into Groups)
   

Every link needs to have a GroupID but teh SubGroupID is optional. How do i write a SQL query to show:

Links.LinkName, Groups.GroupName, SubGroup.SubGroupName

For the records with no subgroup just put a blank entry in that field. If i have 250 link rows, i should get back 250 reecords from this query.

Is there a way to do this in one query or do i need to do multiple queries?

Answer 1

SELECT 
  links.linkname
  , groups.groupname
  , subgroup.groupname
FROM
  links 
  JOIN groups ON links.groupid = groups.groupid
  LEFT OUTER JOIN subgroups ON links.subgroupid = subgroup.subgroupid

(re-added to address OP's question) incidentally, why not keep groups and subgroups in the same table, and use a self-referential join?

Akantro:

You'd have something like this: create table groups( groupid integer primary key, parentgroupid integer foreign key references groups (groupid), groupname varchar(50))

your query would then be

SELECT 
  links.linkname
  , groups.groupname
  , SUBGROUPS.groupname
FROM
  links 
  JOIN groups ON links.groupid = groups.groupid
  LEFT OUTER JOIN groups SUBGROUPS ON links.subgroupid = subgroup.groupid

there's no functional difference to keeping the tables like this, but the benefit is you only have to go to one place to edit the groups/subgroups

Answer 2

This assumes that there is at most only 1 subgroup per group. if there are more, then you have the potential to get additional records.

select links.linkname, groups.groupname, subgroup.subgroupname
from links
  inner join groups on (links.groupid = groups.groupid)
  left outer join subgroup on (links.subgroupid = subgroup.subgroupid)

Answer 3

You're not too clear, but I think you want to get all rows including those that don't have a correspondent in the SubGroup table.

For this you can use LEFT JOIN, it will fetch NULLs if there are no matching rows.

Answer 4

SELECT Links.LinkName, Groups.GroupName, SubGroup.SubGroupName -- Will potentially be NULL
FROM Links
INNER JOIN Groups
    ON Group.GroupID = Links.GroupID
LEFT JOIN SubGroup
    ON SubGroup.SubGroupID = Links.SubGroupID

Answer 5

You would use an Outer Join:

select Links.LinkName, Groups.GroupName, SubGroup.SubGroupName
from Links 
inner join Groups on Groups.GroupID = Links.GroupID
left outer join SubGroup on Links.SubGroupID = SubGroup.SubGroupID

Answer 6

Just use a LEFT OUTER JOIN on the SubGroup table like:

select
    l.LinkName,
    g.GroupName,
    s.SubGroupName
from
    Links l
'
    JOIN Group g
       on ( g.GroupId = l.GroupId)
'
    LEFT OUTER JOIN SubGroup s
       on ( s.SubGroupId = l.SubGroupId )

That should do it.

Ron

Answer 7

SELECT LinkName, GroupName, SubGroupNamne
FROM Links INNER JOIN Groups ON LInks.GroupID = Groups.GroupID
    LEFT JOIN SubGroup ON Links.SubGroupID = SubGroup.SubGroupID

This will include rows that do not have a sub group. That column will simply be NULL.

Answer 8

select L1.LinkName, G1.GroupName, NVL(S1.SubGroupName,' ')
 from Links L1, Groups G1, SubGroup S1 
where L1.GroupID = G1.GroupID and
      L1.GroupID = S1.GroupID

Answer 9

Okay, try:

select a.linkname, b.groupname, c.subgroupname
from links a, groups b, subgroup c
where a.groupid = b.groupid
and a.subgroupid = c.subgroupid
and a.subgroupid is not null
union all
select a.linkname, b.groupname, '  '
from links a, groups b
where a.groupid = b.groupid
and a.subgroupid is null

I think that should work (it does in DB2 which is the DBMS I use most) - you'll need to adjust the spaces in the second select to match the subgroup.subgroupname size.

Answer 10

Use a LEFT OUTER JOIN on the SubGroup table will give you all rows from the Links table and where a SubGroup exists will return that otherwise you see a NULL value.

SELECT L.LinkName, G.GroupName, S.SubGroupName
 FROM Links As L
  INNER JOIN Groups As G ON L.GroupID=G.GroupID
  LEFT OUTER JOIN SubGroup S ON L.SubGroupID=S.SubGroupID

This does not check that your SubGroups.LinkID matches the Links.LinkID which should never happen but if you need to check this then add in another clause to the join:

SELECT L.LinkName, G.GroupName, S.SubGroupName
 FROM Links As L
  INNER JOIN Groups As G ON L.GroupID=G.GroupID
  LEFT OUTER JOIN SubGroup S ON L.SubGroupID=S.SubGroupID AND L.GroupID=S.GroupID