Indication if value exists in a related table

[Armen Stein]

Hello,

I have a problem writing a certain query and I'd
appreciate any help.

I have a table called books which has 2 fields
(for this example): book_id and book_title where
book_id is the key. I also have a table called
people which also has 2 fields: person_id and
person_name where person_id is the key.

A person can be associated with more than one book
and each book can be associated with more than one
person, this is saved in a table called
person_books which has 2 fields: person_id and
book_id which are both the key.

I need to create a query which selects all the
books from the books table and gives me an
indication in one of the select fields if person X

Check out the missing records query wizard.

Basically, if you join Book to PersonBook, and point the join arrowhead
toward PersonBook, the PersonID will have a value in a row for each
Person associated with that Book. But if the PersonID is Null, then
there are no People associated with that Book.

Then, if you want to narrow it down to a specific Person, you can apply
a criteria of PersonID = [somevalue] or IsNull(PersonID). This will
give you every Book and whether that Person is associated with it.

There is no need for a Union query at all.

 

[Dan]

Hello,

I have a problem writing a certain query and I'd
appreciate any help.

I have a table called books which has 2 fields
(for this example): book_id and book_title where
book_id is the key. I also have a table called
people which also has 2 fields: person_id and
person_name where person_id is the key.

A person can be associated with more than one book
and each book can be associated with more than one
person, this is saved in a table called
person_books which has 2 fields: person_id and
book_id which are both the key.

I need to create a query which selects all the
books from the books table and gives me an
indication in one of the select fields if person X
has that book associated with him in the
person_books. I have no idea how to add this
indication, I thought about using a union in which
one select will select fielda,fieldb,1 if exists
(select from person_books where person_id=X) and
the other select will do the same but with not
exists and select the value 0 instead of 1.

I hope there's a better way to approach it. I
appologize if I wrote too much or wasn't clear in
any way. I'd still appreciate any help

Thank you
Dan

 

[Dan]

Hi,

Thank you for your quick reply!

Basically, if you join Book to PersonBook, and
point the join arrowhead
toward PersonBook, the PersonID will have a
value in a row for each
Person associated with that Book. But if the
PersonID is Null, then
there are no People associated with that Book.

Indeed. A very simple left join solved my problem.
I really need to read a sql book...

Thank you, again
Dan