Hey all, I am using this query using UNION, where both fields that contains the amount are combined into one table, and i wanted to split them into 2 fields, first for cash and other for cheque SELECT exp_cat.cat_name, SUM(exp_cheque.exp_amount) AS Cheque FROM exp_cat INNER JOIN exp_cheque ON exp_cat.ID = exp_cheque.exp_cat_id GROUP BY...
I've decided to rewrite a database I have that is poorly normalized. I've created the new database but now need to move data from the old database into the new one. I'm not sure exactly how to accomplish though. For example, in the old database I have a webDorms table that looks like this: id (PK, int) room_type (varchar) description (...
hi there, I need to query a sales table and have the resulting table show the breakdown for different time periods within the given day. For example, the table has the following fields: Id (int), EntryDate (datetime), SaleReference (varchar) I'd like to produce a table that looks like this: Date Sales 9am-12pm Sales 12p...
I have one column of varchar type which store numbers like 10, 11, 16.5, 24, 43, 12, 100 etc I want to order these field. but it sort like these 10 11 12 16.5 100 24 43 expected 10 11 12 16.5 24 43 100 please help ...
I have the following code: FROM CTE_Order cte INNER JOIN tblOrders o ON cte.OrderId = o.Id INNER JOIN tblOrderUnits ou ON o.id = ou.OrderId INNER JOIN tblOrderServiceUnits osu ON ou.VMSUnitID = osu.UnitId When I join the ou I get 2 of the same unit Id's. This make the Inner Join...
I have a multithreaded process which inserts several records into a single table. The inserts are performed in a stored procedure, with the sequence being generated INTO a variable, and that variable is later used inside of an INSERT. Given that I'm not doing mysequence.nextval inside the INSERT itself, it makes me think that it is pos...
My SQL-Query should return a previous record(Claim). Previous means that it has a different primary key (idData), an equal SSN_Number and an earlier Received_Date. The problem is that the Received_Date could be equal so I have to look for another column. The priority should be the same as the Sort-Order. What am I doing wrong, because th...
My base table is like: ColumnA|ColumnB --------------- A | C1 A | C2 A | C3 B | C1 B | C3 C | C4 I want to read records from the base table and write it into the below table: ColumnA | C1 | C2 | C3 | C4 ---------------------------- A | Y | Y | Y | N B | Y | N | Y | N C | N ...
This is dragging at my nerves! Scenario: I am using the geocoder plugin: http://github.com/alexreisner/geocoder/ I have two models: Address and Item the latitude and the longitude are in Address and an Item belongs_to :address Now, when I try to do a near search: Item.near(Person.first.address.coordinates, 20) on an Item it fails with...
I have three database tables - one containing units, one containing categories of those units, and one containing user permissions. If a user can access a category, they are allowed to access any units within the category. I am trying to create a query that will retrieve a list of units they are allowed to access. So far I have a very ...
I want to have an instead of delete trigger so that I can get text field values out of the table row that's being deleted to preserve those fields when the actual delete occurs. For some reason I can't pull them from the Deleted table in a standard Delete trigger (SQL errors out). Is there a way to do an actual delete within in an "inst...
I am trying to keep a rolling checksum to account for order, so take the previous 'checksum' and xor it with the current one and generate a new checksum. Name Checksum Rolling Checksum ------ ----------- ----------------- foo 11829231 11829231 bar 27380135 checksum(27380135 ^ 11829231) = 93291803 baz ...
I would like to embed a SELECT inside a COUNT, but I can't find any examples. #pseudosql SELECT a AS current_a, COUNT(*) AS b, COUNT( SELECT FROM t WHERE a = current_a AND c = 'const' ) as d, from t group by a order by b desc ...
In the following query: SELECT column1,column2 FROM table1 ORDER BY column1 LIMIT 0,30 How can I find out the number of rows that would have been returned were it not for the LIMIT? Edit: I am looking for a way to work this into the query above and not do a separate query. (If possible.) ...
Is it possible to grab data from two tables (that have the same fields) into one view. Basically, so the view sees the data as if it was one table. ...
Hi, I have two tables in MySQL, one containing a field City and one containing a field HomeCounty. I need to get X number of records sorted alphabetically so that both City and HomeCounty are taken into the set. I can't join, because these two tables have no relation... and because I need these two columns to be "one", not two. So, I ...
i have the following sql check and it accepts values that are supposed to be two uppercase letters followed by 3 numeric values. CREATE TABLE Project( projectID NCHAR(5) NOT NULL PRIMARY KEY, CHECK(projectID LIKE '[A-Z][A-Z][0-9][0-9][0-9]'), projectName NVARCHAR(20) NOT N...
I have a stored procedure that has a bunch of input and output parameters because it is Inserting values to multiple tables. In some cases the stored proc only inserts to a single table (depending on the input parameters). Here is a mocked up scenario to illustrate. Tables / Data Objects: Person Person.Id Person.Name Person.Address N...
I have a SAS dataset with around 3,000 variables, and I would like to get rid of the character variables for which all values are missing. I know how to do this for numeric variables-- I'm wondering specifically about the character variables. I need to do the work using base SAS, but that could include proc SQL, which is why I've tagged...
I'm trying to run one SQL command to update multiple databases. If I copy 'n paste sql code below directly into PHPMYADMIN it executes just fine, but when I run the sql through php it doesn't update? If I run the updates for each database separately via my php script they both update fine, so I'm confused? What am I doing wrong? SQ...