match 2 tables from 1 table

[Trainingjason]

I have a problem whereby I have the following relationships

table a, table b, table c

table a has a one to many relationship with table b and c.

I want to return records where they match both b or c.

Help and how. I can match the records where the data is in both a, b and c but not
a and (b or c)

any advise greatly appreciated

jason

 

[benjamenus]

You could try this: there may be a more efficient way of achieving the same thing - please let me know if it helps at all.

select table_a.*
where table_a.unq_id in
(select unq_id
from table_b)
or table_a.unq_id in
(select unq_id
from table_c)

I'm assuming that all tables are link on the unq_id field. This possibly will return duplicate records if it exists in both b and c, in which case use SELECT DISTINCT.

 

[Fibee]

Hi Jason
Another way I have found sucessful is to change to SQL view and try changing any 'Inner Joins' to Left or Right Joins and see what happens!
Fi

 

[Trainingjason]

Thank you I can not tell you until Friday if it has worked

Jason

 

[rac2]

Hello Jason,

You might try a number of queries. The condition you describe, that the entity have entries in all three tables, is the condition that would be met by INNER JOINs like the following.

SELECT cc.person_id, cc.firstname, p.person_id, p.number, a.person_id, a.city
FROM CustomerContacts cc
JOIN PhoneNumbers p ON cc.person_id = p.person_id
JOIN Addresses a ON p.person_id = a.person_id
ORDER BY 1

Only rows for person_id's present in all three tables would be returned. If a person has two phone numbers then two rows would be returned.

Here are some other queries based on the same three tables. You might want to create the tables with sample data and run the queries. Be sure to leave out phone numbers for some persons and leave out addresses for others. Observe the number of rows returned and which rows are left out by each query.

/*Thnx to benjamenus*/
SELECT cc.person_id, cc.firstname
FROM CustomerContacts cc
WHERE cc.person_id IN ( SELECT person_id FROM PhoneNumbers )
   OR cc.person_id IN ( SELECT person_id FROM Addresses )
ORDER BY 1

SELECT cc.person_id, cc.firstname,
      (SELECT TOP 1 number
       FROM PhoneNumbers 
       WHERE person_id = cc.person_id),
      (SELECT TOP 1  city
       FROM Addresses
       WHERE person_id = cc.person_id)
FROM CustomerContacts cc
ORDER BY 1

SELECT cc.person_id, cc.firstname, p.person_id, p.number
FROM CustomerContacts cc
JOIN PhoneNumbers p ON cc.person_id = p.person_id
ORDER BY 1

SELECT cc.person_id, cc.firstname
FROM CustomerContacts cc
ORDER BY 1

Have fun,
rac2

PS, Are you an instructor who is training Jason; an instructor named TrainingJason; or a person in training named Jason who is looking for an instructor?

 

[Trainingjason]

I am a trainer and have an aol account called trainingjason which is a parody on my profession.

jason

 

[hoym]

Can any body please tell me how can I open the design panel of a specified Query instead of running the Query itself by pressing an control in a Startup form.


Thanks !


hoym@netvigator.com