LEFT JOIN GROUP BY HAVING problem
Ok, I've got a problem, I've got an inventory of books they can be stored in different locations in the warehouse and I need to generate a booklist, and I want to include the title and an author, I have a table with the authors in it, and there may be anywhere from 1 to 10 authors for a particular book, but I don't really care, I just need one for displaying ... anyway, here is a layout table stock ( qty int, isbn char(20), location int ) table books ( isbn char(20), title char(128) ) table authors ( isbn char(20), author char(120) ) table prices ( isbn char, retail decimal(7,2), wholesale decimal(7,2) ) all the tables with the exception of stock and authors have a unique index on the isbn, so only one isbn in those. Now I'm trying to generate a book list with quantities available, and it needs a short description, title, listprice, ourprice, author.. I tried this query, select sum(s.qty),s.isbn,b.title, p.listprice,p.ourprice, a.author from stock s left join books b on s.isbn=b.isbn left join prices p on s.isbn=p.isbn left join authors a on s.isbn=a.isbn group by s.isbn HAVING sum(s.qty) > 10; now this works ok, but of course if I have more than one author the actual sum of the books is multiplied by the actual amount of authors listed for the book, now I don't really care what author shows up, it's just mostly a filler for the list, but I finally struck on this solution and now I just need to know if it should be reliable...? select sum(s.qty) ,s.isbn,p.listprice,p.ourprice, a.author,count(distinct a.author) from stock s left join books b on s.isbn=b.isbn left join prices p on s.isbn=p.isbn left join authors a on s.isbn=a.isbn group by s.isbn HAVING sum(s.qty) > 10; There is a possiblity, that there may not be an author listed for a particular isbn, or I would do the math in the query, but using the above, I should be able to test the count of distinct a.author and divide sum.qty by the distinct a.authors and come up with a legit count for the actual count of books I have, of course testing for zero count in the distinct a.author to avoid diving by zero, and using this I believe that I should have the correct count in inventory? Should this work and be reliable? Long winded, but just wanting to make sure I'm understood.. Kelley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB questions for all!
Chris, From: Chris Nolan ([EMAIL PROTECTED]) Subject: InnoDB questions for all! This is the only article in this thread View: Original Format Newsgroups: mailing.database.myodbc Date: 2003-08-28 16:16:53 PST > Hi everyone! > > My silly questions for today concern the not-silly-at-all InnoDB table > backend. not silly questions at all. > 1. If I do an ALTER TABLE tbl_name TYPE=InnoDB on an InnoDB table, > Heikki has > indicated that a defrag of that table should happen. Does anyone have > any comments regarding the > result of a power failure while this operation is in progress? MySQL performs an ALTER TABLE tablename by (1) first creating a temporary table with the new definition, and then copying all rows to it. Then it (2) renames the old table, (3) renames the temporary table to 'tablename', and (4) drops the old table. This should ensure the data is never lost, even in a power failure. If the failure happens at a bad time, you may end up with 2 renamed tables and no table of the original name 'tablename'. But no one has ever reported this situation. InnoDB also contains a special mechanism by which you can rename a temporary table #sql.., see http://www.innodb.com/ibman.html#InnoDB_troubleshooting_dict. > 2. If I do a SELECT * on an InnoDB table and dump the output to a > file inside a single transaction, > will INSERT statements still complete correctly? I've completed a 3rd > year Database course at a decent uni > and know the theory as well as the practice, but admittedly I don't know > enough about InnoDB's innards to > answer this question for myself. My gut feeling says that INSERTs will > complete successfully while this process > is going on and that UPDATEs may complete, depending on InnoDB's method > of multiversioning. A SELECT ... INTO OUTFILE ... does not set any locks or disturb INSERTs or UPDATEs. The only limitation is that InnoDB cannot purge its history while the SELECT is transaction is active. If the SELECT transaction lasts long, say for hours or days, you might run out of space in the tablespace. > Thanks all! > > Regards, > > Chris Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple query help
* Boris Villazon > El vie, 29-08-2003 a las 22:05, Daniel Clark escribió: > > > select value from tableName where date in (select max(date) > > > from tableName where id = 4); > > > > > > But, it doesn't work with mysql 4.0. > > > > > > Any ideas? Does anybody had this problem before? > > > > What about: > > > > SELECT value, date > > FROM tablename > > WHERE id = 4 > > ORDER BY date ASC > > > > Just pick the first row. > > > > Thanks Daniel. > > Yes, it's the last option. But, I think that there is a good one. > I think that somebody had this problem before. > > Does anybody have more ideas? You wanted tha latest date, right? You should use what Daniel suggested, exept you probably want "...ORDER BY date DESC LIMIT 1" -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Simple query help
El vie, 29-08-2003 a las 22:05, Daniel Clark escribió: > > select value from tableName where date in (select max(date) from > > tableName where id = 4); > > > > But, it doesn't work with mysql 4.0. > > > > Any ideas? Does anybody had this problem before? > > What about: > > SELECT value, date > FROM tablename > WHERE id = 4 > ORDER BY date ASC > > Just pick the first row. > Thanks Daniel. Yes, it's the last option. But, I think that there is a good one. I think that somebody had this problem before. Does anybody have more ideas? Thanks in advance and best regards boricles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP Search + MySQL Query
Brown, Chris wrote: > +---+-+ >> Field | Type| > +---+-+ >> id| int(6) | >> svc | varchar(20) | >> sev | tinyint(1) | >> dsc | varchar(60) | >> inits | varchar(20) | >> dst | date| >> tst | time| >> den | date| >> ten | time| >> res | tinyint(1) | > +---+-+ > I went back and made the query up manually field by field, one field > at a time, adding each LIKE statement in query after query, and found > that after field DSC it failed, with the empty set result. > Can anyone point me into the direction of a) Why it fails and b) A > proper query that'll make this work?? If you allways want to LIKE a field, declare it NOT NULL DEFAULT '': LIKE '%' on an empty string is TRUE, on a NULL (aka non-existant) it is FALSE. BTW, no need to optimize '%%' to '%' create table test ( id int , text1 char(9) not null default '', text2 char(9) not null default '', text3 char(9) not null default '', ); insert test ( id, text1, text2, text3 ) values ( 1 , 'a', 'b', 'c' ),( 2 , 'aa', 'bb', '' ); insert test ( id, text1, text2 ) values ( 3 , '', ''); select * from test where text1 like '%' AND text2 like '%%' AND text3 like '%'; HansH -- The evil powers of NULL ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DBUG_PRINT
Hi all, How can I get the output of the DBUG_PRINT (which is everywhere in the code of the mysql) option to print the output as it is intended? Thanks in advance. Yonghua -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: PHP Search + MySQL Query
Did you ORed or ANDed the LIKE clauses?
Please provide the combined statement that didn't return results, the one
with more than one LIKE clauses.
Lian Sebe, M.Sc.
Freelance Analyst-Programmer
www.programEz.net
> -Original Message-
> From: Brown, Chris [mailto:[EMAIL PROTECTED]
> Sent: Saturday, August 30, 2003 1:28 PM
> To: [EMAIL PROTECTED]
> Subject: PHP Search + MySQL Query
>
>
> Hi all,
>
> In learning PHP and expanding my (seriously limited) SQL knowledge, I
> need to write a query that will look for one or more (can be any number)
> of the following fields held in the table:
>
> +---+-+
> | Field | Type|
> +---+-+
> | id| int(6) |
> | svc | varchar(20) |
> | sev | tinyint(1) |
> | dsc | varchar(60) |
> | inits | varchar(20) |
> | dst | date|
> | tst | time|
> | den | date|
> | ten | time|
> | res | tinyint(1) |
> +---+-+
>
> I was attempting this using a SELECT command, a WHERE clause, and lots
> of LIKE statements. If search criteria had been entered, the LIKE
> statement went along the lines of LIKE('%criteria%'), and if it was
> empty, was left with a LIKE('%').
>
> Yes, it's tragic SQL (one day they may send me on a proper SQL
> course...) and naturally, it doesn't work. The result given is "Empty
> Set", even if I only put in one part of search criteria, and if I run a
> manual SELECT id,dsc FROM problems WHERE $field
> LIKE('%this_criteria_exists%'); it works fine.
>
> I went back and made the query up manually field by field, one field at
> a time, adding each LIKE statement in query after query, and found that
> after field DSC it failed, with the empty set result.
>
> Can anyone point me into the direction of a) Why it fails and b) A
> proper query that'll make this work??
>
> All help very gratefully received.
>
> Many thanks in advance
>
> --
> Chris Brown
> e: ckb@:nospam:.maxnet.eu.org, m:
>
> Never argue with an idiot, he'll just lower you to his level and beat
> you with experience...
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
PHP Search + MySQL Query
Hi all,
In learning PHP and expanding my (seriously limited) SQL knowledge, I
need to write a query that will look for one or more (can be any number)
of the following fields held in the table:
+---+-+
| Field | Type|
+---+-+
| id| int(6) |
| svc | varchar(20) |
| sev | tinyint(1) |
| dsc | varchar(60) |
| inits | varchar(20) |
| dst | date|
| tst | time|
| den | date|
| ten | time|
| res | tinyint(1) |
+---+-+
I was attempting this using a SELECT command, a WHERE clause, and lots
of LIKE statements. If search criteria had been entered, the LIKE
statement went along the lines of LIKE('%criteria%'), and if it was
empty, was left with a LIKE('%').
Yes, it's tragic SQL (one day they may send me on a proper SQL
course...) and naturally, it doesn't work. The result given is "Empty
Set", even if I only put in one part of search criteria, and if I run a
manual SELECT id,dsc FROM problems WHERE $field
LIKE('%this_criteria_exists%'); it works fine.
I went back and made the query up manually field by field, one field at
a time, adding each LIKE statement in query after query, and found that
after field DSC it failed, with the empty set result.
Can anyone point me into the direction of a) Why it fails and b) A
proper query that'll make this work??
All help very gratefully received.
Many thanks in advance
--
Chris Brown
e: ckb@:nospam:.maxnet.eu.org, m:
Never argue with an idiot, he'll just lower you to his level and beat
you with experience...
Re: Computing Age for Select Query
OK, really I should have RTFM closer on this one (at least the tutorial section). http://www.mysql.com/doc/en/Date_calculations.html even has a part about computing ages. However, I guess my original question remains, which is, is there a better, cleaner way to do it than this, which computes the age twice, albeit for different users: SELECT user_b.* FROM user AS user_a, user AS user_b WHERE (YEAR(CURDATE())-YEAR(user_a.date_of_birth)) - (RIGHT(CURDATE(),5) To: <[EMAIL PROTECTED]> Sent: Saturday, August 30, 2003 4:31 AM Subject: Computing Age for Select Query I'm writing a SQL query that returns a set of users based each user's mutual matching preferences. Picture a dating service, where one user is 35 looking to meet 25-35 year olds, and another (matching) user is 25 looking to meet 30-35 year olds. The relevant fields in the user table would be: date_of_birth (date) min_age (int) -- the min age a match is allowed to be max_age (int) -- the max age a match is allowed to be The pseudo SQL I have for this is something like: SELECT user_b.* FROM user AS user_a, user AS user_b WHERE (getAge(user_a.date_of_birth) BETWEEN user_b.min_age AND user_b.max_age) AND (getAge(user_b.date_of_birth) BETWEEN user_a.min_age AND user_a.max_age) /* and for a specific user maybe */ AND user_a.id = 5; All well and good, except there's obviously no such "getAge" function, nor, as I understand it, is there a way in MySQL to define one. What's the recommended practice for a situation like this? Is there a way I can do this "function-like" so I don't end up duplicating a lot of strange SQL code where the getAge function would be in both expressions? I guess I'm less concerned here about the date/time functions of MySQL (unless there actually is a getAge function), as I am to figuring out the proper/elegant way to use them. Many thanks for any help or gentle shove(s) in the right direction. Noah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Computing Age for Select Query
I'm writing a SQL query that returns a set of users based each user's mutual matching preferences. Picture a dating service, where one user is 35 looking to meet 25-35 year olds, and another (matching) user is 25 looking to meet 30-35 year olds. The relevant fields in the user table would be: date_of_birth (date) min_age (int) -- the min age a match is allowed to be max_age (int) -- the max age a match is allowed to be The pseudo SQL I have for this is something like: SELECT user_b.* FROM user AS user_a, user AS user_b WHERE (getAge(user_a.date_of_birth) BETWEEN user_b.min_age AND user_b.max_age) AND (getAge(user_b.date_of_birth) BETWEEN user_a.min_age AND user_a.max_age) /* and for a specific user maybe */ AND user_a.id = 5; All well and good, except there's obviously no such "getAge" function, nor, as I understand it, is there a way in MySQL to define one. What's the recommended practice for a situation like this? Is there a way I can do this "function-like" so I don't end up duplicating a lot of strange SQL code where the getAge function would be in both expressions? I guess I'm less concerned here about the date/time functions of MySQL (unless there actually is a getAge function), as I am to figuring out the proper/elegant way to use them. Many thanks for any help or gentle shove(s) in the right direction. Noah
Using multiple character sets (Russian & English)
Hello,
I have experience using MySQL and SQL commands in
general, but I am going through my first experience
with working with multiple character sets.
I am working on a Russian/English translation project
and I'd like to create a table where column1 contains
words in Russian and column2 contains the word
translated into English. Russian character set should
be cp1251, which I have confirmed is installed on the
MySQL server (3.23.57-Max running on Linux). At this
time, it is not possible for us to upgrade beyond this
version of the MySQL server.
How do I issue SQL commands in English:
("insert into TableName values...")
values that are of type varchar in both Russian and
English:
(null, "cyrillic_russian_word", "english_word");
Any guidance is appreciated. Thank you.
Sincerely,
Rachel
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: quick PHP question
> Unfortunately I want my darn PHP web page to do this calculation and > spit it out with my other run data but I can not find any useful PHP > function similar to MySQL's sec_to_time() or time_to_sec(). Nor can I > make mktime() do anything useful here. You probably want strtotime(), it can do things like: 1062265296 -- Greg Donald http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
quick PHP question
I'm trying to do some mathematics using a time value in PHP. It's dead simple in MySQL: mysql> select date, duration, miles, sec_to_time(time_to_sec(duration) / miles) as speed from runs where shoes=6 order by date; ++--+---+--+ | date | duration | miles | speed| ++--+---+--+ | 2003-07-06 | 01:55:12 | 12.43 | 00:09:16 | | 2003-07-13 | 01:59:41 |13 | 00:09:12 | | 2003-07-18 | 00:47:33 | 6 | 00:07:55 | | 2003-07-20 | 01:22:53 |10 | 00:08:17 | | 2003-07-27 | 02:15:27 |15 | 00:09:01 | | 2003-08-03 | 02:25:07 |16 | 00:09:04 | | 2003-08-10 | 01:44:29 |12 | 00:08:42 | | 2003-08-17 | 02:41:55 | 18.5 | 00:08:45 | | 2003-08-24 | 02:07:45 |14 | 00:09:07 | ++--+---+--+ 9 rows in set (0.01 sec) We get a nice list of runs with the calculated minutes/miles (speed) field included. This requires converting the stored time field to seconds, doing the division, then converting the resulting seconds back to a time format. Unfortunately I want my darn PHP web page to do this calculation and spit it out with my other run data but I can not find any useful PHP function similar to MySQL's sec_to_time() or time_to_sec(). Nor can I make mktime() do anything useful here. Does anyone know of any PHP function that will solve my above need? -- Trevor Smith|[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trouble compiling 5.0
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Spam Bait wrote: [snip] > Does anyone have a link to some docs on the syntax for 'create procedure'? > I checked on the net for PL/SQL syntax, and found some Oracle and > Postgres examples, but none of them worked for me. > > Just a little example? Hi! See mysql-test/t/sp.test in your source distribution for examples, and Docs/sp-implemented.txt and Docs/sp-imp-spec.txt for further information. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 557 2388 www.mysql.com Are you MySQL Certified? http://www.mysql.com/certification/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/T+j8tvXNTca6JD8RAlY6AJ9doWc43sBXJsB+Ji6A0WnuPtlTVwCgkp1B dY/bxgKV5XoXoWVaBUJZjs8= =ztF7 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
