Nested SQL statement isn't working properly.

[erwin001]

I'm using the following Select statement to select information from a table. It works well when I set [Report ID=1].

AND t.[Report ID]=1 And i.[Report ID]=1

I however, want to find report id's that equal both 1 and 2. Ultimately, this will be a dynamic SQL statement, but I need to check literal entries first. I'm having problems constructing this select statement in a way to select reports that me criteria for 1 and 2. Can someone provide some insight. I'm trying to build a nested SQL statement.

SELECT t.[Report ID], f.[Full Field Name]
FROM MasterTBLReport AS t, TBL_FieldID AS f, TBL_Index AS i
WHERE f.[Field ID]=i.[Field ID]
AND t.[Report ID]=1 And i.[Report ID]=1

 

[Golom]

SELECT t.[Report ID], f.[Full Field Name]
FROM MasterTBLReport AS t, TBL_FieldID AS f, TBL_Index AS i
WHERE f.[Field ID]=i.[Field ID]
AND t.[Report ID] IN (1,2) And i.[Report ID] IN (1,2)

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[erwin001]

Thanks Golom, but when I use the above syntax, 36 records are returned. I however, should only return 18 records. (1)has 4 queried records associated with it, while (2) has 14 queried records associated with it. What I need is the 4 unique data elements associated with (1), and the 14 unique data elements asociated with (2) for a total 18 records. With this syntax I get 4 records for (1), and it also attaches the 14 records for mapped to (1). The same is true for (2) for a total 36 records.

 

[Golom]

Can't be sure without seeing the data but what I did was set criteria such that both t.[Report ID] and i.[Report ID] had the values 1 or 2 ... although not requiring that they have the same value at the same time. For example
[tt]
t.[Report ID] i.[Report ID]
1 1
2 1
1 2
2 2
[/tt]
would all qualify. It sounds like you wanted only one of them to be in the range 1,2 (or possibly you wanted them to be equal ... or unequal ... I've no way to know.)

Can you elaborate?

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[erwin001]

Thanks Golom. This is my desired result. If I start out with records in a table as follows:

Records data elements
1 1
1 3
1 4
2 1
2 3
3 1
3 5

I want the result to return only the data elements pertaining to records 1 & 2:

Record data elements
1 1
1 3
1 4
2 1
2 3

I made the following adjustments and the code works properly.

SELECT i.[Report ID], f.[Full Field Name]
FROM TBL_FieldID AS f, TBL_Index AS i
WHERE f.[Field ID]=i.[Field ID]
And ( i.[Report ID]=1
OR i.[Report ID]=2
)
ORDER BY [Full Field Name];

I didn't need the reference to MasterTBLreport as presented in the previous post.

Thanks again.

 

[lespaul]

you can still use the IN (duplicates a string of OR statements):

SELECT i.[Report ID], f.[Full Field Name]
FROM TBL_FieldID AS f, TBL_Index AS i
WHERE f.[Field ID]=i.[Field ID]
And i.[Report ID] IN (1, 2)
ORDER BY [Full Field Name];

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[erwin001]

Excellent lespaul! This indeed functions the way I need it to.