Help with SELECT and possible JOIN
I have two tables defined: CREATE TABLE `tga_body` ( `body_id` int(10) unsigned NOT NULL auto_increment, `blob_pos` mediumint(8) unsigned NOT NULL, `file_id` int(10) unsigned NOT NULL, `blob_id` int(10) unsigned NOT NULL, PRIMARY KEY USING BTREE (`body_id`), KEY `file_id` (`file_id`), KEY `blob_id` (`blob_id`) ) ENGINE=MyISAM; CREATE TABLE `tga_body_blob` ( `blob_id` int(10) unsigned NOT NULL auto_increment, `blob_data` mediumblob NOT NULL, PRIMARY KEY USING BTREE (`blob_id`), KEY `blob_data` (`blob_data`(64)) ) ENGINE=MyISAM; I need to select all tga_body_blob.blob_data where tga_body.blob_id is equal to tga_body_blob.blob_id and tga_body.file_id is equal to 'some_name' ordered by tga_body.blob_pos. I can type in english what I want. However, I am stumped on the select statement. I'm speculating I will need to use some form of JOIN but am unsure. -d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Help with SELECT and possible JOIN
On Mon, 19 Jan 2009, Duane Hill wrote: I have two tables defined: CREATE TABLE `tga_body` ( `body_id` int(10) unsigned NOT NULL auto_increment, `blob_pos` mediumint(8) unsigned NOT NULL, `file_id` int(10) unsigned NOT NULL, `blob_id` int(10) unsigned NOT NULL, PRIMARY KEY USING BTREE (`body_id`), KEY `file_id` (`file_id`), KEY `blob_id` (`blob_id`) ) ENGINE=MyISAM; CREATE TABLE `tga_body_blob` ( `blob_id` int(10) unsigned NOT NULL auto_increment, `blob_data` mediumblob NOT NULL, PRIMARY KEY USING BTREE (`blob_id`), KEY `blob_data` (`blob_data`(64)) ) ENGINE=MyISAM; I need to select all tga_body_blob.blob_data where tga_body.blob_id is equal to tga_body_blob.blob_id and tga_body.file_id is equal to 'some_name' ordered by tga_body.blob_pos. I can type in english what I want. However, I am stumped on the select statement. I'm speculating I will need to use some form of JOIN but am unsure. I don't know if this is an accurate way of solving or not. I managed to get what I wanted by this select statement: SELECT * FROM tga_body,tga_body_blob WHERE tga_body.file_id = some_id AND tga_body.blob_id = tga_body_blob.blob_id ORDER BY tga_body.blob_pos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: possible join
Hi, SELECT zone from fedex_zones where zip = 94947 select price from fedex_rates where zone = '8' and weight = '25' your query should be : select price from fedex_rates, fedex_zones where fedex_zones.zip = 94947 and fedex_zones.zip = fedex_rates.zip and fedex_rates.weight = '25' Mathias Selon Scott Haneda [EMAIL PROTECTED]: on 5/10/05 8:29 PM, Peter Brawley at [EMAIL PROTECTED] wrote: Scott, ...In part, my trouble is that I need to take the resuling zone from the first select and use that to determine the field name. I can easily do this in my code in 2 selects, but was hoping to be able to get the price back in just one select, if possible... If you have control over the data model, it would be good to change the structure of fedex_rates to (id int PK, zone int, weight int, price decimal(10,2)), getting rid of the denormalised z_* columns which are causing you problems. Then a one-stage query would just be SELECT price FROM fedex_rates WHERE zone=8 AND weight=12. If you're stuck with the table structure you show, you're stuck with two queries. If these lookup tables aren't large, there's probably not much performance to be gained from hiding the two stages inside a stored procedure, but if you want a one-step, IMO that's the way to go. Ok, I changed the tables around a little, I can not really do this all in one table, since the data gets made new often by fedex, at any rate, (no pun intended :-))... mysql describe fedex_zones; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | zip | char(5) | | UNI | || | zone | char(2) | | | || +---+-+--+-+-++ mysql describe fedex_rates; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | weight | int(11) | | | 0 || | zone | int(11) | | | 0 || | price | decimal(10,2) | | | 0.00|| ++---+--+-+-++ so first, I need to get the zone I am in, which is a: SELECT zone from fedex_zones where zip = 94947 8 If the result in that case is 8, then I can select price from fedex_rates where zone = '8' and weight = '25' For some reason, this join is still not screaming out at me, or maybe I have it right, and my data is in duplication, any help is appreciated. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible join
Scott, sorry, my mistake, SELECT price FROM fedex_zones z INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947 WHERE r.weight = 25; PB Scott Haneda wrote: on 5/10/05 8:29 PM, Peter Brawley at [EMAIL PROTECTED] wrote: Scott, ...In part, my trouble is that I need to take the resuling zone from the first select and use that to determine the field name. I can easily do this in my code in 2 selects, but was hoping to be able to get the price back in just one select, if possible... If you have control over the data model, it would be good to change the structure of fedex_rates to (id int PK, zone int, weight int, price decimal(10,2)), getting rid of the denormalised z_* columns which are causing you problems. Then a one-stage query would just be SELECT price FROM fedex_rates WHERE zone=8 AND weight=12. If you're stuck with the table structure you show, you're stuck with two queries. If these lookup tables aren't large, there's probably not much performance to be gained from hiding the two stages inside a stored procedure, but if you want a one-step, IMO that's the way to go. Ok, I changed the tables around a little, I can not really do this all in one table, since the data gets made new often by fedex, at any rate, (no pun intended :-))... mysql describe fedex_zones; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | zip | char(5) | | UNI | || | zone | char(2) | | | || +---+-+--+-+-++ mysql describe fedex_rates; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | weight | int(11) | | | 0 || | zone | int(11) | | | 0 || | price | decimal(10,2) | | | 0.00|| ++---+--+-+-++ so first, I need to get the zone I am in, which is a: SELECT zone from fedex_zones where zip = 94947 8 If the result in that case is 8, then I can select price from fedex_rates where zone = '8' and weight = '25' For some reason, this join is still not screaming out at me, or maybe I have it right, and my data is in duplication, any help is appreciated. No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible join
In article [EMAIL PROTECTED], Peter Brawley [EMAIL PROTECTED] writes: Scott, sorry, my mistake, SELECT price FROM fedex_zones z INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947 WHERE r.weight = 25; PB Although correct, many people consider this bad style - the ON clause of the JOIN should contain only the join condition(s). So it would be better to say SELECT price FROM fedex_zones z JOIN fedex_rates r ON z.zone = r.zone WHERE r.weight = 25 AND z.zip = 94947 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible join
news [EMAIL PROTECTED] wrote on 05/11/2005 09:09:36 AM: In article [EMAIL PROTECTED], Peter Brawley [EMAIL PROTECTED] writes: Scott, sorry, my mistake, SELECT price FROM fedex_zones z INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947 WHERE r.weight = 25; PB Although correct, many people consider this bad style - the ON clause of the JOIN should contain only the join condition(s). So it would be better to say SELECT price FROM fedex_zones z JOIN fedex_rates r ON z.zone = r.zone WHERE r.weight = 25 AND z.zip = 94947 I agree, in principle, with the comment about preferred style. However, certain combinations of ON conditions can trigger the use of different indexes by the optimizer based on the conditions being matched. The final query results may be the same but if you want the best possible performance out of your JOIN queries, sometimes you need to break the style rule. I list two other correct if not properly styled ways of writing the same query below. Based on the optimizer and its choice of indexes used during the JOIN processing phase of their executions, these may have (slightly) different performance characteristics. SELECT price FROM fedex_zones z JOIN fedex_rates r ON z.zone = r.zone AND r.weight = 25 WHERE z.zip = 94947 SELECT price FROM fedex_zones z JOIN fedex_rates r ON z.zone = r.zone AND r.weight = 25 AND z.zip = 94947 As I stress, these should all produce the same results. The one that will perform best depends on both the data composition and the indexes on the tables. The EXPLAIN command will expose any differences in the execution plans for each of these queries. If none of them show a difference in their execution plans, then each of them is interchangeable for the other. That means that you cannot make a decision of which form to use based on performance. The form with the constant conditions in the WHERE clause (the one from the last post) would then be the preferred way of writing this query when performance is not an issue. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: possible join
Although correct, many people consider this bad style - the ON clause of the JOIN should contain only the join condition(s). So it would be better to say Yes indeed but here the 'zip' condition is in the join for the possibility that the constant zip condition could speed up the join. See Shawn's excellent response. PB - Harald Fuchs wrote: In article [EMAIL PROTECTED], Peter Brawley [EMAIL PROTECTED] writes: Scott, sorry, my mistake, SELECT price FROM fedex_zones z INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947 WHERE r.weight = 25; PB Although correct, many people consider this bad style - the ON clause of the JOIN should contain only the join condition(s). So it would be better to say SELECT price FROM fedex_zones z JOIN fedex_rates r ON z.zone = r.zone WHERE r.weight = 25 AND z.zip = 94947 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
possible join
Getting a little stuck on this one: Table defs below: I have two tables, fedex_zones contains zip code to zone data, so for example, zip 94947 is in zone 8 select zone from fedex_zones where zip = '94947' 8 Now, in the defex_rates table is how, based on weight, I can look up how much it will cost to ship. Say the weight is 12. select z_8 from fedex_rates where weight = 8 In part, my trouble is that I need to take the resuling zone from the first select and use that to determine the field name. I can easily do this in my code in 2 selects, but was hoping to be able to get the price back in just one select, if possible. mysql describe fedex_rates; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | weight | int(11) | | | 0 || | z_2| decimal(10,2) | | | 0.00|| | z_3| decimal(10,2) | | | 0.00|| | z_4| decimal(10,2) | | | 0.00|| | z_5| decimal(10,2) | | | 0.00|| | z_6| decimal(10,2) | | | 0.00|| | z_7| decimal(10,2) | | | 0.00|| | z_8| decimal(10,2) | | | 0.00|| | z_9| decimal(10,2) | | | 0.00|| | z_10 | decimal(10,2) | | | 0.00|| | z_14 | decimal(10,2) | | | 0.00|| | z_17 | decimal(10,2) | | | 0.00|| | z_51 | decimal(10,2) | | | 0.00|| | z_54 | decimal(10,2) | | | 0.00|| | z_92 | decimal(10,2) | | | 0.00|| | z_96 | decimal(10,2) | | | 0.00|| | z_22 | decimal(10,2) | | | 0.00|| | z_23 | decimal(10,2) | | | 0.00|| | z_25 | decimal(10,2) | | | 0.00|| ++---+--+-+-++ mysql describe fedex_zones; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | zip | char(5) | | UNI | || | zone | char(2) | | | || +---+-+--+-+-++ -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible join
Scott, ...In part, my trouble is that I need to take the resuling zone from the first select and use that to determine the field name. I can easily do this in my code in 2 selects, but was hoping to be able to get the price back in just one select, if possible... If you have control over the data model, it would be good to change the structure of fedex_rates to (id int PK, zone int, weight int, price decimal(10,2)), getting rid of the denormalised z_* columns which are causing you problems. Then a one-stage query would just be SELECT price FROM fedex_rates WHERE zone=8 AND weight=12. If you're stuck with the table structure you show, you're stuck with two queries. If these lookup tables aren't large, there's probably not much performance to be gained from hiding the two stages inside a stored procedure, but if you want a one-step, IMO that's the way to go. PB - Scott Haneda wrote: Getting a little stuck on this one: Table defs below: I have two tables, fedex_zones contains zip code to zone data, so for example, zip 94947 is in zone 8 select zone from fedex_zones where zip = '94947' 8 Now, in the defex_rates table is how, based on weight, I can look up how much it will cost to ship. Say the weight is 12. select z_8 from fedex_rates where weight = 8 In part, my trouble is that I need to take the resuling zone from the first select and use that to determine the field name. I can easily do this in my code in 2 selects, but was hoping to be able to get the price back in just one select, if possible. mysql describe fedex_rates; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | weight | int(11) | | | 0 || | z_2| decimal(10,2) | | | 0.00|| | z_3| decimal(10,2) | | | 0.00|| | z_4| decimal(10,2) | | | 0.00|| | z_5| decimal(10,2) | | | 0.00|| | z_6| decimal(10,2) | | | 0.00|| | z_7| decimal(10,2) | | | 0.00|| | z_8| decimal(10,2) | | | 0.00|| | z_9| decimal(10,2) | | | 0.00|| | z_10 | decimal(10,2) | | | 0.00|| | z_14 | decimal(10,2) | | | 0.00|| | z_17 | decimal(10,2) | | | 0.00|| | z_51 | decimal(10,2) | | | 0.00|| | z_54 | decimal(10,2) | | | 0.00|| | z_92 | decimal(10,2) | | | 0.00|| | z_96 | decimal(10,2) | | | 0.00|| | z_22 | decimal(10,2) | | | 0.00|| | z_23 | decimal(10,2) | | | 0.00|| | z_25 | decimal(10,2) | | | 0.00|| ++---+--+-+-++ mysql describe fedex_zones; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | zip | char(5) | | UNI | || | zone | char(2) | | | || +---+-+--+-+-++ No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: possible join
on 5/10/05 8:29 PM, Peter Brawley at [EMAIL PROTECTED] wrote: Scott, ...In part, my trouble is that I need to take the resuling zone from the first select and use that to determine the field name. I can easily do this in my code in 2 selects, but was hoping to be able to get the price back in just one select, if possible... If you have control over the data model, it would be good to change the structure of fedex_rates to (id int PK, zone int, weight int, price decimal(10,2)), getting rid of the denormalised z_* columns which are causing you problems. Then a one-stage query would just be SELECT price FROM fedex_rates WHERE zone=8 AND weight=12. If you're stuck with the table structure you show, you're stuck with two queries. If these lookup tables aren't large, there's probably not much performance to be gained from hiding the two stages inside a stored procedure, but if you want a one-step, IMO that's the way to go. Ok, I changed the tables around a little, I can not really do this all in one table, since the data gets made new often by fedex, at any rate, (no pun intended :-))... mysql describe fedex_zones; +---+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-++ | id| int(11) | | PRI | NULL| auto_increment | | zip | char(5) | | UNI | || | zone | char(2) | | | || +---+-+--+-+-++ mysql describe fedex_rates; ++---+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | weight | int(11) | | | 0 || | zone | int(11) | | | 0 || | price | decimal(10,2) | | | 0.00|| ++---+--+-+-++ so first, I need to get the zone I am in, which is a: SELECT zone from fedex_zones where zip = 94947 8 If the result in that case is 8, then I can select price from fedex_rates where zone = '8' and weight = '25' For some reason, this join is still not screaming out at me, or maybe I have it right, and my data is in duplication, any help is appreciated. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
