Defining a Criteria in a Query

[porto99]

I have been trying to build a query that will display a list of messages. However I have a problem in defining the criteria. Four tables: Messages -> Words -> Fields and if Type in Words = 2 (Enumeration) then Enumerations table.

The case is I have a field which has three value (1 = Scalar, 2 = Enumerated, 3 = Fixed Value). Now for the case of 2 (Enumerated) another table has the enumerated number and string. The Word table has the key to this Enumerated table.

My first attempt at the query produced all the data that was enumerated; the Scalar and Fixed Value were omitted.

I have now broke the link to the Enumerations table and I get all the data; but the enumerations as wrong they are the same for all records; i.e. the first enumerations in the database.

I need a criterial to go an get the enumerations when the Word table field Type = 2 (Enumeration).

But can this be done via a criteria?

Many thanks.

 

[Remou]

I think that the problem is in the join types you are using. For example, this type:
[tt]SELECT tblMessages.Field1, tblEnumerations.Field1
FROM ((tblMessages LEFT JOIN tblWords ON tblMessages.WordID = tblWords.WordID) LEFT JOIN tblFields ON tblWords.FieldID = tblFields.FieldID) LEFT JOIN tblEnumerations ON tblWords.EnumerationID = tblEnumerations.EnumerationID;[/tt]

Will include all the records from the 'higher level' table, whether or not there are matches in the next table. The words table has been joined to both tblFields and tblEnumerations.

 

[lespaul]

see the Join link below for more information on what Remou did and why it works.



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
Understanding SQL Joi