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

Google "outer join":

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
Then read this, then this, then this

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

Thanks to all. Just needed a few pointers. :-) Both ideas work.