masteroleary Posted February 7, 2007 Share Posted February 7, 2007 This is what I have so far. Im completely new to mysql. I need to get site.name with the data model_shoot.model_id My tables include two columns: model_shoot.model_id model_shoot.shoot_id shoot_site.shoot_id shoot_site.site_id site.id site.name The second column ids are ; delimited (i.e. model_shoot.shoot_id = 1;4;5;7) SELECT site.name FROM site JOIN shoot_site ON shoot_site.site_id LIKE site.id JOIN model_shoot ON model_shoot.shoot_id LIKE shoot_site.shoot_id WHERE model_shoot.model_id = '6' Quote Link to comment Share on other sites More sharing options...
artacus Posted February 7, 2007 Share Posted February 7, 2007 Not the best design. You would make your life easier if you put those in a separate table. If you insist on doing it this way you can use commas to separate and join on FIND_IN_SET Quote Link to comment Share on other sites More sharing options...
masteroleary Posted February 7, 2007 Author Share Posted February 7, 2007 can you give me an example of putting 'those' in a separate table? There are multiple shoots to one model, and multiple sites to one shoot. Quote Link to comment Share on other sites More sharing options...
artacus Posted February 7, 2007 Share Posted February 7, 2007 pretty much just like I said. They're just small tables with 2 or 3 columns that link one to many's or many to many's. eg. model_shoots table id model_id shoot_id 1 22 4 2 24 4 Quote Link to comment Share on other sites More sharing options...
masteroleary Posted February 7, 2007 Author Share Posted February 7, 2007 So if i have this i would say 'SELECT site.name FROM site JOIN shoot_site ON shoot_site.site_id = site.id JOIN model_shoot ON model_shoot.shoot_id = shoot_site.shoot_id WHERE model_shoot.model_id = 1' ?? and i should get two rows with names for site 1 & 2? Table model_shoot id model_id shoot_id 1 1 4 2 1 5 3 2 1 Table shoot_site id shoot_id site_id 1 4 1 2 4 2 3 5 2 Quote Link to comment Share on other sites More sharing options...
artacus Posted February 7, 2007 Share Posted February 7, 2007 That's how it works. Quote Link to comment Share on other sites More sharing options...
masteroleary Posted February 7, 2007 Author Share Posted February 7, 2007 Thanks Artacus, your the best! Quote Link to comment Share on other sites More sharing options...
artacus Posted February 7, 2007 Share Posted February 7, 2007 I know. And woohoo! That was my 500th post and now I've got another star. Only 30 more stars before I catch fenway. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 7, 2007 Share Posted February 7, 2007 I know. And woohoo! That was my 500th post and now I've got another star. Only 30 more stars before I catch fenway. Are you pondering what I'm pondering... lol. Quote Link to comment Share on other sites More sharing options...
artacus Posted February 7, 2007 Share Posted February 7, 2007 Narf! I think so fenway, but where are we going to find a forum full of noobies and an SQL Injection tutorial at this hour? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 7, 2007 Share Posted February 7, 2007 Narf! I think so fenway, but where are we going to find a forum full of noobies and an SQL Injection tutorial at this hour? If this keeps up, I may have to change my avatar ;-) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.