By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,482 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Query and 1 to many relationship

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.