Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007
    Posts
    2

    one-to-many or none relationship

    Hi,

    Just trying to get my head around one-to-many relationships with a twist.

    As an example, I have one entity called "FORMS" which contains a list of forms that our organization uses.

    Similarly, I have another entity called "GUIDES" which contains the guides that the users of the forms need in order to correctly fill out a form.

    So far, I have modeled the FORMS table as follows:

    form_id : PK and sequential number of the form
    form_name: name of the form
    form_link: link on our website as to where to find the form

    I have modeled the GUIDES table as follows:

    form_id: FK connection to FORMS table
    guide_name: name of the guide
    guide_link: link on our website as to where to find the form

    Rule: A "form" may have a number of guides or it may have none.

    For example, the forms table may have 100 forms. But the GUIDES table only has 50 forms of which 46 are unique guides-to-a-form (ie. 1-1) but two forms have 2 guides (ie. 1-many).

    I am quite OK with JOINING the two tables and the result I get is only 50 records long, because if I use the following SQL, it does not show those "forms" that do not have a "guide".

    SELECT * FROM forms f, guides g
    WHERE f.form_id = g.form_id

    However, I want to join the tables to show the forms that have the guide and the forms that do not have a guide. Something like:

    form_id form_name guide_name
    1 application how to apply
    1 application how to apply if you are green
    2 subscribe how to subscribe
    3 old_form
    4 resign how to resign
    etc.

    I think I am have "conceptual failure"...any help would be appreciated.

    Cheers,
    brizrobbo

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Google "outer join":
    Code:
    SELECT * FROM forms f
    LEFT OUTER JOIN guides g
    ON f.form_id = g.form_id

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Your SQL statement form is incorrect.
    You need to apply the correct JOIN between the two tables within your statement depending on the results you need.

    Look at your WHERE clause - it's narrowing your results down to those records whose form_id match (which is not what you want!)

    Try this
    Code:
    SELECT f.form_name
    FROM   forms f
     LEFT
      JOIN guides g
        ON f.form_id = g.form_id
    Then read this, then this, then this
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you really do have a couple of forms which have more than one guide, then you need a third table, a relationship or association table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2007
    Posts
    2
    Thanks to all. Just needed a few pointers. :-) Both ideas 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
  •