[PHP-DB] Left Join
I am tyring to build a query that will take the most recent stores.store_date column then join it with store_list.store_name where store_list.store_name and stores.store_subject match then return the most recent stores.store_date. I am guessing this will require some sort of join. I have been tyring to figure it out but have had no luck. Any help is greatly apprciated. Thank you. mysql select * from stores; +--+-++-+---+--+-+-- ---+ | store_id | store_subject | store_type | store_date | store_mar | store_by | store_tptest| store_comment s | +--+-++-+---+--+-+-- ---+ | 78 | Bella Roe | corporate | 2011-06-28 15:01:02 | 0 |1 | | | | 79 | Bella | corporate | 2011-08-06 08:48:26 | 0 |1 | 600kbps-3.8mbps | test | | 80 | Brittney~2120 | corporate | 2011-08-06 08:48:50 | 1 |1 | 600kbps-3.8mbps | should post t o Wichita! | | 81 | Motor | premier| 2011-08-06 14:52:55 | 1 |1 | 600kbps-3.8mbps | test | | 82 | Liberty | corporate | 2011-08-06 14:54:41 | 0 |1 | 0-250kbps | test | | 83 | Seneca~3165 | corporate | 2011-08-06 14:58:47 | 0 |1 | 600kbps-3.8mbps | test | | 84 | Liberty | corporate | 2011-08-06 14:59:32 | 0 |1 | 400-600kbps | test | | 85 | Brittney~2120 | corporate | 2011-08-06 15:00:43 | 0 |1 | 600kbps-3.8mbps | test | | 86 | Liberty | corporate | 2011-08-06 15:01:11 | 0 |1 | 300-400kbps | test | | 87 | Brittney~2120 | corporate | 2011-08-06 15:11:06 | 0 |1 | 250-300kbps | test | | 88 | Brittney~2120 | corporate | 2011-08-06 15:23:53 | 1 |1 | 600kbps-3.8mbps | test | | 89 | Bella | corporate | 2011-08-06 15:31:49 | 0 |1 | 600kbps-3.8mbps | test | | 90 | Cleartalk~10221 | premier| 2011-08-06 15:32:54 | 0 |1 | 250-300kbps | test | | 91 | Chit| premier| 2011-08-06 15:33:50 | 1 |1 | 250-300kbps | test | | 92 | || 2011-08-06 15:34:21 | 2 |1 | 300-400kbps | test | | 93 | Bella | corporate | 2011-08-07 11:26:13 | 0 |1 | 600kbps-3.8mbps | test | +--+-++-+---+--+-+-- ---+ 16 rows in set (0.00 sec) mysql mysql select * from store_list LIMIT 15;' ++---+++-+---+ | id | market_prefix | store_name | store_type | market_name | id_market | ++---+++-+---+ | 1 | MCI | Bella Roe~4980 Roe Blvd| Corporate | Kansas City | 0 | | 2 | MCI | Cleartalk~4635 Shawnee Dr | Premier| Kansas City | 0 | | 3 | MCI | Cleartalk~3612 State Avenue| Premier| Kansas City | 0 | | 4 | MCI | Endless Wireless~1620 Central Avenue | Premier| Kansas City | 0 | | 5 | MCI | Get Wireless~840 Minnesota | Premier| Kansas City | 0 | | 6 | MCI | Ring Ring Wireless~7559 State Avenue | Premier| Kansas City | 0 | | 7 | MCI | Cleartalk~1212 Sante Fe| Premier| Kansas City | 0 | | 8 | MCI | Cleartalk~10221 W 75th Street | Premier| Kansas City | 0 | | 9 | MCI | Free Talk~10830 Shawnee Mission Pkwy | Premier| Kansas City | 0 | | 10 | MCI | Loma Vista~8712 Blue Ridge Blvd| Corporate | Kansas City | 0 | | 11 | MCI | Ring Ring Wireless~25 W. 39th Street | Premier| Kansas City | 0 | | 12 | MCI | Ring Ring Wireless~3039 Prospect Ave | Premier| Kansas City | 0 | | 13 | MCI | Ring Ring Wireless~1201 E Linwood Blvd | Premier| Kansas City | 0 | | 14 | MCI | Ring Ring Wireless~4314 Rainbow Blvd | Premier| Kansas City | 0 | | 15 | MCI | Ring Ring Wireless~7445 Prospect Ave. | Premier| Kansas City | 0 | ++---+++-+---+ 15 rows in set (0.00 sec)
Re: [PHP-DB] Left Join
Maybe it's just me, but I can't see anything that would work as foreign key for you to join on - neither table seems to have a foreign key On 7 August 2011 21:53, Chris Stinemetz chrisstinem...@gmail.com wrote: I am tyring to build a query that will take the most recent stores.store_date column then join it with store_list.store_name where store_list.store_name and stores.store_subject match then return the most recent stores.store_date. I am guessing this will require some sort of join. I have been tyring to figure it out but have had no luck. Any help is greatly apprciated. Thank you. mysql select * from stores; +--+-++-+---+--+-+-- ---+ | store_id | store_subject | store_type | store_date | store_mar | store_by | store_tptest | store_comment s | +--+-++-+---+--+-+-- ---+ | 78 | Bella Roe | corporate | 2011-06-28 15:01:02 | 0 | 1 | | | | 79 | Bella | corporate | 2011-08-06 08:48:26 | 0 | 1 | 600kbps-3.8mbps | test | | 80 | Brittney~2120 | corporate | 2011-08-06 08:48:50 | 1 | 1 | 600kbps-3.8mbps | should post t o Wichita! | | 81 | Motor | premier | 2011-08-06 14:52:55 | 1 | 1 | 600kbps-3.8mbps | test | | 82 | Liberty | corporate | 2011-08-06 14:54:41 | 0 | 1 | 0-250kbps | test | | 83 | Seneca~3165 | corporate | 2011-08-06 14:58:47 | 0 | 1 | 600kbps-3.8mbps | test | | 84 | Liberty | corporate | 2011-08-06 14:59:32 | 0 | 1 | 400-600kbps | test | | 85 | Brittney~2120 | corporate | 2011-08-06 15:00:43 | 0 | 1 | 600kbps-3.8mbps | test | | 86 | Liberty | corporate | 2011-08-06 15:01:11 | 0 | 1 | 300-400kbps | test | | 87 | Brittney~2120 | corporate | 2011-08-06 15:11:06 | 0 | 1 | 250-300kbps | test | | 88 | Brittney~2120 | corporate | 2011-08-06 15:23:53 | 1 | 1 | 600kbps-3.8mbps | test | | 89 | Bella | corporate | 2011-08-06 15:31:49 | 0 | 1 | 600kbps-3.8mbps | test | | 90 | Cleartalk~10221 | premier | 2011-08-06 15:32:54 | 0 | 1 | 250-300kbps | test | | 91 | Chit | premier | 2011-08-06 15:33:50 | 1 | 1 | 250-300kbps | test | | 92 | | | 2011-08-06 15:34:21 | 2 | 1 | 300-400kbps | test | | 93 | Bella | corporate | 2011-08-07 11:26:13 | 0 | 1 | 600kbps-3.8mbps | test | +--+-++-+---+--+-+-- ---+ 16 rows in set (0.00 sec) mysql mysql select * from store_list LIMIT 15;' ++---+++-+---+ | id | market_prefix | store_name | store_type | market_name | id_market | ++---+++-+---+ | 1 | MCI | Bella Roe~4980 Roe Blvd | Corporate | Kansas City | 0 | | 2 | MCI | Cleartalk~4635 Shawnee Dr | Premier | Kansas City | 0 | | 3 | MCI | Cleartalk~3612 State Avenue | Premier | Kansas City | 0 | | 4 | MCI | Endless Wireless~1620 Central Avenue | Premier | Kansas City | 0 | | 5 | MCI | Get Wireless~840 Minnesota | Premier | Kansas City | 0 | | 6 | MCI | Ring Ring Wireless~7559 State Avenue | Premier | Kansas City | 0 | | 7 | MCI | Cleartalk~1212 Sante Fe | Premier | Kansas City | 0 | | 8 | MCI | Cleartalk~10221 W 75th Street | Premier | Kansas City | 0 | | 9 | MCI | Free Talk~10830 Shawnee Mission Pkwy | Premier | Kansas City | 0 | | 10 | MCI | Loma Vista~8712 Blue Ridge Blvd | Corporate | Kansas City | 0 | | 11 | MCI | Ring Ring Wireless~25 W. 39th Street | Premier | Kansas City | 0 | | 12 | MCI | Ring Ring Wireless~3039 Prospect Ave | Premier | Kansas City | 0 | | 13 | MCI | Ring Ring Wireless~1201 E Linwood Blvd | Premier | Kansas City | 0 | | 14 | MCI | Ring Ring
Re: [PHP-DB] Left Join
On Sun, Aug 7, 2011 at 3:00 PM, Peter Lind peter.e.l...@gmail.com wrote: Maybe it's just me, but I can't see anything that would work as foreign key for you to join on - neither table seems to have a foreign key Sorry for my ignorance. How do I create the foreign key? The two columns from each table that have a similar relationship are stores.store_mar and store_list.id_market. Once the foreign key is build what would be the correct syntax to achieve my query? Thank you very much, Chris
RE: [PHP-DB] Left Join
Is the design under your control? If so, you need to change it. Your store_list.store_name field breaks the first rule of relational databases - it isn't atomic. That means that you have two pieces of information there - the store name and store address. You even have a '~' there to separate them (nice try, but no). It's not clear what the function of the 'stores' table is, but there's a nasty smell coming from there as well (and a field with tilde-separated data too!) If this *is* your design, then you might want to show what the source data looks like. If it's not your design, you have my permission to tell the owner off ;-) Not that this really has anything to do with PHP, but I won't say anything if you won't. Toby -Original Message- From: Chris Stinemetz [mailto:chrisstinem...@gmail.com] Sent: Sunday, August 07, 2011 9:33 PM To: Peter Lind Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Left Join On Sun, Aug 7, 2011 at 3:00 PM, Peter Lind peter.e.l...@gmail.com wrote: Maybe it's just me, but I can't see anything that would work as foreign key for you to join on - neither table seems to have a foreign key Sorry for my ignorance. How do I create the foreign key? The two columns from each table that have a similar relationship are stores.store_mar and store_list.id_market. Once the foreign key is build what would be the correct syntax to achieve my query? Thank you very much, Chris -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
