| Main > Software Forum |
| SQL for "control sets" help requested |
| << < (3/3) |
| screaming:
Chris, you're so wickid smaht. Can i be your friend? ;) |
| Buddabing:
What I really want is to not include the fields from controldata in the select, so that the default: --- Code: ---select * from gamedata; --- End code --- will always work. So, I guess I need a select with no extra fields. --- Code: --- select gamedata.* from (gamedata inner join controldata on gamedata.romname=controldata.romname) where controldata.control='trackball'; --- End code --- Thanks for your help! |
| Chris:
--- Quote from: Buddabing on September 15, 2006, 11:55:38 am ---What I really want is to not include the fields from controldata in the select, so that the default: --- Code: ---select * from gamedata; --- End code --- will always work. So, I guess I need a select with no extra fields. --- Code: --- select gamedata.* from (gamedata inner join controldata on gamedata.romname=controldata.romname) where controldata.control='trackball'; --- End code --- Thanks for your help! --- End quote --- You would get it anyway; my example was slightly wrong. So just remove the parenthesis from your version and you'll get it all (but again, romname will appear twice in the output records). For my example to work the way I intended, the first * should have been gamedata.*. |
| SirPoonga:
Budda, I really should show you how the controls.dat database is organized :) screaming, don't worry, I was taught to use the where too because at that time it was the most supported technique. I do inner joins now but you will see if I am in a hurry to find some data I will use the where clause. I just wanted to make a note that there is a RIGHT outer join to include all results on the right BUT it is LEFT reversed. In fact older MySQL versions only supported LEFT so you just rearrange the order your tables were listed :) Correction Chris select *, controldata.control That will return ALL columns from ALL table and an extra controldata.control column. Select * FROM table1, table2 blah blah blah will return all columns from all tables Select table1.*, table2.GUID FROM table1, table2 blah blah blah will return all columns from tbale1 and the GUID column form table2 Also screaming you did have the more proper way of organizing the data... FROM gamedata game , controldata control , mappingtable m A mapping table, this will only work if budda makes a seperate table with all the control types in a table. Budda, this is the proper way to do it (3NF normalized) but you can probably get away with just having a controls table that has a game id in it. Edit: I wrote this after screaming made his post, I see you corrected yourself before I finished typing :) |
| Chris:
--- Quote from: SirPoonga on September 15, 2006, 12:10:39 pm ---I just wanted to make a note that there is a RIGHT outer join to include all results on the right BUT it is LEFT reversed. In fact older MySQL versions only supported LEFT so you just rearrange the order your tables were listed :) --- End quote --- There's a good point in this sentence: different SQL implementations use different "dialects" of SQL. I run into this problem all the time taking queries from my Microsoft SQL Server to our AS/400. Basic SELECTs should be the same everywhere, but just be aware that you may not be able to simply cut and paste examples of more complicated queries without a little tinkering. Isn't SQL fun? --Chris |
| Navigation |
| Message Index |
| Previous page |