SQL syntax issue

[crux_online]

Hi all,

I'm using CFMX (v6), by the way.

I'm try to cobble together a search page that will take the information input
on a form and search the database using the LIKE operator.

Regardless of whether the DISTINCT keyword is used, a duplicate of the
appropriate row is returned (In this case, I'm searching for a fragment unique
to a single row, such as SEARCH TERM: 'zac' from DATABASE ELEMENT: 'Zack's
Chicken Hole').

Could it be because I'm pulling from two tables?



SELECT DISTINCT tbl_customers.custID, cust_name, cust_addr1, cust_addr2, city,
state, zip,
desk_phone, cell_phone
FROM tbl_customers, tbl_contacts
WHERE cust_name LIKE '%#form.customer#%'

[Dan Bracuk]

Where is your join between tbl_customers and tbl_contacts?

[crux_online]

There is a join between the two tables, but it exists on CUST_ID (which is not
mentioned in the WHERE clause...yet). You see, in creating this search, my
plan was to have successive CFIF tags to prevent a comparison on a NULL value
(I can't attach the proposed code right now, but can later if needed). The
form used to gather info from the user contains multiple fields so that if a
user only know a portion of several of the various fields containing customer
information, the search will take into account all of the fields filled out and
not JUST cust_name, for example.

This may be a little ambitious for me at my level, but I'd love to get this
running and in production here at the shop.

Your input is appreciated.

[philh]

Unless and until you include the join condition, you'll get "duplicates" of the customer name, because the current query is a Cartesian join.

[crux_online]

Originally posted by: Dan Bracuk
If you set up your database relationships properly, you won't get null values
on cust_id. You won't need to use cfif at all.

I understand what you're saying to be that if the DB is correctly designed,
there should be no NULL values in the cust_ID field, and you're right. The
database is so designed. However, if one were to go to the site, one would be
greeted by an empty form with fields for entering the customer's name, address,
city, state, zip, contact name, and so on. On the back end, the DB has a table
for customers (containing their company name, address, etc.) and a table for
contacts (containing their personal name, various phone numbers, position,
department, email, etc.), so a join (in this case on cust_ID) is required in
order to bring these tables together.

With this in mind, the user must enter some snippet of information into one or
more of these empty form fields, but will not be required to fill ALL fields.
This means that when the user click the "Search" button, the CF code must
perform a search of the DB based on the available form fields, some of which
may be NULL (or empty or '', whichever is appropriate). The CFIF tags are
being used to remove the NULL fields from the WHERE clause. (code attached
below).

Originally posted by: philh
Unless and until you include the join condition, you'll get "duplicates" of
the customer name, because the current query is a Cartesian join.

This is a great point that I completely overlooked. Thank you. In trying to
get the conditions to work in the WHERE clause, I removed my join altogether.
It's back in there now.

Below is the complete unedited query. You'll notice most of the CFIFs have
been commented out temporarily while I adjust, research, and re-adjust.

In it's present state, the duplicate is removed, but the query returns the
proper row PLUS one other row that does not match the seach criteria:

*** RETURNED DATA ***
2 Records Found.
Company Name Address
Zack's Chicken Hole 123 Monkey Way
Aces, LLC 786 D Industrial Park Dr
*** END ***

You can see that with a search term of "zac" in the cust_name field, the first
entry should be the only row returned, but I'm a bit suspect of the LIKE
operator. Perhaps it is returning "Aces, LLC" because of the 'ac' that it and
the search term shares. I didn't think it worked quite like that.

More likely, is that it is returning all companies that HAVE contacts
associated with them and the CFIF is using the wrong test, in which case the
question becomes, "how should the CFIF's be used to test for empty form
fields?" As an additional note, Zack's and Aces are the only companies in the
test DB that have contacts, and each has exactly ONE corresponding contact.

Hopefully this clarifies a few things and raises good questions.



<cfquery datasource="#DS#"
name="searchCustomers"
username="#UN#"
password="#PW#">

SELECT tbl_customers.custID,
cust_name,
cust_addr1,
cust_addr2,
city, state, zip,
desk_phone,
cell_phone
FROM tbl_customers, tbl_contacts
WHERE tbl_customers.custID = tbl_contacts.custID
<cfif form.customer EQ ''>AND cust_name LIKE '%#form.customer#%'</cfif>
<!---
<cfif form.addr1 EQ ''>AND cust_addr1 LIKE '%#form.addr1#%'</cfif>
<cfif form.addr2 EQ ''>AND cust_addr2 LIKE '%#form.addr2#%'</cfif>
<cfif form.city EQ ''>AND city LIKE '%#form.city#%'</cfif>
<cfif form.state EQ ''>AND state LIKE '%#form.state#%'</cfif>
<cfif form.zip EQ ''>AND zip LIKE '%#form.zip#%'</cfif>
<cfif form.phone EQ ''>AND desk_phone LIKE '%#form.phone#%'</cfif>
<cfif form.phone EQ ''>AND cell_phone LIKE '%#form.phone#%'</cfif>
--->
</cfquery>

[philh]

Now we're getting somewhere.

<cfif form.customer EQ ''>AND cust_name LIKE '%#form.customer#%'</cfif>

You're asking for every record, because every record will match an evaluation
of LIKE('%%'). Use NEQ instead, because you want to evaluate only fields that
have user input.

HTH,