A complicated SQL statement

  • Thread starter Thread starter Mota
  • Start date Start date
M

Mota

Hello;
I have a table (DistributorsTBL) having a Primary Key(RecordID) and a NON
uinque field named dID and some other fields.I want to select all fields of
records having a same dID (records having repeating dIDs) and at the same
time exist in SqlDrugsWeNeed.
Suppose that we have 3 records having dID=1,one record having dID=2,five
records having dID=3,and 1 record having dID=4.Now i want to select records
having dID=1 and records having dID=3 (repeating dIDs,and records dID=2 and
dID=4 not be selected) in condition that they exist in SqlDrugsWeNeed.
Assuming records of dID=1 and dID=3 are returned by SqlDrugsWeNeed,the
requested SQL must return 8 records (3+5).Any suggestions?
Thank you in advance for your help.
 
Thank you for ur attention;
But,i dont know,or at least impossible to write from 2000 records,the number
of repeating dID.1 and 3 was an example and there is many many other records
having a common dID.Such as dID=825,dID=1563,dID=96 and so on that have more
than one record in table and i have to select all of them.Any solution?
Thank you.

Graham Mandeno said:
Hi Mota

How about something like this:

Select * from DistributorsTBL
where (dID in (select dID from SqlDrugsWeNeed))
and (dID in (1,3))

You could probably also do it with an inner join between DistributorsTBL and
SqlDrugsWeNeed, but I would need to know more about the latter query.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Mota said:
Hello;
I have a table (DistributorsTBL) having a Primary Key(RecordID) and a NON
uinque field named dID and some other fields.I want to select all fields of
records having a same dID (records having repeating dIDs) and at the same
time exist in SqlDrugsWeNeed.
Suppose that we have 3 records having dID=1,one record having dID=2,five
records having dID=3,and 1 record having dID=4.Now i want to select records
having dID=1 and records having dID=3 (repeating dIDs,and records dID=2 and
dID=4 not be selected) in condition that they exist in SqlDrugsWeNeed.
Assuming records of dID=1 and dID=3 are returned by SqlDrugsWeNeed,the
requested SQL must return 8 records (3+5).Any suggestions?
Thank you in advance for your help.
Click to expand...
Click to expand...
 
Hello;
I have a table (DistributorsTBL) having a Primary Key(RecordID) and a NON
uinque field named dID and some other fields.I want to select all fields of
records having a same dID (records having repeating dIDs) and at the same
time exist in SqlDrugsWeNeed.
Click to expand...

If dID is unique in SqlDrugsWeNeed you can separate the problem:
getting those that exist in SqlDrugsWeNeed is easy, just create a
Query joining the two tables on dID.

To limit the results to those having repeating dID's, use DCount (I
presume you want this recordset updateable, a subquery will prevent
that). Put in a calculated field

Multi: DCount("*", "DistributorsTbl", "[dID] = " & [dID])

and put a criterion on this field of >1.
 
Thank you so much.

Graham Mandeno said:
Sorry, I misunderstood your question.

Instead of...
and (dID in (1,3))
try:
and (dID in (select dID from DistributorsTBL group by dID having
Count(*)>1))

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Mota said:
Thank you for ur attention;
But,i dont know,or at least impossible to write from 2000 records,the number
of repeating dID.1 and 3 was an example and there is many many other records
having a common dID.Such as dID=825,dID=1563,dID=96 and so on that have more
than one record in table and i have to select all of them.Any solution?
Thank you.

Graham Mandeno said:
Hi Mota

How about something like this:

Select * from DistributorsTBL
where (dID in (select dID from SqlDrugsWeNeed))
and (dID in (1,3))

You could probably also do it with an inner join between
Click to expand...
DistributorsTBL
and
SqlDrugsWeNeed, but I would need to know more about the latter query.

--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand

Return mail address is invalid in a vain attempt to reduce spam.
Feedback is welcome at: (e-mail address removed)
Please post new questions or followups to newsgroup.

Hello;
I have a table (DistributorsTBL) having a Primary Key(RecordID) and
Click to expand...
a
NON
uinque field named dID and some other fields.I want to select all fields
of
records having a same dID (records having repeating dIDs) and at the same
time exist in SqlDrugsWeNeed.
Suppose that we have 3 records having dID=1,one record having dID=2,five
records having dID=3,and 1 record having dID=4.Now i want to select
records
having dID=1 and records having dID=3 (repeating dIDs,and records dID=2
and
dID=4 not be selected) in condition that they exist in SqlDrugsWeNeed.
Assuming records of dID=1 and dID=3 are returned by SqlDrugsWeNeed,the
requested SQL must return 8 records (3+5).Any suggestions?
Thank you in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top