Dreamweaver - SQL Statement

Author

SQL Statement

FreakyJesus

2006-02-12, 6:39 pm

Hi Gang
I have a database which contains several hundred printers. They are broken up
into two sections, inkjet (code 43150) and laser (code 43170). As there are so
many I want to break the selection down into brand names, so right now my SQL
statement reads:
SELECT *
FROM Products
WHERE BOSSFCode = 43150 AND Description LIKE '%MMColParam%'
The mmcolparam is Hewlett.
I realised, of course, that this only gives me the hewlett packard printers
from the inkjet range (43150) and I want to get them from the laser range too.
I tried
SELECT *
FROM Products
WHERE BOSSFCode = 43150 or BOSSFCode = 43170 AND Description LIKE
'%MMColParam%' ..... But this seems to ignore the hewlett part and just give me
all of the printers of any make.
I also tried another few variations and they all do the same thing. Can
someone point me in the right direction? To recap - I need to query the
description field for the name Hewlett, and include both set of printers,
bossfcode 43150 and 43170.
Thanks.

Michael Fesser

2006-02-12, 6:39 pm

..oO(FreakyJesus)

> I have a database which contains several hundred printers. They are broken up
>into two sections, inkjet (code 43150) and laser (code 43170). As there are so
>many I want to break the selection down into brand names, so right now my SQL
>statement reads:
> SELECT *
> FROM Products
> WHERE BOSSFCode = 43150 AND Description LIKE '%MMColParam%'
> The mmcolparam is Hewlett.

Any chance to change the database layout? I would store the brand names
(with some additionaly informations maybe) in a different table and
simply use their ID in the main table.

> I realised, of course, that this only gives me the hewlett packard printers
>from the inkjet range (43150) and I want to get them from the laser range too.
> I tried
> SELECT *
> FROM Products
> WHERE BOSSFCode = 43150 or BOSSFCode = 43170 AND Description LIKE
>'%MMColParam%' ..... But this seems to ignore the hewlett part and just give me
>all of the printers of any make.

The AND operator takes predecende over the OR operator, so currently the
WHERE clause is executed like this:

WHERE BOSSFCode = 43150 OR (BOSSFCode = 43170 AND Description LIKE ...)

which will return the HP laser printers and all inkjets.

Try this instead:

WHERE (BOSSFCode = 43150 OR BOSSFCode = 43170) AND Description LIKE ...

or simply

WHERE BOSSFCode IN (43150, 43170) AND Description LIKE ...

Micha

FreakyJesus

2006-02-12, 6:39 pm

that seems to be working fine, micha. If i did place the brand names in a
different table, what advantage might i expect, and given that I placed them in
a table called Brands, what then woudl my sql statement be? (sorry, I am only
used to pretty straightforward quiries up to this point)
Thanks

FreakyJesus

2006-02-12, 6:39 pm

I spoke too soon.
I did that with hewlett, and then the equivalent with 5Star. When I test the
statement everything is fine, but when I upload and go from one page to
another, both are displaying the hewlett. I know it is becase the recordset on
the details page is wrong, but am unsure how to correct it. Right now it is:
SELECT *
FROM Products
WHERE BOSSFCode = 43150 AND Description LIKE '%MMColParam%'
This is from when i was working yesterday. What do I need to make this
recordset so it receives the details passed as per the one you outlined for me?
Thanks again.

Michael Fesser

2006-02-12, 6:40 pm

..oO(FreakyJesus)

>that seems to be working fine, micha. If i did place the brand names in a
>different table, what advantage might i expect

It doesn't necessarily have to be a different table, but at least a
separate column. Each field in a database table should only contain one
type of content (read about database normalization if interested, this
looks like a violation of 1NF). A brand name and a product description
are definitely two diffferent kinds of content, they have to be stored
independently.

This could be just a separate column in the same table, but putting the
brands into another table would have some real benefits:

* less space required on the server, each name is only stored once
* it's easier to maintain and update the tables, you would just have to
deal with numeric IDs, not with complex names
* it allows to store some more informations about each company, e.g. a
short description, website etc.

>, and given that I placed them in
>a table called Brands, what then woudl my sql statement be?

This is where JOINs come into play.

BTW: What database server are you using?

>(sorry, I am only
>used to pretty straightforward quiries up to this point)

You should definitely read something about database design and get at
least some basic knowledge (but I can't recommend good English books, so
don't ask me). It will really pay off. Proper DB design not only reduces
redundancy and server load, it also makes it easier for you when
querying the database. With an improper design the queries can become
really complicated or you would have to do a lot of further processing
in your script after the query to get the informations you want.

With a better DB design the server can do many things automatically and
already deliver the informations you're looking for, so no further
processing would be necessary.

Micha

FreakyJesus

2006-02-12, 6:41 pm

Thanks for the reply. It is asp javascript pages with acces DB on windows
hosting. All day i played around with this and all day i kept getting my
details page showing all records. i am pretty sure the problem lies with the
recordset on the detail spage, not the listings page, as when i click Test on
the listing page (on either recordset) i get he correct results. The details
page just hasn't been told how to show what it receives.
So...monday when i return to work i will create a column in the table for
Brand and enter the brand name for each product. That in mind, can you point me
in the direction of what the recordset should look like?
Given that I used your previous suggestion and it worked fine....
"WHERE (BOSSFCode = 43150 OR BOSSFCode = 43170) AND Description LIKE ..."
what then should i set on the details page to correctly show the info from
this recordset?
Thanks.

FreakyJesus

2006-02-12, 6:43 pm

Beuller? Beuller? Anyone? Class? Anyone?