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 | | > | > | product_description | varchar(255) | | | | > | > +---------------------+--------------+------+-----+---------+- > ---------------+ > 4 rows in set (0.00 sec) > > mysql> describe specs; > +------------+--------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +------------+--------------+------+-----+---------+----------------+ > | id | int(12) | | PRI | NULL | auto_increment | > | fg_number | varchar(8) | | MUL | | | > | spec_name | varchar(255) | | | | | > | spec_value | varchar(255) | | | | | > +------------+--------------+------+-----+---------+----------------+ > 4 rows in set (0.00 sec) > > Man is this sql, query stuff fun ;-) > > Michael > > > > > > --------------------------------------------------------------------- > 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
