It has been mentioned to me i should separate columns that are used often from columns that are not. Why? Is it because a table with less columns is better for the cache? Is there an optimization for caching all columns rather than selected? Is this for tables frequently read? Should I separate freq write columns into their own table? ...
I have the following: declare @PrintJob TABLE ( PageNumber Int, Copies Int ) INSERT INTO @PrintJob(PageNumber,Copies) VALUES(1,100) INSERT INTO @PrintJob(PageNumber,Copies) VALUES(2,100) INSERT INTO @PrintJob(PageNumber,Copies) VALUES(3,100) INSERT INTO @PrintJob(PageNumber,Copies) VALUES(4,100) INSERT INTO @PrintJob(PageNumber,Cop...
I am working with an old MySQL table, which serves as a log of sorts. It looks like CREATE TABLE `queries` ( `Email` char(32) NOT NULL DEFAULT '', `Query` blob, `NumRecords` int(5) unsigned DEFAULT NULL, `Date` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Now, I need to be able to UPDATE the records in this table ...
I have a table with an audit log: BugId Timestamp Status 1 2010-06-24 10:00:00 open 2 2010-06-24 11:00:00 open 1 2010-06-25 12:00:00 closed 2 2010-06-26 13:00:00 closed I want a running total of open and closed bugs like: Timestamp # Status 2...
I have a table with two foreign keys in one table. Table PROJECTS - Id - Owner - FK - Client - FK and table USERS - Id - Name I'd like to select all projects with appropriate names of the owner and client The result should look like: Id | OwnerName | ClientName ...
I currently have an INFORMIX-SQL app I wish to re-write in OpenEdge/4GL(non-GUI) client with the Personal engine and would like to know the methods and details for dealing with record arrays where I need to display, update and add multiple transaction rows within Progress' forms. I also have several question regarding functionality, e.g....
I've got a table in MySQL that looks roughly like: value | count ------------- Fred | 7 FRED | 1 Roger | 3 roger | 1 That is, it was created with string ops outside of MySQL, so the values are case- and trailing-whitespace-sensitive. I want it to look like: value | count ------------- Fred | 8 Roger | 4 That is, managed by MySQ...
Hi, I'm pretty new to databases and sql. I have a problem where I have two tables which both contain a foreign key to the primary key of the other. My problem is I have a large number of elements which can have multiple names in different languages, but MUST have a single primary name/language. Firstly, I want to know if this is possib...
I have to tables, users and classes. I need to show the classes count of each user with user ID and i have to show those users as well .. with no classes. how to do it .. ...
I'm trying to extract data from a many to many table configuration. I'm sorry if this explanation is vague, but it's the best I can do without a lengthy explanation of the whole inner workings of my program. I have 2 modules in an application. Module one shows information relevant to a group of items. And it strips out information tha...
I want to show the BookTitle, Firstname along with a COUNT of Copies. When I run the query, it gives an error saying to group. But when I group it says not a expression to group. Can someone help me? SELECT bk.BookTitle, au.FirstName, COUNT(bkc.BookCopyID), rb.ReservedDate FROM Book bk, Book_Author ba, BookCopy bkc, ReserveBook rb, Aut...
Hi All, What is meaning of single column in Join condition expession. Eg. In the following query, what table5.col_int_key and table2.pk predicate do ? Is it simply colval !=0 ? SELECT STRAIGHT_JOIN COUNT(table1 .col_int) FROM g table1 RIGHT JOIN e table2 LEFT JOIN m table3 LEFT ...
Is it possible to reseed an auto increment column in a SQLite database, and if so, how is this done? ie. The equivalent of DBCC CHECKIDENT ('MyTable', RESEED, 1) in SQL Server. ...
I have to search user according by city but my problem is that in user table in city field there are two cities like arizona@losvegas, because in registration user can select two cities. So how can I search city by city name? Like if someone searches for all users from arizona... I have done this by using LIKE in SELECT query but I wan...
Hi, I want to compare the individual words from the user input to individual words from a column in my table. For example, consider these rows in my table: ID Name 1 Jack Nicholson 2 Henry Jack Blueberry 3 Pontiac Riddleson Jack Consider that the user's input is 'Pontiac Jack'. I want to assign weights/ranks for each match, so I ...
Suppose I have two queries on a database table. The queries are defined in terms of fields used in the query: Query1: depends on f1, f2, and f3 Query2: depends on f1, f2, f3 and f4 I remember reading somewhere that the SQL query engine (mySQL in this case) parses the index tree starting from the leftmost fields in the index. If that...
I have a table doctors ( id int, name varchar(255) ) where name like "Sername Name". In query i need only Sername of doctor. How can i do it with standard or with solution on most RDBMS? I know only two solution. First is SUBSTRING(input SIMILAR pattern ESCAPE escape-char) in postgres is SUBSTRING(input FROM pattern-string). Sec...
When researching on how to do the classic "get POI in range" problem I've found that the most used algorithms are Haversine and if you need real accuracy then Vincenty's formula. I went the first one because high accuracy wasn't an issue. However, it got me thinking on something that hits me as odd, why is that I found no references to c...
Problem I am trying to write a stored procedure to select a list of NewsItem records where each NewsItem has all of the Categories that are in a list. If the list is empty then it should return all news items. NewsItem NewsItemCategories Category -------- ------------------ -------- NewsID NewsID C...
Hello, I have 3 tables; game, platform, game_platform game id name desc ---- ------------ --------- 1 Modern Warfare... Awesome game...... 2 ... ... 3 ... ... platform id name ---- ------------ 1 pc 2 ps3 3 ... game_platform game_id platform_i...