Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

0
votes
1answer
44 views

Cannot attach My Database without log file

I deleted my whole database by mistake and all my accounting data is gone. I recovered the .mdf file via FileScavenger. Now when want to attach it without .ldf file it gives this error: Could not ...
1
vote
1answer
17 views

Replacing double digit numbers

I have column with numeric values that I need to replace into text values. I used the sql statement: REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(...
3
votes
2answers
226 views

Difference between “<> ANY(…)” and “NOT = ANY(…)”?

Could you please explain the difference between these two operators? sID != smth and not sID = smth. At a first glance they seem perfectly equal. But they give different results.
2
votes
2answers
71 views

Fastest query for selecting arrays that contain duplicates

I'm using Postgres 9.5 and I have a column, named phones, of type text[]. I need to find all rows where this column contains duplicates. I've found this extremely useful set of functions https://...
0
votes
1answer
31 views

Cumulative Subtraction from external input

I have an external value 20. I need 1 to be subtracted from 20 (=> 19) and then 2 to be subtracted from 19 (=> 17) and so on. Data: Serial No | Value1 | External value(20) A | 1 | ...
0
votes
1answer
36 views

MyBB forum MySql Internal Error :1366

I'm using MyBB forum on my Ubuntu 16.04 LTS server, using LAMP, mySQL Improved to be specific. When I try to install a plugin (by putting it in the mybb plugin folder) and hit install plugin in my ...
0
votes
1answer
38 views

Query must be a SELECT

I try to do a select union with order by but it seem like order by can not use union. I receive following message Query must be a SELECT This is my code (SELECT `tabsales_action`....
0
votes
1answer
20 views

Create a view showing total minutes per customer per month and change from previous month

I have an existing database (SQL Server 2012, on premise installation) and need to add a column to a view which gets displayed via a Crystal Report. The issue I am having is with restricting the ...
1
vote
0answers
38 views

Under which conditions would selecting a small column off a primary key be slow?

Usually querying the table is in the millisecond range at most. But sometimes it takes 30 seconds and then times out. What could cause SELECT smallColumn FROM SomeTable WHERE pkID=@pkID to be slow? ...
0
votes
0answers
37 views

Shrink .ldf file issue

These are the timing of some jobs on SQL-Server: rebuild indexing at 10.30 am (all timing in IST) reorg indexing at 11 am full backup at 12.30 pm transaction backup every 2 hours truncate log ...
1
vote
2answers
47 views

How to find out if rows are refenced from other tables?

I have a table named contacts (primary key id) which is referenced by a number of other tables via foreign keys. When a list of contacts is displayed in the application, I get their primary keys in an ...
1
vote
1answer
12 views

Selecting entries with the same count as in another table

I'm using a SELECT statement to select multiple IDs and than use GROUP BY ID HAVING COUNT(*) = someNumber to only have the IDs with a certain count. What I however actually want to do is to select ...
1
vote
0answers
35 views

UPDATE based on the occurence of the same string within two other columns

I am currently using Microsoft Access 2007. I wonder if there is a way to find whether two specific columns in one table contain the same (undetermined) string. The table may look as follows: ...
0
votes
1answer
16 views

Query to map a particular database to a syslogin

I need to map a database to a syslogin. Following is the query I have used to create login and make it a db_owner but after the execution, I can see a tick as db_owner for that database in ...
0
votes
1answer
39 views

store procedure variable storage takes long time to execute

i have a store procedure This procedure take a endless time for execution BEGIN set @my_user_id=null; select user_id into @my_user_id from office_user where username like my_username ...
0
votes
2answers
22 views

Read CSV and perform SQL in SQL Server Management Studio

Is it possible in SQL Server Management Studio to read a CSV and perform an SQL update per line? Example data.csv "column1","column2,"column3" "column1","column2,"column3" "column1","column2,"...
0
votes
1answer
22 views

Merge sub-queries to a single query

I am trying to merge the nested MYSQL queries into one First of all I fire this query select names , city , category from names_data group by names , city ,category ; this produces a result of ~...
1
vote
1answer
24 views

Get relation data on comma separated values

I am an average in database operation but I never deal with a database having values in comma separated manner. As I want the result from both table. I have one table "Form table" +------+----------...
0
votes
0answers
33 views

How to visualize Redshift EXPLAIN query plan?

I've tried many different tools such as pgAdmin, DataGrip, http://explain.depesz.com, etc. None of them, however, seem to support Redshift's query plan with what I've tried. For simple queries, the ...
0
votes
2answers
32 views

Display list of SSRS reports in a dropdown list, and allow users to select which report to display

Our company has about 10 different SSRS reports. We would like to allow users to navigate from one report to another simply by selecting the report from a dropdown list. Is this possible?
0
votes
1answer
79 views

Is it beneficial to include ORDER BY fields in the list of SELECT fields? [closed]

I'm just wondering when you do a query and you use ORDER BY should the fields you use in the ORDER BY clause always be included in the SELECT list even if you don't need them? Is this beneficial in ...
1
vote
2answers
51 views

Full Join Query

I am doing a full join query from two tables. SELECT i.idinasistencia, i.fecha, i.valor, i.justificado, h.id, p.nombres, p.apellido_paterno, p.apellido_materno, p.rut_persona FROM historialalumno ...
3
votes
1answer
60 views

How to LEFT JOIN a date table onto each customer (GROUP BY) in a customer purchase table? Can a JOIN be done for dates after initial purchase?

I am stuck on implementing a left join on dates for each individual customer. I have a table which stores customer purchases: SELECT purchase_date, customer FROM purchases I would like to create a ...
0
votes
1answer
33 views

SQL Server IOPS

I'm trying to evaluate the IOPS of TPCH queries on SQL Server. Specifically, for TPCH query 13, using "set statistics io on" and "set statistics time on", I got: Table 'orders'. Scan count 9, logical ...
0
votes
0answers
20 views

Basic Inventory/Stock Schema

Previously, I thought I needed an "attributes" table based on their category/subcategory (Medicines have "Dosage" but Soap doesn't right?) ... but while trying an example I made up in my head, ...
0
votes
3answers
32 views

How do I REPLACE values in column after querying

I have a column with numeric values I am querying SELECT column FROM table WHERE column='1' OR column='2' OR column='3' OR column='4' THEN REPLACE (column, "1", "ABC"); REPLACE (column, "2", "...
3
votes
1answer
77 views

Optimise a LATERAL JOIN query on a big table

I'm using Postgres 9.5. I have a table that records page hits from several web sites. This table contains about 32 million rows spanning from Jan 1, 2016 to June 30, 2016. CREATE TABLE event_pg ( ...
1
vote
1answer
65 views

SQL Server 2014 Triggers and where are they created

I am on SQL Server 2014 Standard edition. I am developing a Supply Chain DW and I am currently in the process of building a Landing schema which will be used to Land data coming out of the source ...
0
votes
1answer
48 views

Group COUNT by month less than variable

In SQL Server 2008 R2, I have the following two tables, Employee and EmployeeStatus: CREATE TABLE [dbo].[Employee] ( [employeeID] [int] IDENTITY(9500,1) NOT NULL, [firstName] [nvarchar](50) ...
-2
votes
2answers
65 views

Query taking very long time in 12C environment, but the same query is taking just a few seconds in 10G production environment

We are migrating a database from 10G to 12C and currently we are testing in a dummy 12C production environment. Particularly 3 SQL queries are taking almost 50 min. time to complete and show the ...
1
vote
1answer
26 views

Efficient way on updating multiple tags or categories

What's the efficient or best way on updating a multiple tags or categories? Currently I have posts and posts_meta tables. I have this structure on posts_meta table: +----+---------+----------+------...
0
votes
0answers
18 views

Write sql query to store alarm details for next 1 week with given user id [migrated]

I am making a scheduler where currently I am saving details for reminder set by client with specific user id. Now I want to add a button of Repeat weekly where the alarm will be repeated for next 7 ...
0
votes
1answer
29 views

When defining a foreign key in this Oracle example, what is the significance of the CONSTRAINT keyword?

In the code below, The first column I create for the table courses is a column called department_code. I then define fk_courses_department_code and specify that department_code in this table will be a ...
0
votes
1answer
42 views

Database design help. many-to-many relationship

I have four columns. All columns are varchar fields. Three of them are independent and distinct fields having many to many relation between them and the fourth one is dependent on the combination of ...
1
vote
0answers
24 views

Database design - many-to-many relationship between three columns [duplicate]

I'm noob in database. So don't mind if its a silly question.This is used in my application and is the first one. I need a database design for it. value 4 is dependent on the other value 1,2&3. ...
0
votes
0answers
37 views

Oracle rows to columns

I have the following question: I have the following resultset for my query: SELECT ID, quantity, date FROM table: id quantity date 32758669 255150 17.08.2016 32758669 271350 24.08....
0
votes
1answer
75 views

CTE Query copy value if exists into an existing table [closed]

I got the following query: WITH table_data AS ( SELECT UNNEST(array['Small Bus', 'Window', 'Test 1']) AS description, UNNEST(array['1dfsdf4', 'QSF-312', 'EXD-TUH']) ...
1
vote
1answer
45 views

How can I count some rows before inserting?

Here is my table structure: // QandA +----+----------------------------------------+------+---------+-----------+------------+ | Id | body | type | related | ...
0
votes
0answers
38 views

how to get the data from table

Before this query is run an activity name (e.g. ‘Art’) should be assigned to a variable. The query should then output a list of the children registered for the specified activity. The output should be ...
0
votes
1answer
50 views

how to judge sql performance through these two explain?

If I give you 2 explain results from MySQL,how do you know which query is better? the first sql explain *************************** 1. row *************************** id: 1 select_type: ...
2
votes
1answer
112 views

Very slow merge replication synchronization

I had an unforeseen issue with a sub going down (generic error, was probably a connection issue) and now that I've reconnected the sub it is insanely slow re-syncing. It seems to download about 1000 ...
1
vote
2answers
39 views

Why is table opened every time a query is executed against it?

I execute this SQL command twice and profile select * from sbtest.sbtest2 limit 100; The first time profile result is mysql> show profile for query 1; +----------------------+----------+ | ...
1
vote
1answer
97 views

SQL Query error driving me crazy! [closed]

I'm wondering if anyone can find the mistake in my SQL-Query which I can't seem to find.. Been starring at the same lines of code for like 2 hours and it's driving me nuts! So, here's the query... (...
0
votes
0answers
25 views

Create custom JSON response from PostgreSQL table

Consider the following table: CREATE TABLE IF NOT EXISTS trips ( trip_id SERIAL PRIMARY KEY, start_location TEXT REFERENCES locations(label), end_location TEXT REFERENCES locations(label), ...
0
votes
0answers
32 views

OraOLEDB.Oracle not showing up in Providers in Linked Server

Im trying to link the Oracle database to our SQL Server database. However OraOLEDB.Oracle is not in the Providers list on the Linked Servers. I have installed Oracle Database Client (12.1.0.2.0) for ...
0
votes
0answers
47 views

SQL 2016 Installation Error

I am trying to install sql 2016 standard on a windows server 2012 standard edition, and I am running into an odd error, hopefully someone can point me in the right direction. I am getting account ...
0
votes
1answer
42 views

What exactly happens during Database Mirroring? What about during a fail over?

So I am trying to configure a two server setup, both containing SQL with independent storage. We're using windows server 2012, and I have a question about mirroring. If the server's data bases are in ...
0
votes
2answers
21 views

Set Login User as database Onwer

What's the query to set a login user as db_owner during the creation of database. Following is the set of queries, which I'm using right now, Create database Test1; ALTER AUTHORIZATION ON DATABASE::...
1
vote
1answer
56 views

Update all tables containing column name

In SQL Server, I can find all tables containing a COLUMN_NAME: SELECT * FROM INFORMATION_SCHEMA.columns WHERE COLUMN_NAME = 'COLUMN_NAME' How can I update all of these columns like: UPDATE <...
-1
votes
1answer
28 views

SELECT rows according to another table

Table A (Id, Value), Table B(AId, Value). I need to select only rows from A, for which the pair (Id, Value) is not in table B. Solution with "JOIN" would probably better, because of performance.