Is it possible to create a sql statement for this ?

  • Thread starter Thread starter Fie Fie Niles
  • Start date Start date
F

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.
 
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.
 
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
 
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.
 
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.
 
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
 
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


Van T. Dinh said:
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)


Fie Fie Niles said:
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.
 
Back
Top