467,081 Members | 969 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,081 developers. It's quick & easy.

Query and 1 to many relationship

This must be simple but I can't figure it out. Table 1 1xM to table 2:
Table 1 IDfield
1
2
Table 2 IDfield CriteriaField
1 Criteria value 1
2 Criteria value 1
2 Criteria value 2

For example I need to pull out every IDvalue of table 1 that have both
Criteria value 1 and Criteria value 2 linked to them. In this case IDvalue 2
matches that criteria. In my form I'd like to give the user the option to
choose as many Criteria values for the query as he wants.

How should I configure this query? How should I go about this?
Thanks in advance,
john
Feb 8 '07 #1
  • viewed: 2437
Share:
2 Replies
John,

I believe you're going to have to build a dynamic query definition based on
the following structure:

select *
from Table1
where
exists
(select * from Table2 where Table1.IDField = Table2.IDField and
CriteriaField = 'Criteria Value 1')
and exists
(select * from Table2 where Table1.IDField = Table2.IDField and
CriteriaField = 'Criteria Value 2')

-- Bill

"john" <jo**@test.comwrote in message
news:Wq*********************@casema.nl...
This must be simple but I can't figure it out. Table 1 1xM to table 2:
Table 1 IDfield
1
2
Table 2 IDfield CriteriaField
1 Criteria value 1
2 Criteria value 1
2 Criteria value 2

For example I need to pull out every IDvalue of table 1 that have both
Criteria value 1 and Criteria value 2 linked to them. In this case IDvalue
2 matches that criteria. In my form I'd like to give the user the option
to choose as many Criteria values for the query as he wants.

How should I configure this query? How should I go about this?
Thanks in advance,
john

Feb 9 '07 #2
Thanks. I kinda was afraid of that...
john

"AlterEgo" <al********@dslextreme.comschreef in bericht
news:12*************@corp.supernews.com...
John,

I believe you're going to have to build a dynamic query definition based
on the following structure:

select *
from Table1
where
exists
(select * from Table2 where Table1.IDField = Table2.IDField and
CriteriaField = 'Criteria Value 1')
and exists
(select * from Table2 where Table1.IDField = Table2.IDField and
CriteriaField = 'Criteria Value 2')

-- Bill

"john" <jo**@test.comwrote in message
news:Wq*********************@casema.nl...
>This must be simple but I can't figure it out. Table 1 1xM to table 2:
Table 1 IDfield
1
2
Table 2 IDfield CriteriaField
1 Criteria value 1
2 Criteria value 1
2 Criteria value 2

For example I need to pull out every IDvalue of table 1 that have both
Criteria value 1 and Criteria value 2 linked to them. In this case
IDvalue 2 matches that criteria. In my form I'd like to give the user the
option to choose as many Criteria values for the query as he wants.

How should I configure this query? How should I go about this?
Thanks in advance,
john


Feb 10 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Simon Withers | last post: by
2 posts views Thread by Fendi Baba | last post: by
4 posts views Thread by pmacdiddie@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.