AND/OR SQL help

[Babray]

Hi!

Maybe someone can point me in the right direction, suggest a source or
something.

Here's the existing query:
<cfquery name="listall" datasource="mmdb">
select distinct m.met_id,m.metname
from MineralDesc m,
M_Keyword k
where m.met_id = k.met_id
and k.key_id in ( 0#form.keywords# )
order by metName asc
</cfquery>

Form.keywords returns a comma list such as (80,604) and this does a wonderful
job of performing an OR search, which I do need and am grateful to have
working. I also need to transform this into an AND search so that BOTH 80 AND
604 are true on the final listings. The list can also be 1 or more values.

Also, I need to append another value either to the list or onto the end of the
query.

Any guidance, assistance or ideas will be very muchly appreciated, and thanks
in advance. Information on this type thing seems to be sparse and cryptic at
best or aimed at AND-ing from different fields.

Thanks,
B

[paross1]

Think about it. How will your query ever return any rows where k.key_id = 80 AND k.key_id = 604 at the same time? For any row in your table, the k.key column will have only one value, right?

Phil

[Babray]

Nope, these are not unique values. There are 3 tables, 1) L_Keywords with a
unique value that is the same as the k.met_id value, but that is nbot the
indexing value for the M_Keyword table. The designer I had working on this some
time back just by passed the first table since the value was identical. I
borrowed his query since the long one I did had a syntax error somewhere. So,
in reality, if the query works the way I want it to there would be 9 values
returned and currently there are 33 since it is the OR plus not being able to
figure out how to get the other value in there.

Frankly, the long way is clearer to me, but I see why it works and have no
problems with it.

Hope that helps!
B

[paross1]

So, what you are saying is that for any SINGLE row in the M_Keyword table, the
met_id column may have 9 values (or 33)? If that is true, you have a very,
very, bad design on your hands, and good luck with that one.

Phil

[paross1]

Are you sure that you are not really looking for an OR condition?

These two queries are equivalent:

select distinct m.met_id,m.metname
from MineralDesc m,
M_Keyword k
where m.met_id = k.met_id
and k.key_id in (80,604)

--and--

select distinct m.met_id,m.metname
from MineralDesc m,
M_Keyword k
where m.met_id = k.met_id
and (k.key_id = 80 OR k.key_id = 604)

So, if you want results where key_id is 80 AND 604 you actually need an OR
condition in the query, assuming of course that the key_id field has only one
value PER ROW.

Perhaps I just do not understand what you are trying to ask for.

Phil

[Babray]

Actually, I got the design from several books! Here's is the deal. L_Keywords
is a large "lookup table with the unique value id that is the same as the
non-unique value key_id in M_Keyword. The third table is MineralDesc with the
unique value met_ID which matches to the non-unique value in M_Keyword met-id.
M_keyword does have a unique key value that for all practical purposes is not
used except to keep MS Access happy.

There "should" be a way to loop or something the AND... but what do I know!
LOL... I'm still trying to get the darned thing to work!

[paross1]

Sorry, but (to me at least) you are not explaining what you are actually trying
to do differently than what you are already doing. Plus, what the heck does
L_Keywords have to do with anything, since you are not joining that table in
your query?

Phil

[Babray]

According to "Sam's Teach Yourself SQL" it is AND... "A keyword used in a WHERE
clause to specify that only rows matching all the specified conditions should
be retrieved," AND what you said at the end is right I wnat BOTH/all
conditions met. What I cannot figure out is how to get the AND in the middle of
the k.key_id in (0#form.keywords#).

And, THANKS for answering Phil, more than you know! I'm practically bald over
this!

Betty

[paross1]

....and what I'm trying to explain to you is that you will get no rows returned
if you make this an AND condition. It is an OR even though you are saying that
"I want the results if key_id equals this and this and this.....etc." The SQL
must be OR, unless key_id is actually a LIST of values in a single field that
ALL must be true. However, it actually looks to me like a single foreign key
value to L_Keywords. Don't know how else to explain it, sorry. (Unless you are
leaving out something important.)

Phil

[Babray]

