Hi Eric,
I'll second Bob Hall's opinion.
One could extend a query so that it checks also your additional condition.
You haven't gotten to the 'group by' and 'having' goodies, that's the hint.
But this would make it over-complicated,
and MySQL could run into problems when attempting to evaluate that query
(the join would actually produce a cross product,
which you'd only afterwards collapse to a linear list by the 'group by').
BTW, what is packagesParts.pri supposed to mean?
Perhaps the fact that none of packagesParts.pri = 0 for a given package
is significant enough that you want to record it with packenames?
OTOH this, as a derived piece of information,
would go against the "Normalize!" imperative.
You'd have to be more careful when updating the records,
as MySQL won't look for that part of info for you.
Just a suggestion.
Jan Dvorak
Bob Hall wrote:
>
> Sir, here's a guess in return. The best way to deal with this is to
> have your client app run a query that counts the rows where
> packagesParts.pri <> 0. Then run the query below only if the count is
> greater than 0.
>
> Bob Hall
>
> >Hi,
> >
> >I am guessing this is the kind of problem that would be easier to solve with
> >a sub select
> >
> >
> >SELECT
> >simpleparts.category,simpleparts.partnumber,simpleparts.manufacturer,simplep
> >arts.descrp,ABS(packagesParts.pri) as abPRI,categories.sortorder
> >FROM ((packagenames LEFT JOIN packagesParts ON packagenames.packageID =
> >packagesParts.packageID)
> >LEFT JOIN simpleparts ON packagesParts.partnumber = simpleparts.partnumber)
> >LEFT JOIN categories ON simpleparts.category = categories.category
> >WHERE simpleparts.configurator <>0
> >AND categories.onconfig <>0
> >AND packagenames.packagename = $system_name
> >AND simpleparts.category = ?
> >ORDER BY abPRI DESC
> >
> >This in one case returns
> >
> >
> >------------------+------------+--------------+---------------------+------+
> >-----------+
> >| category | partnumber | manufacturer | descrp | pri
> >| sortorder |
> >+------------------+------------+--------------+---------------------+------
> >+-----------+
> >| Game Controllers | 234232 | Jumbo Video | Video card | 1 |
> >24 |
> >| Hard Drives | 78544 | HTH | a Drive controller
> >| 1 | 12 |
> >| Floppy Drives | HP-4p | HP | 4P - 48bit
> >| 1 | 13 |
> >| CPUs | int-550c | Intel | Celeron 500MHZ 128K |
> >1 | 1 |
> >| Memory | mem-102 | who knows | 256M 120pin | 1
> >| 3 |
> >| Scanners | 9955 | Acer | 10 ISA
> >| 0 | 22 |
> >| Game Controllers | game-01 | Atari | Simple stick |
> >0 | 24 |
> >| CPUs | INT550 | INTEL | PIII 550 CPU
> >| 0 | 1 |
> >| Memory | mem-101 | who knows | 128M
> >| 0 | 3 |
> >| Modems | mod-124 | USR | sportster 28.8
> >| 0 | 9 |
> >+------------------+------------+--------------+---------------------+------
> >+-----------+
> >
> >But I have one more constraint that I would like to be able to include in
> >the query and not deal with in code.
> >
> >I want to not return anything in the case where none of the
> >packagesParts.pri are = 0 this is hard because I do what those
> >packagesParts.pri =0 where there is a packagesParts.pri = 1 in the result set.
> >
> >
> >Is there a way to cram this into my query with mysql as it stands?
> >
> >
> >I saw some people here do some pretty fancy stuff, so I thought it was worth
> >asking.
> >
> >
> >Thanks,
> >
> >Eric
> >
> >
> >Frazier Consulting
> >http://www.kwinternet.com/eric
> >(250) 655 - 9513
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
