[ic] Searching tables

[Mark Johnson]

Kevin Flynn wrote:
> 
> I'd appreciate any further help with the piece of code below. I want to
> search the description field in the orderline table to see what order
> contains a particular product (i.e. Hand Planer) and pull back all relevant
> information for that order (i.e. Order number, username, address, etc...).
> which is held in the transactions table.
> 
> <snip>
> 
> [query
>             sql="SELECT order_number, fname, lname, city, state, country,
> order_date, status
>             FROM transactions,orderline WHERE description LIKE
> 'given_keyword'
>             AND transactions.order_number = orderline.order_number
> [comment]===== I keep getting an INTERNAL SERVER ERROR when I put the above
> line in =====[/comment]
> "]
> [sql-param order_number]
> [sql-param order_date]
> [sql-param fname]
> [sql-param lname]
> [sql-param city],
> [sql-param state],
> [sql-param country]
> [sql-param status]
> [/query]
> 
> </snip>
> 
> Regards,
> 
> Kevin Flynn
> kflynn@celticlinks.com
> 

First, I assume that you are using a true SQL database that can handle
joins. If you are using DBMs, you can forget it.

Second, there are possible ambiguities in your SQL, unless you know your
fields are unique and are sure your database can handle non-explicit
references. I strongly recommend you fully qualify all your columns when
you join tables. Also, for at least some databases, it is useful to have
your join condition be the first element(s) of your where clause.
Something like:

SELECT DISTINCT transactions.order_number, transactions.fname,
	transactions.lname, transactions.city, transactions.state,
	transactions.country, transactions.order_date,
	transactions.status
FROM transactions, orderline
WHERE transactions.order_number=orderline.order_number
AND orderline.description LIKE 'given_keyword'

The DISTINCT will eliminate multiple rows with the same data for any
matches against multiple line items on the same order.

-- 
Mark Johnson
Senior Systems Architect - Professional Services
Red Hat, Inc.
E-Business Solutions
markj@redhat.com
703-456-2912