Main > Software Forum
SQL for "control sets" help requested
Chris:
--- Quote from: screaming on September 15, 2006, 10:27:25 am ---Force of habit, I guess. It's just what I've always done... The sqlite engine is smart enough to rewrite the joins in there. I wonder which way would be faster?
--- End quote ---
Depends on the SQL engine, indexes, primary keys, etc. If I recall, SQLite (don't know why I said TinySQL before, that's Java) doesn't have a lot of the niceties of a full-fledged relational database (it just treats each record as a string, exactly like WinCab does). JOINS are the "textbook" way to do it, and thus I would expect that they would be more efficient, but in a project like this, all that's important is that you get the desired results. Your approach was was sufficiently different to the way I was trained that I thought it might be a SQLite technical limitation you were working around.
screaming:
Nope! If you were trained at all then your way is probably more correct :) My experience is 100% hands on, and I've never been trained.
Buddabing:
Okay, how about:
--- Code: ---create temporary table gamedata
(
romname varchar[12] unique primary key;
... other fields...
);
create temporary table controldata
(
romname varchar[12] ;
control varchar[36];
... other fields...
);
select * from
(gamedata inner join
(select * from controldata where controldata.control='trackball')
on gamedata.romname=controldata.romname);
--- End code ---
The engine is SQLite 2.
I don't want to get any duplicate rows, just one row per game, on the main select.
youki:
Sorry i didn't read all the thread so may be i repeat something.
I think you have to do a new table : ControlSet
with column :
ControlSetID
GameID
Control
then you could query like that
Select distinct GameId , (other columns..) from gamedata , ControlSet where (gamedata.GameID=controlset.GameID And controlset.Control like "Trackball");
Sorry, my SQL is a little bit rusty... but just to give an Idea.
Chris:
--- Quote from: Buddabing on September 15, 2006, 11:13:14 am ---I don't want to get any duplicate rows, just one row per game, on the main select.
--- End quote ---
Then when doing your main select, don't include the INNER JOIN. If you do include the INNER JOIN without the WHERE clause you'll get back one row for each control in a game.
--- Quote ---
--- Code: ---select * from
(gamedata inner join
(select * from controldata where controldata.control='trackball')
on gamedata.romname=controldata.romname);
--- End code ---
--- End quote ---
This doesn't look quite right. Try:
--- Code: ---select *, controldata.* from gamedata
inner join controldata on gamedata.romname=controldata.romname
where controldata.control='trackball';
--- End code ---
Although you really don't need * from controldata; that will get you romname twice. All you care about in controldata is the name of the control, so:
--- Code: ---select *, controldata.control from gamedata
inner join controldata on gamedata.romname=controldata.romname
where controldata.control='trackball';
--- End code ---
Something to note: if a game has no entry in the controldata table, it won't appear in the output AT ALL. Of course, this shouldn't happen. But if there are really games that have no control in the table, the INNER JOIN ("only include records that are in both tables") should be a LEFT OUTER JOIN ("always include all records from the table on the left").
--Chris
Navigation
[0] Message Index
[#] Next page
[*] Previous page
Go to full version