FAQ Database Discussion Community


creating stored procedure in mysql calculate profit from product table

mysql,sql,database,stored-procedures
My following code show some error: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN SET @v1:= (select sum( cost_price * current_stock) from product)' at line 2 CREATE PROCEDURE sp_profit BEGIN...

Combine multiple rows using SUM that share a same column value but has different other column values

sql,sql-server
I thought this would be a very simple query but for some reason, I can't seem to get the results I'm looking for. I have a table that has this structure. I just want a single entry for each account while summing the charges. I don't really care which date...

Purging Database - Count purged/not-purged tables

mysql,sql,sql-server,database,stored-procedures
So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...

JOIN on keys that don't have the same value

mysql,sql,join
I am trying to do an INNER JOIN on two tables that have similar values, but not quite the same. One table has a fully qualified host name for its primary key, and the other the hosts short name, as well as the subdomain. It it safe to assume that...

How to delete only the table relationed

sql,laravel,migration
I know how to create the migrations with laravel 5.1 and I have the following code public function up() { Schema::create('articulos', function (Blueprint $table) { $table->increments('id'); $table->string('title'); $table->string('content'); $table->integer('categoria_id')->unsigned(); $table->integer('creador_id')->unsigned(); $table->string('slug', 32); $table->timestamps(); }); Schema::table('articulos', function($table) { $table->foreign('categoria_id')->references('id')->on('categorias');...

Newbie to linking SQL tables

sql,sql-server
I'm new to SQL, so forgive me if I am understanding this incorrectly. I have one table named Employees, where I have columns for: EmployeeID - This is Int Identity (1,1) FirstName LastName I have a second table named Calendar, with the calendar information populated for the next 50 years....

pivot not working in SSIS

sql,sql-server,ssis
My SQL query works fine when i execute it in SQL management studio but throw the error below when execute in SSIS package. Any advise? **When click on Build Query of the syntax - it shows The PIVOT SQL construct or statement is not supported. Case 1: SELECT listid, [1]...

SQL Server: Issues with data type

sql,sql-server,toad
SELECT pa.[type], (SUM(pa.Actions_Logged)/SUM(pi.Impressions_Served)) AS ActionRates from Performance_Actions pa INNER JOIN Performance_Impressions pi ON pa.Alternative = Pi.Alternative GROUP BY pa.[type]; The above query generates an error: Lookup Error - SQL Server Database Error: Arithmetic overflow error converting expression to data type int. Both pa.Actions_Logged and pi.Impressions_Served are int and the result...

Conversion failed when converting date and/or time from character string in sql server

sql,sql-server
I have a function to convert a string to datetime (101) format. But it gives me an error when I convert this value. 2016-03-01 00:00:00.0000000 And the error is Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string. And my function...

Select count Columns group by No

sql,sql-server
Emp_No Emp_Shift Emp_Date 500 AL 1/5/2015 600 S 2/5/2015 600 H 3/5/2015 500 S 4/5/2015 500 AL 5/5/2015 600 AL 6/5/2015 I need help on this issue , HOW TO RETURN count in 3 Columns >> EX: Emp_No Count Al Count S Count H 500 2 1 0 600 1...

Create a unique URL for linking to data pulled from a database [closed]

javascript,html,sql,google-maps,maps
Is it possible to create a unique URL to link to information that is called and displayed from a database? [I am struggling to articulate this question, so any suggestions for revising it are welcome.] Specifics: I have a page that includes an embedded map and related information about each...

Dynamic Query Generation is not working with SQL

sql,sql-server
I am trying to execute following SQL statement : USE[Northwind] declare @MainQuery varchar(255), @WhereClause varchar(255) Select @WhereClause = 'where CustomerId =ALFKI' Select @MainQuery ='Select * from Customers [email protected] execute(@MainQuery) But getting the error , Msg 207, Level 16, State 1, Line 1 Invalid column name 'ALFKI'. What is wrong ?...

converting varchar to Int SQL SERVER

sql,sql-server,casting
I needed help with something I am not entirely sure how to resolve. This is my code : SELECT DISTINCT Nr_of_Times_Cust_No_Appears=CASE WHEN CAST(a.TV_Code AS Int)-CAST(BB_Code AS Int)=0 THEN COUNT(*) OVER (PARTITION BY BB_Code) ELSE 'Not same' END FROM table Basically, the above code is meant to make sure that the...

Can't output Guid Hashcode

sql,vb.net,guid,hashcode
I'm trying to use the hashcode of a guid as a random voucher name for a website, and have been trying to write new records to a SQL table with the Hashcode variable but it doesn't work. command.Parameters.Add("@voucherName", SqlDbType.NVarChar) command.Parameters("@voucherName").Value = Guid.NewGuid().GetHashCode() When using that code it just puts a...

Retrieve Values As Column

mysql,sql
I've the following table in my PHPMYADMIN The desired output is headline                              impressions              clicks Buy A new Iphone                 ...

timestamp SQL to Excel

php,mysql,sql,excel
If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

Inner Join 3 tables pl/sql

mysql,sql
Basically i want to inner join 3 tables and the case is understood by the query itself as follows. Tables: A has 2 columns column 1 and column 2 B has 2 columns column 3 and column 4 C has 3 columns column 5,column 6 and column 7 Query: select...

Count Rows On a Condition in SQL SERVER 2000

sql,sql-server-2000
I want to fetch data in a following format from SQL SERVER 2000 Department | TotalPresent | Employee | Officer XYZ 12 6 6 I am running this query SELECT a.department, Count(emp_id) AS totalpresent, CASE WHEN a.type = 'Employee' THEN Count(a.type) ELSE 0 END AS employee, CASE WHEN a.type =...

How do I find records in one table that do exist in another but based on a date?

mysql,sql
I want to find all the dealers who haven't had an order in 2015 yet. I know this query doesn't work but I thought it might be helpful to understand what I want to do. In this example I want to get just "Bob" as a result. He is the...

One identifier for set of values

sql,sql-server,sql-server-2008,sql-server-2008-r2
I have this two columns. One is filled with some data, second is null. col1|col2 --------- null| 72 null| 72 null| 72 null| 33 null| 33 null| 12 null| 12 null| 55 null| 72 I want to generate values for col1 that will gather and group values from col2. So...

Get items with 0 Counts after GroupBy in SQL

sql,sql-server
I want to get the total count of records for past 5 days, based on a boolean "IsInsert". The below query neglects all the days with zero Total. SELECT CAST(V.ChangeDate AS DATE) AS ChangeDate, COUNT(*) AS Total FROM CarrierRate.VendorBillUVBLog V WITH(NOLOCK) WHERE V.IsInsert = 1 And v.ChangeDate >= DATEADD(d,-4,GETDATE()) AND...

SQL Subquery column equals operation

sql,sql-server,tsql
I am trying to do a SQL query for user with certain permission enabled flag. I know, I can do this: select u.ID, u.Name, (select p.Value from Permissions p where p.UserID = u.ID AND p.Key = 'CanEdit') as IsPermissionEnabled from Users u But it's not exactly what I need, can...

Executing dynamically created SQL Query and storing the Query results as a temporary table

sql,sql-server
I am creating a SQL Query dynamically. After it's been created I want to execute it and store it as a temporary table. WITH [VALIDACCOUNTS] AS( EXEC (@sqlQuery)) ...

mysql_real_escape_string creates \ in server only not in local

php,sql
When I use mysql_real_escape_string in my localhost and I output the result of html in a table I have no problem. But when I use it on my server it outputs even the \ This is how I use it: $_GETVARS['txtEmpNum'] = mysql_real_escape_string($_GETVARS['txtEmpNum']); $_GETVARS['txtLName'] = mysql_real_escape_string($_GETVARS['txtLName']); $_GETVARS['txtFName'] = mysql_real_escape_string($_GETVARS['txtFName']); $varSQL...

Using Sum in If in Mysql

mysql,sql,select,sum
I want to select the sum of values of my_field. The unit of my_field is in seconds. I don't want to exceed 24 hours, so when it exceeds 24 hours, I want it to select 24*60*60 = 86400. SELECT IF(SUM(my_field) > 86400, 86400, SUM(my_field)) ... This solution doesn't seem to...

Error 1136: Column dosent match value count at row 1

java,mysql,sql
I've tried to add this row at the DB, but i've the problem: Error 1136: Column dosent match value count at row 1 the table is : Table: cliente Columns: CFCL varchar(16) PK CognomeCL varchar(30) NomeCL varchar(30) SessoCL varchar(1) ComuneNascitaCL varchar(20) DataNCL date IndirizzoCL varchar(30) TelefonoCL varchar(20) CittadinanzaCL varchar(30) PatenteCL...

How to select next row after select in SQL Server?

sql,sql-server
I have this issue, I need your help: EmpNo Shift_Date Shift1 shift2 stamp_Date stamp_Time stamp_Type 426 2015-04-12 A 2015-04-12 10:09:00.000 I 426 2015-04-15 B C 2015-04-15 23:46:00.000 I 426 2015-04-15 B C 2015-04-15 23:45:00.000 O 426 2015-04-16 OF 2015-04-16 07:02:00.000 O 426 2015-04-17 A 2015-04-17 07:34:00.000 I 426 2015-04-18 A...

SQL result table, match in second table SET type

mysql,sql
The following two tables are not liked by any type of constraint. First i have a table called subscription_plans that looks like this: name | price | ID ------------------- plan_A | 9.99 | 1 Plan_B | 19.99 | 2 plan_C | 29.99 | 3 I have a second table called...

Updating entity framework model using Linq

c#,sql,linq,entity-framework
I have a problem with updating data in an entity framework model. I have a few tables in the model (Users, UserActivity and Orders). I have added associations between the tables, one between Users and UserActivity and one between Users and Orders. The associations are through the column UserID. These...

How to use subquery result as the column name of another query

sql,oracle,plsql
I want to use the result from subquery as the column name of another query since the data changes column all the time and the subquery will decide which column the current forcast data stored. My example: select item, item_type ... forcast_0 * 0.9 as finalforcast forcast_0 * 0.8 as...

C# No value given for one or more required parameters

c#,sql,syntax,ms-access-2007,helper
Is there anything Wrong in SQL Syntax ? Sql syntax : UPDATE Table1 SET Username='Diana' , [email protected]' , FirstName='' , LastName='' , CrediCardNum='' Where Username='Diana' it Keeps Giving me the Error: No value given for one or more required parameters. I tried restarting visual studio but keeps giving the same...

Matplotlib: Plot the result of an SQL query

python,sql,matplotlib,plot
from sqlalchemy import create_engine import _mssql from matplotlib import pyplot as plt engine = create_engine('mssql+pymssql://**:[email protected]:1433/AffectV_Test') connection = engine.connect() result = connection.execute('SELECT Campaign_id, SUM(Count) AS Total_Count FROM Impressions GROUP BY Campaign_id') for row in result: print row connection.close() The above code generates an array: (54ca686d0189607081dbda85', 4174469) (551c21150189601fb08b6b64', 182) (552391ee0189601fb08b6b73', 237304) (5469f3ec0189606b1b25bcc0',...

T-SQL Ordering a Recursive Query - Parent/Child Structure

sql,tsql,recursion,order,hierarchy
I am trying (and failing) to correctly order my recursive CTE. My table consists of a parent-child structure where one task can relate to another on a variety of different levels. For example I could create a task (this is the parent), then create a sub-task from this and then...

Take thousand value in SQL

sql,sql-server
I have values like below I need to take only the thousand value in sql. 38,635.123 90,232.89 123,456.47888 I need to take result as below. 635 232 456...

Cant delete in database because of constraints

c#,sql,asp.net,oracle
I am making an asp.net project and I am trying to delete a row in my database but that table got constraints. I am using this method to delete it.How can I rewrite it so that all constraints are disabled or something in order that i can properly delete a...

Can someone explain to me how this statement is an exclude?

sql,sql-server,tsql
Can someone tell me how this is an exclude? Assuming that tableID is auto generated and in columnY there can be value of 0 or 1. This statement should exclude everything were columnY has a value of 1. SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON...

Improving work with SQL DataTime

sql,sql-server,database,tsql
I have a business in my Date. I need change date below for each date before 25 of each month I want get 25 of month before and for each date after 25 of each month I want get 25 of the same month. Example : for 2015/06/24 (or 2015/06/06)...

Database object with different data

sql,asp.net,asp.net-mvc,database,entity-framework-6
I'm making a web page in ASP.NET MVC which compares prices from different shops. I have a one-to-many with products and the shops, where the SHOP has one PRODUCT and a PRODUCT has many SHOPs, the problem is that the product is the same but the price is different. Example:...

Can't find FULLTEXT index while using IN BOOLEAN MODE

mysql,sql,full-text-search,mariadb,match-against
I'm trying to filter some data with an MATCH ... AGAINST WHERE clause. But I get the following error: Can't find FULLTEXT index matching the column list This is an query I execute: SELECT PAR.idPartij, PAR.Partijnaam, PRO.Productnaam, PER.Perceel, R.Ras, PAR.TagNR FROM Partij AS PAR LEFT OUTER JOIN Product AS PRO...

Title search in SQL With replacement of noice words [on hold]

sql,sql-server,sql-server-2008
I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...

insert and update sql statement in single sql statement

c#,sql
I want to create insert and update sql statements in single statement. But insert statement is for one table and update statement for another table... this is Appliance_Location table CREATE TABLE [dbo].[Appliance_Location] ( [Appliance_Id] NCHAR (10) NOT NULL, [RoomId] NVARCHAR (20) NOT NULL, [ApplianceName] NCHAR (10) NOT NULL, [AddDate] DATE...

How to incorporate wildcards with place holder variables in Java using SQL

java,sql,sql-server
Hello the following block of code represents one of the options a user can put in to perform a query search. As is, it successfully returns when the formation name is completely spelled out. However, I am trying to make it work for partial entries as well. if (!Strings.isNullOrEmpty(searchParameters.get("formation"))) {...

Extracting XML data from CLOB

sql,xml,oracle
How can I extract Food ItemID and Food Item Name and Quantity from the data as mentioned below. This is in clob column in plsql. <ServiceDetails> <FoodItemDetails> <FoodItem FoodItemID="6486" FoodItemName="CARROT" Quantity="2" Comments="" ServingQuantityID="142" ServingQuantityName="SMALL GLASS" FoodItemPrice="50" ItemDishPriceID="5336" CurrencyName="INR" Currency Id="43"/> </FoodItemDetails> <BillOption> <Bill Details Total Price="22222" BillOption="cash"/> </BillOption> <Authoritativeness/>...

like and regexp_like

sql,regex,oracle,oracle11g,regexp-like
For like we have %. for ex: if we give ad% it ll fetch all the records which starts with ad but i should use regexp_like. for regexp_like what can be used so that it acts as % for like. i cant use ^ad because from UI we ll give...

Is there a better way to write this query involving a self select?

sql,postgresql,join,aggregate-functions
I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...

oracle sql error Case When Then Else

sql,oracle,oracle11g
SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND ( CASE WHEN EXISTS(SELECT top 1 FROM SecondTable) THEN 1 ELSE EXISTS( SELECT SecondTable.RowProcessed FROM SecondTable WHERE FirstTable.Key = SecondTable.Key AND SecondTable.RowProcessed = 'Y' ) END ) AND OtherConditions Case When then else in where clause. Not sure about the syntax....

Looping distinct values from one table through another without a join

sql,sql-server,tsql,while-loop
I know looping is not ideal in SQL, but I couldn't think of another way of doing this. I want each distinct row from this Table 1 to have each distinct date and hour produced on Table 2. In other words, Table 2 has the dates between 05/01/2014 through 04/30/2015,...

Return 0 in sum when no values to sum - sql server

sql,sql-server,sql-server-2008-r2,sum
Trying to return 0 if any of these columns have no values returned, in my particular case 'Past Due' has no values to total, so it is returned, so i get the return in the second snippet here. How can I return something if these are no values to count?...

How to order SQL query result on condition?

sql,postgresql,order,condition
i have the this SQL query: SELECT DISTINCT category FROM merchant ORDER BY category ASC that gives this output: accommodation education food general health money shopping sport transport How to put the row that contains "general" at the start (or the end) of the result?...

select: result based on occurrence of explicit value

mysql,sql
Given is following mysql table: CREATE TABLE fonts (`id` int, `fontName` varchar(22), `price` int,`reducedPrice` int,`weight` int) ; INSERT INTO fonts (`id`, `fontName`, `price`,`reducedprice`,`weight`) VALUES (1, 'regular', 50,30,1), (2, 'regular-italic', 50,20,1), (3, 'medium', 60,30,2), (4, 'medium-italic', 50,30,2), (5, 'bold', 50,30,3), (6, 'bold-italic', 50,30,3), (7, 'bold-condensed', 50,30,3), (8, 'super', 50,30,4) ; As...

Update where column has highest value

sql,sql-server
I'm trying to write a simple "undo"-system for a table with the following structure: id element position start_date end_date 1 1 23 01/01/2015 05/01/2015 2 2 36 01/01/2015 NULL 3 1 17 05/01/2015 NULL So, when a new action is added for an existing element, it adds a new row...

Fastest way to find different rows in two mysql tables

php,mysql,sql,mysqli
I have two almost identical tables. Second one is a "slave" of first one. First table has autoincrement int ID column and second also has ID2 which is indexed unique int, but not autoincremented. ID2 is an analog of ID. I need to find fastest way to detect new rows...

Latest two datetime in SQL Server

sql,sql-server,datetime
I have following table CREATE TABLE [dbo].[events] ( [event_id] [nvarchar](50) NULL, [event_timestamp] [datetime] NULL, [reading] [int] NULL ) ON [PRIMARY] with the following data and resutls Table: events event_id event_timestamp Reading --------------------------------- 1200 14-Jun-15 1500 1200 13-Jun-15 1430.7 1200 12-Jun-15 1400.9 1200 11-Jun-15 1333.2 1200 10-Jun-15 1233.7 1330 14-Jun-15 1490...

How to get entries from SQL that only appear in certain value?

mysql,sql
I need to find entries in my SQL table that always and only appear in a certain value. For example: DeviceID Transmission -------- ------------ 000329 Inventory 000980 Inventory 004406 Starting 000980 Stopping 000329 Inventory 004406 Inventory Now I need to find all DeviceIDs that only have Inventory Transmissions and never...

Merging two tables into new table by ID and date

sql,sql-server,phpmyadmin
Let' say I have two tables: table 1: col a | col b | col c 1 | 62215 | 21 1 | 62015 | 22 2 | 62215 | 23 2 | 51315 | 24 and table 2: col a | col b| col f 1 | 62015| z...

postgresql complex group by in query

sql,postgresql
I have a query that gives: itemid deadlineneeded delievrydate quantity 200 15/07/15 14/07/15 5 200 15/07/15 14/07/15 10 200 15/07/15 13/07/15 25 201 15/07/15 14/07/15 30 200 14/07/15 10/07/15 3 201 15/07/15 15/07/15 100 It gives the information from multiple tables. Basically it means When items arrive to warehouse (delievrydate)...

SQL Repeated Condition in Two Tables

sql,variables,coding-style,condition
I am a beginner to SQL, and I am having trouble getting rid of repeated code. I have a condition "AccountId=20" that I use twice in a SQL query. The SQL looks something like: DELETE FROM Table_A WHERE FunctionId IN (Select FunctionId FROM Table_B WHERE AccountId=20); DELETE FROM Table_B WHERE...

Problems With FOR XML AUTO

sql,asp.net,sql-server,subquery,sqlxml
i have the following Query: SELECT QuestionID_PK , QuestionTitle , ( SELECT dbo.Tags.TagID_PK , dbo.Tags.TagTitle , dbo.Tags.TagTitle_EN FROM dbo.Question_Tag_Rel INNER JOIN dbo.Questions ON dbo.Question_Tag_Rel.QuestionID_FK = dbo.Questions.QuestionID_PK INNER JOIN dbo.Tags ON dbo.Question_Tag_Rel.TagID_FK = dbo.Tags.TagID_PK AND dbo.Questions.QuestionID_PK = '2116' FOR XML PATH('') , TYPE , ELEMENTS ) AS Tags FROM Questions WHERE...

SQL only one cell from column can have X value

mysql,sql
Is there a way to ensure, when you design a table - SQL, MySQL -, that only one cell from column X is true, the rest being automatically false? When you change the value of a different cell to true, all other cells should automatically become false. A radio button...

Multiple column values into a single column as comma separated value

sql,sql-server
I have a table CommentsTable with columns like, CommentA, CommentB, CommentC, CommentD, CommentE. All comments columns are VARCHAR (200), by default all columns are NULL also. The data looks like: CommentId CommentA CommentB CommentC CommentD CommentE --------------------------------------------------------------------- 12345 NULL C 001 C 002 NULL C 003 45678 C 005 NULL...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

sql,sql-server,sql-server-2008
I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...

copy table and drop it

sql,sql-server,sql-server-2008,tsql,stored-procedures
I know we can simply create a new table and copy the old table by doing select * into tbl2 from tbl1 i would like to check if table tbl2 exists, if it does then copy all the rows, if it doesn't then create a new one without having to...

Group and order by a column but donot include that column in results

mysql,sql
I've been trying to figure out how I can modify this query so that the result set does not include the numHits. I want the same results in the same order, just not have the numHits included. SELECT `newel_inventoryKeywordIdDictionaryId`.`inventoryId` ,COUNT(`newel_inventoryKeywordIdDictionaryId`.`inventoryId`) as numHits FROM `newel_inventoryKeywordIdDictionaryId` , `newel_inventoryDictionary` WHERE `newel_inventoryKeywordIdDictionaryId`.`dicId` = `newel_inventoryDictionary`.`dicId`...

Add 1 to datediff result if time pass 14:30 or 2:30 PM

sql,ms-access,ms-access-2007
Here is my SQL query which returns number of days by subtracting current date from specified date and returns exact as i need, but in addition i want to add 1 to result if current time passes 14:30 or 2:30. My query SELECT reservations.customerid, DateDiff("d",reservations.checkin_date,Now()) AS Due_nights FROM reservations Am...

Scrambling(randomize) row id's in mysql table

php,mysql,sql
I have table images with ID's starting from 1 to 5000. On the page they are showed straight from 1 to 5000. What I wonder is it possible to scramble the records inside database table not to query them by RAND() on the page because they will be random on...

echo both users

php,mysql,sql,database,loops
The code at the bottom of this post currently echoes: Name: Spongebob Squarepants Description: I live in a pineapple under the sea. Role: editor But there are two users in "wp_usermeta". It's only echoing one. The result needs to look like this: Name: wp_dev_05 Description: My name is Chris Topher!...

How to join 2 tables with select and count in single query

sql,oracle,left-join
I need to join 2 tables (Person and PersonLine). The result should contain id and name column from Person table and count of personlineid column from PersonLine Table for each id. But sql query returns count of all personlineid. Can anyone help to form the sql. Person: ID NAME AGE...

In PostgreSQL is it possible to join between table and function?

sql,postgresql
I have a table named Cars and a function named PartsPerCar there is no direct link between carid and partid. the link is though 3 tables in the middle. I do have a function called PartsPerCar which gets carid (it goves via the tables in the middle) and give back...

left join table, find both null and match value

sql,sql-server,join
I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...

MySQL: Select several rows based on several keys on a given column

mysql,sql,database
I have this mysql table structure: ------------------------------------ | item_id | meta_key | meta_value | ------------------------------------ 159 category Bungalow 159 location Lagos 159 price 45000 160 category Bungalow 160 location Abuja 160 price 53500 ... 350 category Bungalow 350 location Lagos 350 price 32000 What I'd like to do is select...

Query how often an event occurred at a given time

mysql,sql
[Aim] We would like to find out how often an event "A" ocurred before time "X". More concretely, given the dataset below we want to find out the count of the prior purchases. [Context] DMBS: MySQL 5.6 We have following dataset: user | date 1 | 2015-06-01 17:00:00 2 |...

Select all none null values from all columns in a table

mysql,sql
I am having an issue going through column by column and getting a count of all the non-null values in each column. I think issue is how I am getting the column names. In the last select statement where I am trying to select all of the values in each...

How can I create missing date records for each employee based off a limited calendar?

mysql,sql,tsql
I have two tables, one an abbreviated calendar: create table dbo.calendar ( [date] datetime ) insert into dbo.calendar values ('20150101 00:00:00 AM') -- 1/1/15 insert into dbo.calendar values ('20150102 00:00:00 AM') -- 1/2/15 insert into dbo.calendar values ('20150103 00:00:00 AM') -- 1/3/15 insert into dbo.calendar values ('20150104 00:00:00 AM') --...

Get unique row by single column where duplicates exist

sql,sql-server
I have the following DB table called messages with columns: thread_id, sender_id, receiver_id, date Example: +-----------+------------+-------------+-------------+ | thread_id | sender_id | receiver_id | date | +----------------------+-----------+-----------------+ | 1 | 1 | 2 | 11/06/2015 | | 1 | 2 | 1 | 14/06/2015 | | 1 | 1 | 2...

Is it possible to index views in Apache Solr

sql,view,solr
Let me first give you an example. I have two tables -table1 and table2. table1 has a field id_table2, which is a foreign key and references one of the fields in table2. So, when I want to scan table1, I make a query like: SELECT t1.attr_1_, t1.attr_2_, t2.attr_3_ FROM table1...

SQL Customized search with special characters

sql,sql-server,sql-server-2008
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...

Foreign key in C#

c#,sql,sql-server,database
I have two tables, A and B, in a dataset in SQL Server; I have created a connection to the dataset in a c# project in visual studio. How can I create a foreign key ( A is the parent) between my two tables ? I want to create the...

Entity Framework code-first: querying a view with no primary key

sql,oracle,entity-framework,view,ef-code-first
Our customer has given the access to views in which there is no primary key is defined. I know Entity Framework needs a primary key for table to identify. But for views not having primary key is it still possible to query. I try to find but always Entity Framework...

SQL: overcoming no ORDER BY in nested query

sql,sqlite
I have a table t as follows: CREATE TABLE t( id INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT, a TEXT, b TEXT ); Sample Data 1|2015-06-15|a1 15|b1 15 2|2015-06-15|a2 15|b2 15 3|2015-06-16|a1 16|b1 16 4|2015-06-16|a2 16|b2 16 5|2015-06-17|a1 17|b1 17 6|2015-06-17|a2 17|b2 17 I want to select all values of a...

SQL Multiple LIKE Statements

sql,sql-server,tsql,variables,like
I'm currently working on a report that shows me all post codes covered by our sales team. Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this. SELECT...

How to join result of a Group by Query to another table

sql,tsql
I have the following Group By SQL: SELECT s.Login_Name, COUNT(s.s1CIDNumber) FROM [dbSuppHousing].[dbo].[tblSurvey] s group by s.Login_Name I want to know how I can join this result set to another table(tblUsers) to add user_id to the result set (tblSurvey and tblUsers have 1-1 relationship on Login_Name) I tried the following: Select...

update a table from another table using oracle db

sql,oracle
I have to update one table from another one: I can do the update with the MySQL sgbd: update product pr , provider p set pr.provider_name = p.name where p.provider_id = pr.provider_id ; but when I try to do it with oracle : I tried this query for oracle UPDATE...

Select Statement on Two different views

sql
I couldn't find the answer to my specific question anywhere. Is it possible to select from two different views? For example my code looks something like this right. select view1.col1, view1.col2, view1.col3 from dbo.view1 inner join ~~~~~ inner join ~~~~~ but I want to include a column from a different...

Default the year based on month value

sql,sql-server
I have a query to display the year value from a table and default the current year. select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder from [TTS].[dbo].[Class] where year(startdate) <> year(getdate()) union all select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder order by sOrder asc,...

Pull information from SQL database and getting login errors

php,sql,database
I am creating a very small, simple CRM for a company, they require the function to be able to view the last 25 orders via the dashboard. The orders are added via a Order-add form within the CRM. When adding the following code to the CRM I get an error:...

Recursive Lag Column Calculation in SQL

sql,sql-server,recursion
I am trying to write a procedure that inserts calculated table data into another table. The problem I have is that I need each row's calculated column to be influenced by the result of the previous row's calculated column. I tried to lag the calculation itself but this does not...

how can i use parameters to avoid sql attacks

sql,vb.net
I have a project without any parameters used in SQL queries. Is there any solution so that i don't have to change the function and validate parameters from the Query string itself? Query = "select * from tbl_Users where userName='"& textbox1.text &"' and password='"& textbox2.text &"' " ds = obj.ExecuteQueryReturnDS(Query)...

How to Retrieve value from a UDT

sql,sql-server
Goal: I want to reach and display the value of @data inside if [wwtest]. Problem: The UDT is from the database [test] and I retrieve an error message "The type name 'test.dbo.tvf_id' contains more than the maximum number of prefixes. The maximum is 1.". I don't know how to retrieve...

Retrieve data from one table and insert into another table

sql,asp.net,sql-server
I am trying to retrieve data from one table and then insert it into another table. This is a a sample of the first table in which there is the following data. tb1 is the table which consists of data. The two columns Manager and TeamLeader basically means for example...

Sql inner join with three tables

sql,sql-server-2008
I have this problem, I am a newbie in SQL. I have 3 tables: TBL_PRODUCT: prod_id, prod_type, prod_code, prod_desc TBL_DOCUMENT: doc_id, doc_name, doc_expiry, doc_term, enable_amt TBL_DOCUMENT_GROUP: grp_id, prod_type, doc_type, doc_id For every document, there are corresponding products, one to many. What I can't figure out is how can I display...

SQL average age comparison function returns null

mysql,sql,function,datetime
So, I'm working in MySQL, writing a function that averages the ages of women and men from a table and compares them, and returns which is greater. Dates are in the format of YYYY-MM-DD, and I'm using DATEDIFF(). The function appears to work correctly, but when I run the script,...

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=

sql
I need to know where is the problem with below stored procedure..it will return error of "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression" CREATE PROCEDURE [dbo].[SP_APP_FP_Complete_Details] @FinishProductId...

SQL varchar variable inserts question mark

sql,sql-server-2012
I've a database value that when inserted into a SQL variable, shows with question mark at the end !! can't find a reason?! declare @A varchar(50) = 'R2300529‏' select @A Results: R2300529? any explanation? i'm using SQL server 2012....

for-loop add columns using SQL in MS Access

sql,ms-access,table,for-loop,iteration
I am trying to add n columns to a table, like in this example of code where n = 10: Sub toto() Dim db As Database, i As Integer Set db = CurrentDb() For i = 1 To i = 10 db.Execute " ALTER TABLE time_series " _ & "ADD...

Groovy: run SQL SELECT LIKE from file with params

sql,select,groovy
The groovy code is import groovy.sql.* dbUrl = 'jdbc:sqlserver://server' dbUser = 'username' dbPassword = 'password' dbDriver = 'com.microsoft.sqlserver.jdbc.SQLServerDriver' sql = Sql.newInstance(dbUrl, dbUser, dbPassword, dbDriver) def sqlfromfile = new SqlFromFile() sqlfromfile.sql_filename='select_query.sql' sqlfromfile.read() try{ def result = sql.rows(sqlfromfile.result,[id:'01']) println result }catch(e){ println e } class SqlFromFile { def sql_filename def read(){ result=...

How to SUM two first character in MySQL?

mysql,sql
Is it possible to SUM only first two characters using SUBSTR and GROUP BY in MySQL? Such as : SELECT SUM(Substr(period, 1,2) FROM table GROUP BY period I have tried these SQL. But didn't effect because of using GROUP BY. The result of these query is sum all of the...

Fastest way to add a grouping column which divides the result per 4 rows

sql,sql-server,tsql,sql-server-2012
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...

How to design a history for n:m relations

sql,plsql,many-to-many
Problem: I have a n:m relation between a table A and another table B and it is required to have the complete history of this relation, in other words I must be able to reproduce the status of any point the past. Ideas: My first attempt is to dissect to...

Convert AWK command to sqlite query

sql,awk,sqlite3
I have to parse very big (abt 40Gb) text files(logs) very often. Usually AWK/grep is enough for my needs, but logs are growing and now I'm curious whether sqlite3 will allow me to do the same things more efficiently. I chosen sqlite for my tests as it installed out-of-the-box (SQLite...

SQL Group By multiple categories

php,mysql,sql,mysqli
This is the query I'm using to count how many events are booked for each category of bookings. SELECT Categories.name, count(case when locations.name ='loc 1' then 1 end) as Location1, count(case when locations.name ='loc 2' then 1 end) as Location2, count(case when locations.name ='loc 3' then 1 end) as Location3,...