Sorry about the L_Keywords... that table is used on the prior page to display
choices to the user. Since the value is the same as the M_Keyword table the
query above just ignores it and uses 2 tables not 3.

As for being the same, it isn't... the query as written returns values where
all minerals that meet either of the conditions are returned rather than only
the minerals that meet BOTH conditions. Now you see the problem... the only
examples I can find deal with AND/OR of different names rather than different
rows in the same table.

[Babray]

GOT IT! You finally hit the nail on the head with the fact that SQL will not do
this... strange, but I am good at accepting what I cannot change! Now to the
next question... you said. "However, it actually looks to me like a single
foreign key value to L_Keywords." Can you see a way around this? There is
bound to be a way around this issue even if I have to re-work the darned thing!

[paross1]

Ding! I think that I finally get what you are asking for. I think that the
query below may give you the results that you want, at least for two values,
but as you can see, it gets complicated with more parameters, as each requires
its own sub select. Let me sleep on it and see if I can come up with a simpler
solution, as I came up with this one after a couple glasses of wine this
evening!

select distinct m.met_id, m.metname
from MineralDesc m
where exists(select 1
from M_Keyword k
where m.met_id = k.met_id
and k.key_id = 80)
and exists(select 1
from M_Keyword k
where m.met_id = k.met_id
and k.key_id = 604)

Sorry, it took me a while to fully understand wht you were actually trying to
do.

Phil

[Babray]

Thank you!!! I'll give it a try tomorrow, probably early afternoon! I really appreciate your sticking with this!!!
Betty

[rmorgan]

Damn!! I have never thought of a statement like that before, sorry I am not offering any help, but just had to comment on the ingenuity of the statement.

[Babray]

:D

YOU ARE AMAZING!!!!! WONDERFUL!!!! TERRIFIC!!!! That IS IT!!! It even picked
up a couple of glitches in my data entry!

Now, just so I do not make it more difficult that it should be, how do I go
from there to dynamic? More simply put, how do I get the values chosen by the
user on the previous page into the query in place of the 80 and 604. I've gone
many places, thanks to books and forums but this is not one of those places!
Thanks again Phil!!!!!!
Betty

[paross1]

If you just want to substitute the two constants with variables, then capture
the values from your input page in FORM or URL variables, and then use the
variable names in the query.

If you want to make it truly dynamic in the sense that you may have more than
two parameters, then it gets very intresting. You are going to have to make the
number of sub-selects created in your query dynamic as well, and dependent upon
the number of parameter values passed. I'm sorry, but I don't have that sort of
spare time to devote to doing this for you, but it will involve looping and
building the sql statement dynamically.

Best of luck to you.

Phil

[mxstu]

You maybe be able to use the HAVING clause. In the example below, the two
tables are joined and a "count" of the matching records is calculated. The
HAVING clause is used to filter the results and return only those items where
the number of matches equals the number of "form.keyword" items (ie. matches
all of the keywords).

Using the HAVING clause does not always provide the best performance, for
tables containing a large amount of data, because the database must compute a
COUNT(*) for all records first before evaluating the HAVING expression.
However, it may be a possibility for smaller tables. Check your database
documentation for more information about the HAVING clause.

Two important notes about the attached example: A) since the example below is
not counting DISTINCT matches, if your "M_Keyword" table contains duplicates,
the query may return incorrect results. B) If the "form.keyword" list contains
blank entries (ex. 80,,,604) or duplicates (ex. 80,604,80) the query may also
return incorrect results



<!---Note: Query returns ALL records if form.keywords list is empty --->
<cfset form.keywords = "80,604">
<cfquery name="listall" datasource="yourDSN">
SELECT m.Met_ID, m.MetName, COUNT(*) AS KeyWordCount
FROM MineralDesc m, M_Keyword k
WHERE m.Met_ID = k.Met_ID
<cfif ListLen(form.keywords) GT 0>
AND k.Key_ID IN (#form.keywords#)
</cfif>
GROUP BY m.Met_ID, m.MetName
<cfif ListLen(form.keywords) GT 0>
HAVING COUNT(*) = #ListLen(form.keywords)#
</cfif>
ORDER BY m.MetName
</cfquery>