RE: query help
It seems that you have two entries in the products table for the same fg_number, one with the values: 'SPKR,CM8HD', 'FG00914', '8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER' And a second one with the same fg_number but in this case with a lowercase F. '', 'fg00914', '' Is this rigth? Please, if you find a good resource for complex queries advise me. I also have the DuBois book, and I need more advanced for advanced queries with mySql. Regards Javier -Mensaje original- De: Michael Knauf/Niles [mailto:[EMAIL PROTECTED]] Enviado el: martes, 08 de octubre de 2002 14:48 Para: [EMAIL PROTECTED] Asunto: query help Here's a query for ya: SELECT products.product_name, products.fg_number, products.product_description, specs.spec_name, specs.spec_value FROM products LEFT JOIN specs ON products.fg_number = specs.fg_number WHERE products.fg_number='fg00914'; and here's the result: +--+---+-- --+--+ -- -+ | product_name | fg_number | product_description | spec_name| spec_value | +--+---+-- --+--+ -- -+ | | fg00914 | | Woofer | 8 injection-molded TCC (talc) test | | | fg00914 | | Tweeter | 1 fluid-cooled ultra-wide dispersion tri-laminate Teteron tweeter in a custom pivoting coaxial enclosure | | | fg00914 | | Recommended amplifier power | 10 to 150 watts | | | fg00914 | | Frequency | 40Hz-21kHz +/- 3dB | | | fg00914 | | Sensitivity | 90dB For 2.83V pink noise | | | fg00914 | | Impedance | 4 or 8 ohm selectable | | | fg00914 | | Dimensions | 10-5/8 diameter | | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Woofer | 8 injection-molded TCC (talc) test | | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Tweeter | 1 fluid-cooled ultra-wide dispersion tri-laminate Teteron tweeter in a custom pivoting coaxial enclosure | | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Recommended amplifier power | 10 to 150 watts | | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Frequency | 40Hz-21kHz +/- 3dB | | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Sensitivity | 90dB For 2.83V pink noise | | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Impedance | 4 or 8 ohm selectable | | SPKR,CM8HD | FG00914 | 8,HIGH DEFINITION,CEILING MNT LOUDSPEAKER | Dimensions | 10-5/8 diameter | +--+---+-- --+--+ -- -+ 14 rows in set (0.00 sec) I get 7 rows of data I don't want, then the 7 rows of data I do want... and after scratching my head for awhile, I've come to the conclusion that I do not understand the query structure. (the query came from multiple suggestions in response to my last posting on this list, so clearly it makes sense to some of you...) Can somebody point me to a resource on complicated multi-table select statements? I have the Paul DuBois MySQL book, which got me from 0 to where I am now, but isn't getting me past this particular query. Or maybe just explain how to get the last 7 rows without the first? --it's duplicating the same data with and without the product_name and product_description fields. Here's the table structure: mysql describe products; +-+--+--+-+-+- ---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+- ---+ | id | int(6) | | PRI | NULL| auto_increment | | fg_number | varchar(9) | | MUL | 0 | | | product_name| varchar(64) | | MUL | | |
Select IN and NOT IN
Hello everybody, I have a next problem to get a query working. I have some tables. A User has Phone numbers and Calls associated with each phone. # Example: All the calls of the user number 1 SELECT Call.* FROM User, Phone, Call WHERE Phone.User=1 AND Call.Phone=Phone.PhoneNumber A User has also an agenda with ContactGroups an Contacts, where the contact has also a phone number. #Al the contacts for the user 1 SELECT Contact.* FROM User, ContactGroup, Contact WHERE ContactGroup.User=1 AND Contact.ContactGroup=ContactGroup.ContactGroupId What I want to retrieve is a list with all the calls, but with the contact associated to the called number (if that contact exists) or a text showing that the call is no assigned to a contact. I have tried something like this: SELECT IFNULL(Contact.Name, 'Not Assigned'), Call.* FROM User, Phone, Call LEFT JOIN ContactGroup ON ContactGroup.User=1 LEFT JOIN Contact ON Contact.ContactGroup=ContactGroup.ContactGroupId WHERE (Contact.PhoneNumber=Call.CalledNumber OR Contact.PhoneNumber IS NULL) AND Phone.User=1 AND Call.Phone=Phone.PhoneNumber It works with calls that have the called number in the user contact list, but the query repeat the calls for each group that don't have received calls (the effect of the OR Contact.PhoneNumber IS NULL). I don't know if I'm missing something or this cannot be done with MySql. I think that this could be done with a Temp table, but I need to do it in only a query. Any idea about how to do it? Regards Javier *Note: The tables are simplified because they were named in Spanish and there were more tables used to filter the calls, but they are not related with my problem - 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
Insert date format
Hello,
I am developing an aplication where I need format the date for inserting
into mySQL.
With DATE_FORMAT I can format the mySQL date to my date, but I haven't
found a good way to format my date to the mySQL date.
My format is 'dd-mm-' and to convert it to '-mm-dd' I'm using
the next sequence:
SELECT DATE_FORMAT(
CONCAT(
SUBSTRING_INDEX('03-05-2002', '-', -1),
'-',
SUBSTRING_INDEX(SUBSTRING_INDEX('03-05-2002', '-', 2),
'-', -1),
'-',
SUBSTRING_INDEX('03-05-2002', '-', 1)),
'%Y-%m-%d' );
I have searched in the manual, the list and in the DuBois' book, but I
haven't found a single function to parse my date to the mySQL date. Is
that rigth? Is there no function to parse from any date to the mySQL
date?
Regards
Javier
-
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
RE: Insert date format
Hello,
I am developing an aplication where I need format the date for
inserting into mySQL.
=what language is your application written in?
=dn
The application is written in Java, but the problem is that the module
where the dates for the DB are managed is an open source framework and I
wouldn't like to touch that code.
The framework provides a way to modify the dates from/to the database
defining native database functions in a config file.
Anyway, the problem is alredy solved, but I still have the dude if I
there is a better solution.
Regards
Javier
With DATE_FORMAT I can format the mySQL date to my date, but I
haven't found a good way to format my date to the mySQL date.
My format is 'dd-mm-' and to convert it to '-mm-dd'
I'm using
the next sequence:
SELECT DATE_FORMAT(
CONCAT(
SUBSTRING_INDEX('03-05-2002', '-', -1),
'-',
SUBSTRING_INDEX(SUBSTRING_INDEX('03-05-2002', '-', 2),
'-', -1),
'-',
SUBSTRING_INDEX('03-05-2002', '-', 1)),
'%Y-%m-%d' );
I have searched in the manual, the list and in the DuBois'
book, but I
haven't found a single function to parse my date to the
mySQL date. Is
that rigth? Is there no function to parse from any date to
the mySQL
date?
Regards
Javier
-
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
-
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
RE: Left join?
Hi, I have a similar problem (with phones calls) but I would like to select only those elements that have less than a number. Using the previous example, is like selecting only those interest elements that have less that 5 members associated. Something like this (obviously this doesn't work) query SELECT DISTINCT(mi.interest_id), COUNT(*) AS count, i.name FROM member_interests AS mi left join outer interests AS i on mi.interest_id = i.id AND count 5 GROUP BY mi.interest_id ORDER BY i.name /query Does anyone have an idea to solve this kind of problem? Does anyone know where can I find a web site with complex queries? Thank you Javier -Mensaje original- De: Cal Evans [mailto:[EMAIL PROTECTED]] Enviado el: martes 4 de junio de 2002 22:48 Para: [EMAIL PROTECTED]; [EMAIL PROTECTED] Asunto: RE: Left join? SELECT DISTINCT(mi.interest_id), COUNT(*) AS count, i.name FROM member_interests AS mi left join outer interests AS i on mi.interest_id = i.id GROUP BY mi.interest_id ORDER BY i.name You are correct, a left outer join is what you need. * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: Daren Cotter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 04, 2002 2:07 PM To: [EMAIL PROTECTED] Subject: Left join? I have the following tables: Member_interests: Member_id Interest_id Interests: Name Interest_id I need a query that selects each interest name, and the # of members who have selected it...sample output: Boating 25 Hiking10 .. Swimming 0 Jumping 0 Talking 0 The following query works great, but does not display the Interest names with 0 members: SELECT DISTINCT(mi.interest_id), COUNT(*) AS count, i.name FROM member_interests AS mi, interests AS i WHERE mi.interest_id = i.id GROUP BY mi.interest_id ORDER BY i.name Is there a way to have the 0's displayed as well? I'm thinking a left join would probably be involved? - 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 - 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 - 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
