Is it possible to create a sql statement for this ?

[Fie Fie Niles]

I have a table with 3 columns: SUBJECT_KEY, TOPIC_KEY, FIELD_KEY, DTEXT .
For example:
SUBJECT_KEY TOPIC_KEY FIELD_KEY DTEXT
100 1 10
Microsoft
100 1 10
Dell
100 1 12
Computer

200 1 10
Compaq
200 1 12
Computer

300 1 10
IBM

I need to create a sql statement from the table so that it returns me
SUBJECT_KEY where (DTEXT = 'Microsoft' or 'Dell')
AND
(DTEXT = 'Computer')

In my example above, the sql statement will return SUBJECT_KEY = 100

Is it possible to create a sql statement to do that, and how ?
Thank you very much.

 

[Lynn Trapp]

Not with an AND. You can do it with another OR.

Select Subject_Key
From YourTable
Where DTEXT = "Micorsoft" OR DTEXT = "Dell" OR DTEXT = "Computer"

Add the DISTINCT key word if you want to only return the single value.

 

[Tonín]

SELECT DISTINCT MyTable.SUBJECT_KEY
FROM MyTable INNER JOIN MyTable AS MyTable_1 ON MyTable.SUBJECT_KEY =
MyTable_1.SUBJECT_KEY
WHERE (MyTable.DTEXT="Microsoft" OR MyTable.DTEXT="Dell") AND
MyTable_1.DTEXT="Computer";

Not totally sure about what kind of query do you need, but hope being
helpful.


Tonín
Spain

 

[Fie Fie Niles]

Thanks a lot.
Your query works.
But, I could not get it to work if I need to do more then 1 AND.
For example:
SUBJECT_KEY TOPIC_KEY FIELD_KEY DTEXT
100 1 10
Microsoft
100 1 10
Dell
100 1 12
Computer
100 1 13
Wireless

200 1 10
Compaq
200 1 12
Computer

300 1 10
IBM

I need to create a sql statement from the table so that it returns me
SUBJECT_KEY where ((DTEXT = 'Microsoft' or 'Dell') and data1.eei_key = 10))
AND ((DTEXT = 'Computer') and data1.eei_key = 12)
AND ((DTEXT = 'Wireles') and data1.eei_key = 13)

When I did the following statement, I got an error:
SELECT DISTINCT MyTable.SUBJECT_KEY
FROM MyTable
INNER JOIN MyTable AS MyTable_1 ON MyTable.SUBJECT_KEY =
MyTable_1.SUBJECT_KEY
INNER JOIN MyTable AS MyTable_2 ON MyTable.SUBJECT_KEY =
MyTable_2.SUBJECT_KEY
WHERE
(MyTable.DTEXT="Microsoft" OR MyTable.DTEXT="Dell")
AND MyTable_1.DTEXT="Computer"
AND MyTable_2.DTEXT="Wireles'"

Is my syntax wrong, or can I do multiple INNER JOIN in Access ?

Thank you.

 

[Van T. Dinh]

I don't think you need Joins at all. See Lynn Trapp's answer.

BTW, in your original post, you wrote 3 Columns then gave 4 names and the
sample data also showed 4 names. I assume the Field [eei_key] comes from
the same Table.

My interpretation of your criteria is:

SELECT DISTINCT SUBJECT_KEY
FROM MyTable
WHERE
( ( (DTEXT = 'Microsoft') OR (DTEXT = or 'Dell') ) AND (eei_key =
10) )
OR ( (DTEXT = 'Computer') AND (eei_key = 12) )
OR ( (DTEXT = 'Wireless') AND (eei_key = 13) )

If you are typing into the SQL View, watch out for typing mistakes. You had
"Wireles" in you SQL String.

 

[Tonín]

Again, hardly sure about your criteria and reasons for that sort of queries
)). Please, check Van T. Dinh's answer and evaluate what are you really
intending to get. In fact, I probably misunderstood your purposes. If don't,
try this one about that syntax error you got:

SELECT DISTINCT MyTable.SUBJECT_KEY
FROM (MyTable INNER JOIN MyTable AS MyTable_1 ON MyTable.SUBJECT_KEY =
MyTable_1.SUBJECT_KEY) INNER JOIN MyTable AS MyTable_2 ON
MyTable.SUBJECT_KEY = MyTable_2.SUBJECT_KEY
WHERE (MyTable.DTEXT="Microsoft" Or MyTable.DTEXT="Dell") AND
MyTable_1.DTEXT="Computer" AND MyTable_2.DTEXT="Wireless";

