• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

out putting a table but putting null in the rows where the attributes are not listed. (outer join)

 
bhavin ragha
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i have to use a left outer join to list the band_ids, band_names and their members details. right now i just put *.
below is the sql, and it works. but only displays the bands that have members in it.
i want it to show all the bands and if they dont have members then put null in the rows

explain a little abot the tables.
bands(band_id, band_name) represents a list of bands

band_members(band_id, name, dob, country, sex) represents the members of the
bands in relation bands
(there are 9 bands and only 7 of them have members in them)

ok now the condition also states the if no members listed in the relation then the rows should still be listed but with null values for the band members details.
how do I show the other rows where the band_id, in the bands table in not in the band_members table

SELECT *
FROM bands
left join band_members
where band_members.band_id = bands.band_id
 
bhavin ragha
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ok i have just found out if i use on instead of where then all the data is listed. but how do i put null in the rows where there is no data
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
you are missing the word OUTER
this page looks quite good http://db.apache.org/derby/docs/10.2/ref/rrefsqlj18922.html
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic