DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

4
votes
1answer
60 views

Why do async_network_io wait types occur?

Last week something strange happened on our database. All of a sudden, the application blocked for our users who were not able to save new entities etc. After looking at the Activity Monitor of the ...
1
vote
0answers
13 views

MS Access: Subform only displays first entry. Continuous Form follow-up question

I am new to MS Access and Database creation in general. Currently I am working on a simple employee database that can view an employee's information and can show their current (and previous) projects ...
1
vote
1answer
190 views

Query multiple rooms with different conditionals (Adults, Children)

I have a simple query where I select available x Rooms with x Adults + x Children per hotel that matches a date range, but I'm having a hard time trying to figure out how to query a list of rooms per ...
5
votes
2answers
92 views

Will I need to create a pivot or can I alter this query

The following Query that I wrote is working to a point. The problem that I found is that in each pump, cable, etc can be listed in each of the part columns. Example: What I would like to get is ...
2
votes
3answers
15k views

Exporting table records to an INSERT script

I'm setting up a test environment for development and need to export some static data from tables in Production. Ideally the exported product would take the form of a script with all the required ...
1
vote
1answer
117 views

MYSQL testing string field contains another altered string field

I have this table : mysql> desc mytab; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
1
vote
1answer
205 views

Very slow update with subquery (different tables)

I run this query to update "domains" table based on "log" table: UPDATE domains SET domains.mx_collected = 0 WHERE domains.domain IN (SELECT log.mail as domain_name FROM log WHERE `time` >= ...
2
votes
2answers
64 views

MySQL: memory requirements for django app with 1 big table (460M rows)

I have created a django app that is running on virtual machine. All db tables are rather small (up to 1M rows) but recently I was asked to add a big table (460M rows). CREATE TABLE `genescorrelation` ...
5
votes
1answer
56 views

Joining in query decomposition

I have a question about query decomposition and localization, as in here: When joining 3 tables (here EMP, ASG and PROJ) is there any specifiy order, when to join which table? Why ASG and EMP are ...
6
votes
2answers
263 views

Trouble with a NOT IN query

SELECT DISTINCT d.customer_id, d.date_added FROM `order` d WHERE d.customer_id NOT IN ( SELECT DISTINCT i.customer_id FROM `order` i WHERE i.date_added > '2015-02-15 14:00:00' ) ORDER ...
3
votes
0answers
98 views

How to manage hundreds of SQL jobs? [closed]

I work in Microsoft SQL Server database and I have to monitor and manage hundreds of SQL Jobs to see which job will execute on what day and what time. I also have to troubleshoot the failure of the ...
3
votes
1answer
104 views

How to enforce a constraint over multiple tables?

I have the following five tables: PRODUCT FEATURE PROVIDER PROVIDER_PRODUCT PROVIDER_FEATURE The logic is as follows: A generic PRODUCT can have one or more FEATUREs. There are different ...
1
vote
1answer
37 views

PostgreSQL “size of temporary files”

I've imported data into a new database (about 600m rows of timestamp, integer, double). I then created some indexes and tried to alter some columns (got some out of space issues), the database is ...
1
vote
2answers
232 views

MYSQL joining 3 tables with left join

I have these tables: pupils: (id_pupil, name, surname, email, user, pass, level, class) incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester) ...
0
votes
1answer
118 views

import text file getting command is not allowed with this MySQL version

here is my table CREATE TABLE cities ( country CHAR(2), city_ascii VARCHAR(100), city VARCHAR(255), region CHAR(2), population INT UNSIGNED, ...
1
vote
1answer
145 views

Query does not use index (according to EXPLAIN SELECT)

Can anyone give me an indication, why this query does not use the Key on the table 'gtg'): ...
2
votes
1answer
46 views

How to insert values in junction table for many to many relationships (MySQL)?

I'm new to relational database design and I've hit a stumbling block. I have three tables... PROJECTS project_id, name, ... PROJECTS_UNDERGROUND projects_FK, underground_FK (I have both FKs setup ...
3
votes
0answers
100 views

Which is more efficient b+ tree or search (binary or linear)?

SELECT A FROM T WHERE M > 1000 AND M < 5000; I am unable to judge which of the below is best suited for the above query: Linear search Binary search on M B+-tree with index on M B+-tree with ...
3
votes
1answer
25 views

Inventory management in Rails

I'm creating a inventory management app in rails. I have an User Table and a Recipe table, there is also some tables with types of material (pens, sharpies, paper, and so on). Because there is a lot ...
0
votes
1answer
84 views

Find columns with different values compared with a reference row

My table, MARC, has more than 200 columns with WERKS (plant) as the key. Most of the entries in MARC have values that come from a reference plant. So, usually, all the 200 columns will contain the ...
2
votes
1answer
100 views

How to join all combinations from two tables with a common column

I have a list of suppliers who supply a list of countries. For example: Supplier A supplies to: USA France China and another list of suppliers with their suppliers' factories For example: ...
0
votes
1answer
21 views

Is there a query to see if all of my logins and password are moved during migration from SQL Server 2005 to 2008R2

I am trying to migrate my database from SQL Server 2005 to 2008 R2 test, I did follow back up and restore method to migrate the database, and I ran the QUERY provided by Microsoft to transfer logins ...
1
vote
1answer
19 views

mysql - select random rows with max 3 rows per parent id

I currently have a query that returns a maximum of 3 rows per parent_id. I want to enhance the query to return a maximum of 3 random rows per parent_id. This does not work for me at the moment as it ...
1
vote
0answers
15 views

MaxDB - Strange behaviour when selecting from a CLOB-Row

I am using "sqlcli" for Linux to write the output of the following query into a file: select clob_field from table (...) in SAPGui (original user input) the contents are displayed as: First ...
0
votes
0answers
35 views

how to select the date from the tables?

I have two tables: CSS533 CSSSERV I want to select SERV_ACCT_NUM and BID_TYPE_CODE from CSS533 and SC_START_DATE and SC_END_DATE from CSSSERV where SERV_ACCNT_NUM for both tables should be ...
4
votes
3answers
132 views

Select Column value based on two values in another column [closed]

I am using the below table: 1 0051ML66220600132482 06:00:00 06:00:00 1538 100 0 1 2 0051ML66220600132482 06:00:00 06:00:00 1540 200 0 0 3 ...
4
votes
2answers
46 views

Select only those records that have different/multiple values for a particular column

Below is an example of my membership table. There some records having multiple values in the email field. I only want to select those records that have multiple email values: Member table ID ...
2
votes
2answers
179 views

Group and order output into a single row

I want to display each person's contact info and their commissions in a single row as opposed to multiple rows. Commission Table ID COMMISSION DATE ...
0
votes
1answer
35 views

Why are some of my migrated tables showing “The given key was not present in the dictionary” in WebMatrix's interface?

I'm having an issue while using the SQL Server Migration tool to convert Access accdbs into SQL Server 2014 Express. I am converting the tables using SQL Server Migration Tool and I have a connection ...
3
votes
2answers
41 views

Combine the effects of separate delete and insert rows

How to I combine the two rows below: Date Id For_Id Type New_Value Old_Value ------------------------------------------------------------------ 2015-08-13 6 327 ...
2
votes
1answer
125 views

Order by values with most incidence

I have a table as this example: CREATE TABLE customerEvent( customerID int NOT NULL, eventID int NOT NULL ) customerID may appear in many records. I use the following query to identify ...
0
votes
1answer
111 views

Most used tables in MySQL database server

Can somebody please help me with a query that lists the most used tables in a MySQL database? Or how can i find out the most used tables in the database? Thanks
0
votes
1answer
31 views

Automate Backup of SQL Server Database Structure

I have a data warehouse which isn't huge only about 16Gb in a Microsoft SQL Server Standard Edition 2014 database. However, now that it forms part of our production system I don't like developing on ...
2
votes
2answers
253 views

Numeric vs Integer for a column - size and performance

I have an application which uses a PostgreSQL table. The table is very big (billions of rows) and has a column which is an integer. The integer can be up to 6 digits, i.e. 0-999,999 , no negatives. ...
1
vote
2answers
51 views

Use array expression from subquery in ANY() condition

I have a relatively complicated query with a subquery fetching an array like so: ... ARRAY(SELECT category_id FROM category_schedule_con con WHERE s.id = con.schedule_id ORDER BY category_id) ...
1
vote
2answers
28 views

Hows to make a DB2 table read-only?

We need to test how an application will fail when the DB2 database cannot be written to. What is the easiest way to make a DB2 table read-only? Is this considered locking? Thanks in advance, Bert
2
votes
2answers
28 views

Oracle Database 12c: TNS:permission Denied (CentOS)

(This post has a similar title to another post but the underlying cause and solution are totally different.) Problem: I can't login to sqlplus as sysdba but the listener seems to start ...
1
vote
1answer
27 views

Identify records with one identical field, but different timestamp

I am trying to process some data, stored in an Oracle DB, for a machine learning model I am working on. My current query uses this line in the SELECT statement to identify records that have a field ...
2
votes
2answers
32 views

Differentiate between a row modification and add?

Any recommendations into how one can figure out which rows were solely modified from an existing row (edit) and which one were solely inserted? I know minus can extract any rows that were modified ...
2
votes
1answer
30 views

How to fix DB2 Unsupported functionality: GENERATED BY DEFAULT

On IBM DB2 I try the following direct SQL: CREATE TABLE users ( user_id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1), user_name VARCHAR(16) NOT NULL, email_address ...
1
vote
0answers
28 views

Update row value with a sub-select join

I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table. The query looks like this ...
1
vote
2answers
37 views

Troubleshooting high disk read - buffer cache query

I have a table tbl_Table1 that is about 5 GB in size and has millions of rows. It has these columns: ID, ContractID, Person, Address1, Address2, and many more. One clustered index on the PK, ID. I ...
4
votes
2answers
98 views

Count rows with inner joined tables

I have 3 tables: Players: mysql> SELECT * FROM players; ...
2
votes
1answer
59 views

Select last row for a certain group

I have two tables: Table users columns | type | notes --------|--------|------- id | bigint | PK ... | ... | ... Table events columns | type | notes ...
0
votes
2answers
226 views

Dealing with data stored as arrays in a MySQL DB

So I know storing arrays in a DB field is wrong and would never do it myself, however a 3rd party plugin my company is using stores data in an array and I was wondering if you could help me try to ...
3
votes
1answer
38 views

Scripting Microsoft Data Warehouse Wizard

I am trying to automate the MDW setup process in my environment. Currently I have everything complete except for enabling the data collectors (usually uses a wizard as seen ). Has anyone seen or ...
2
votes
0answers
55 views

Finding the most recent event older than 60 days [closed]

I would like to filter rows in a table for the most recent event for particular individuals that did not occur within the last 60 days: Select pid, Max(date) as date FROM table WHERE date ...
1
vote
2answers
55 views

Retrieve additional columns in recursive CTE

This works as far as getting the amount of children a "thread" has but now I can't seem to get it to pull parent row columns. Parent rows have parent_id is null, trees can be any level deep. I manage ...
1
vote
0answers
82 views

SQL Server 2012 memory issue [closed]

I am having performance issues on a Windows 2012 Server running SQL 2012. When I run the DBCC MEMORYSTATUS command I receive the following. Process/System Counts Value Available Physical Memory ...
2
votes
0answers
50 views

How can I organize complex queries?

I am analysing a relatively large (25GB compressed MySQL dump) static data set. Some of the queries I'm writing are several tens of lines long, which makes them difficult to understand, modify, and ...