(Just a comment: I guess you will be more comfortable creating this queries
in standard design view (not SQL view, I mean). I ALWAYS do it!. That avoids
most of the typographic errors, the syntax errors, and it is quick, visual
and easy-to-use).

If 1st query OK, then the complete query you are trying could be this one,
assuming FIELD_KEY was your new "data1.eei_key" field, and assuming
FIELD_KEY is integer, not text (if text, then replace 10 into "10", 12 into
"12" and so on):

SELECT DISTINCT MyTable.SUBJECT_KEY
FROM (MyTable INNER JOIN MyTable AS MyTable_1 ON MyTable.SUBJECT_KEY =
MyTable_1.SUBJECT_KEY) INNER JOIN MyTable AS MyTable_2 ON
MyTable.SUBJECT_KEY = MyTable_2.SUBJECT_KEY
WHERE (MyTable.DTEXT="Microsoft" Or MyTable.DTEXT="Dell") AND
MyTable.FIELD_KEY=10 AND MyTable_1.DTEXT="Computer" AND
MyTable_1.FIELD_KEY=12 AND MyTable_2.DTEXT="Wireless" AND
MyTable_2.FIELD_KEY=13;



Best regards


Tonín
Spain

 

[Tonín]

I get your point, but I think the problem to solve is similar to this one:

Let's suppose we have a table like this table following

People PlaceVisited
Tom New York
Tom Tokyo
John New York
Ann Tokyo

Who have been in both New York an Tokyo cities? Answer: Tom.

If "Fie Fie Niles" is not intending to do something similar to this, then I
totally agree with your answer. If don't, then to perform a query for that
result, I think is possible:
1. The "Inner Join" + "Distinct" thing
2. No "Inner Join", but a pair of "DLookups" joint by an "And" (slightly
more complicated)
3. No "Inner Join", but Subqueries (¿?) (more complicated even)
4. No "Inner Join", but ...

[Sure I'm wrong. I'm totally newbie in SQL language (and, BTW, in English
language). I would appreciate all corrections and alternatives suggested.]


Tonín
Spain

I don't think you need Joins at all. See Lynn Trapp's answer.

BTW, in your original post, you wrote 3 Columns then gave 4 names and the
sample data also showed 4 names. I assume the Field [eei_key] comes from
the same Table.

My interpretation of your criteria is:

SELECT DISTINCT SUBJECT_KEY
FROM MyTable
WHERE
( ( (DTEXT = 'Microsoft') OR (DTEXT = or 'Dell') ) AND (eei_key =
10) )
OR ( (DTEXT = 'Computer') AND (eei_key = 12) )
OR ( (DTEXT = 'Wireless') AND (eei_key = 13) )

If you are typing into the SQL View, watch out for typing mistakes. You had
"Wireles" in you SQL String.

--
HTH
Van T. Dinh
MVP (Access)

Thanks a lot.
Your query works.
But, I could not get it to work if I need to do more then 1 AND.
For example:
SUBJECT_KEY TOPIC_KEY FIELD_KEY DTEXT
100 1 10
Microsoft
100 1 10
Dell
100 1 12
Computer
100 1 13
Wireless

200 1 10
Compaq
200 1 12
Computer

300 1 10
IBM

I need to create a sql statement from the table so that it returns me
SUBJECT_KEY where ((DTEXT = 'Microsoft' or 'Dell') and data1.eei_key = 10))
AND ((DTEXT = 'Computer') and data1.eei_key = 12)
AND ((DTEXT = 'Wireles') and data1.eei_key = 13)

When I did the following statement, I got an error:
SELECT DISTINCT MyTable.SUBJECT_KEY
FROM MyTable
INNER JOIN MyTable AS MyTable_1 ON MyTable.SUBJECT_KEY =
MyTable_1.SUBJECT_KEY
INNER JOIN MyTable AS MyTable_2 ON MyTable.SUBJECT_KEY =
MyTable_2.SUBJECT_KEY
WHERE
(MyTable.DTEXT="Microsoft" OR MyTable.DTEXT="Dell")
AND MyTable_1.DTEXT="Computer"
AND MyTable_2.DTEXT="Wireles'"

Is my syntax wrong, or can I do multiple INNER JOIN in Access ?

Thank you.