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)

3
votes
1answer
67 views

SQL Joins and Relationships

I am confused by joins. I want to know how to choose the join based on the relationship between tables, or do joins have no dependency on relationship? if relationship is "one-to-one" then "Inner ...
3
votes
1answer
64 views

4m Rows in MySQL, ~5 Minutes to Run Query

I have 2 tables, a users table and a log table which literally logs each time a user signs in. What I'm trying to do is grab a list of all users with a certain rank who have not logged in for over a ...
2
votes
1answer
60 views

MySQL - Ordering 'featured' items first

I need to have the results of a query show featured items first for a given date before listing the day's other items. You can ignore the categories that are being pulled from another table. ...
5
votes
2answers
65 views

Using Left or substring with wildcard in SQL

I am looking to retrieve information from a field, but am having troubles getting exactly what I want. The information I want has no set order and I am also looking for two different things. Here is ...
0
votes
1answer
53 views

SQL Server 2012 Exclude a value

I'm teaching myself SQL, and I ran into a scenario that I cannot seem to solve. Please any help. I have a table called tblRecord with a column called BuyerID, in this case there are two BuyerIDS, and ...
13
votes
9answers
742 views

Alternative to WHERE clause [closed]

Is there a way to only SELECT rows with certain data in a column, without using WHERE? e.g. If I had this: SELECT * FROM Users WHERE town = 'Townsville' is there a way to implement the WHERE ...
0
votes
1answer
22 views

Returning rows based on time of day respective to their timezone [closed]

I also posted this on Stack Overflow. I got no answers there so I'm hoping someone here might be able to help. I have a table in which every row represents a user, I am also storing the user's ...
-1
votes
1answer
60 views

How can I generate a script to choose only top 10 items from each table in SQL database?

I know how to generate scripts that would contain insert queries of all records in all tables. But how can I limit this to top 10 records only from each table?I can select "Data Only" when generating ...
1
vote
1answer
24 views

Create one row for every user in a Postgres table

I have a bunch of rows of users in a users table, for example: | id | name | |----|-------| | 1 | Chris | | 2 | Max | | 3 | Steve | For each one of these users, I'd like to create a row in ...
-2
votes
0answers
44 views

SQL Server 2014 - Query output

To be clear: I want to use the result of first query in the second query. Below are the two queries: Query 1 select files.originalfilename from files inner join digitalfiles on files.fileid ...
2
votes
3answers
69 views

Generate a list from columns that do not contain a zero for each row

I have a table TAB1 in an Oracle database: +----+----+----+----+----+----+ | ID | C1 | C2 | C3 | C4 | C5 | +----+----+----+----+----+----+ | 1 | 1 | 0 | 3 | 0 | 5 | | 2 | 11 | 22 | 0 | 44 | ...
1
vote
1answer
31 views

can i shrink log file in full recovery model

i have configured Always on Availability group where iam unable to shrink log file size through simple recovery model. can i shrink the log file size in full recovery model if yes or possible then is ...
1
vote
2answers
47 views

How to optimise MySQL query that uses dependent subquery

I have a web app that has a custom feed of images. The image queried are based on whether the users have previously seen the image and ordered by a Hacker News style score. There are only 1000 rows ...
5
votes
2answers
80 views

Combining data from multiple rows

I am relatively new when working with queries so let me apologize in advance. This is a MS 2012 Server that I will be working with. This data has been brought into a staging table through a nightly ...
0
votes
0answers
31 views

How to make a query from a database with schemas to a database without schemas, and vice versa, with DBLINK in postgres

I have a DB (DB1) in postgres 9.4 who use DBLINK to query another DB (DB2), and vice versa, using views and functions. But now i have to add 2 schemes to DB1 (CLIENT1 And CLIENT2) and i want when i ...
0
votes
0answers
28 views

Table functions unnest, cardinality not found in DB2 10.1 LUW [closed]

I'm having difficulty in trying to re-deploy a stored procedure in DB2 10.1 LUW. CREATE TYPE "TBSGL"."ACCT" AS ROW( ACCT_NO VARCHAR(10), ACCT_DESC VARCHAR(40)); CREATE TYPE "TBSGL"."ACCT_ARR" AS ...
0
votes
1answer
28 views

Oracle: How to create secondary index based on every word in a CLOB

I have a Table where each record is made up of a VARCHAR2 and a CLOB. The VARCHAR2 is the primary key. There are currently 1.5 million records and growing. The table is only ever used for REGEX ...
1
vote
2answers
40 views

MySQL Escape Characters

I'm getting an SQL error when trying to mix a match against in a SQL statement with a specific character which is not covered by MySQL escape functions in PHP. This is an example: The name to ...
0
votes
1answer
53 views

How to insert values through inner join

Could anybody tell me how to do insert statement with inner join. i am trying to insert ProductName,ProductCompany,ProductPrice,ProductQty in Product table and CategoryName in category Table. And ...
1
vote
1answer
21 views

Sql parent Child Query not working

I want to get parent and their child data which means, parents first, then children. Below is the query that I tried to convert to parent and their children SELECT a.FactorTitle, a.FactorCode, ...
0
votes
1answer
24 views

Pivot Results By Day [duplicate]

I have a table in SQL Server 2008, with mails received from a pop account. The table has the following columns: +------+----+---------+------+------+ | from | to | subject | body | date | ...
0
votes
1answer
37 views

Connecting to the default instance of SQL Server connects to a named instance

I have 2 instances of SQL Server installed on one server: SQL002. One instance is a default instance, SQL2008 R2, and the SQL service is generally not running except for specific tests. The other ...
2
votes
1answer
51 views

SQL query to identify missing hinted indexes

We have a fairly large number of queries using index hints in our 11.2.0.4 database. I recently uncovered some performance issues and found that some of the indexes referenced by the query hints were ...
0
votes
0answers
21 views

Query to get result in Excel file [duplicate]

The following query gives me the error Incorrect syntax near 'OUTPUT'. SELECT * FROM dbo.sales ; OUTPUT USING 'Driver=Microsoft Excel Driver (*.xls); DBQ=c:\\test\\sales.xls; READONLY=0' INTO ...
1
vote
1answer
30 views

table or view does not exist when using insert into

I'm trying to insert into but getting the "table or view does not exist" error. It tells me the specific line where the error occurs but I can't figure out what the problem is: INSERT INTO c_ord_cur ...
1
vote
0answers
28 views

Howto measure improvement after create and move new filegroups in SQL Server

How can one measure the (hopefully) positive effect of moving couples of indexes to a different finegroup, so to say seperate them from the data filegroup, and move this filegroup to another LUN in ...
0
votes
1answer
30 views

Batch for PostgreSQL query

1) the pgpass.conf (of PostgreSQL) is: localhost:5432:*:postgres:mypassword I haven't experience in batch and I have spent the day searching in the net. I'm using windows if it helps. 2) I tried ...
4
votes
2answers
158 views

Distinct Combination of Two Columns

I am currently trying to find distinct combinations within a table as two of the columns have a many to many relationship with each other. The data is all around backup policies being run on against ...
2
votes
0answers
11 views

Alternate in access database [migrated]

I want to check whether a row exists or not before inserting a new one in Access 2007. I have the following working query in SQL Server but I'm having trouble converting it to Access. update category ...
1
vote
0answers
28 views

HQL pagination returns same result on different pages - with SQL

We have the following query in the server (using sql): Query query = this.entityManager.createQuery( "SELECT type, id, date, amount FROM Transaction ORDER BY type ASC"); The type field ...
1
vote
1answer
43 views

Merging adjacent date intervals into a single row

Here is the scenario: If the employee transaction is continuous display as-is. if the employee got any gap display as a new row. Below is the sample table Emid Fromdate Todate ----- -------- ...
0
votes
1answer
29 views

Why we can FLASHBACK DROP table where it is DDL command and hence autocommited?

DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider ...
2
votes
1answer
22 views

Problem with “not” in full text search

I don't understand why I'm getting these results with my full-text search query in SQL Server 2014. The following query returns results with "Supervisor" in the Title field and "Tokyo" in the ...
2
votes
1answer
32 views

Extend a single week's win/loss ratio calculation to a whole season

I'm quite inexperienced at SQL, and I'm trying to improve a query in a small project. The database schema looks somewhat like this. (I've reduced to relevant information, if there seems to be ...
4
votes
1answer
45 views

How to deduplicate SELECT statements in relational division?

I have a query that does a lot of duplicate work: SELECT visitor_id, '1'::text AS filter FROM events WHERE id IN (SELECT event_id FROM params WHERE key = 'utm_campaign' AND value ...
1
vote
1answer
23 views

How to merge partitions in Postgres?

I have a table that is partitioned by date on a monthly basis beginning January 1, 2014. There is significantly more rows of data in 2015 than there were in 2014 and the data in 2014 is not accessed ...
2
votes
2answers
80 views

Select Count of one table into another

I have one SQL statement as: SELECT ARTICLES.NEWS_ARTCL_ID, ARTICLES.NEWS_ARTCL_TTL_DES, ARTICLES.NEWS_ARTCL_CNTNT_T, ARTICLES.NEWS_ARTCL_PUB_DT, ARTICLES.NEWS_ARTCL_AUTH_NM, ...
0
votes
0answers
41 views

Upsert - Should I verify the row exists first, attempt an update, or try to insert first?

In a software that I'm working, I have a table with a few columns and a id. In some events in my software, I need to update some column of a record with a certain unique field (other column), but ...
1
vote
3answers
72 views

Finding “islands” of holiday periods for employees

I'm looking for some tips to find a nice solution for my problem: Let's say I have a table, where days of vacation of employees are stored. e.g. CREATE TABLE test_dates ( id NUMBER ...
1
vote
1answer
40 views

fetch data from two tables

I have two tables one for property detail and other for amenities. In amenities i have inserted data one by one means it has three column amenity_id,property_id,amenity. Now I want to get the property ...
2
votes
1answer
65 views

How do i reverse this SQL Query Script, I am trying to remove filestream from my SQL 2014 Server

I am trying to remove filestream from my database, so I have to do the following I would like to know how to remove the following: ALTER TABLE AMGR_Documents_Tbl ADD UId uniqueidentifier ROWGUIDCOL ...
1
vote
0answers
14 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
2answers
76 views

FLUSH TABLE : can't execute the given command because you have active locked tables mysql

If i execute these queries one by one , all get fail except first : Query1 : flush table tablename1 with read lock; Works Query2 : flush table tablename2 with read lock; Error : can't ...
2
votes
0answers
34 views

How to implement search for world-wide Wikipedia/OSM places with alternative-names

I would like to implement a search-backend which returns a location-aware search results of all Wikipedia / OSM place names, while recognising place names in all languages. Thus, if you search for ...
1
vote
1answer
18 views

Logins mapped to dropped database sql server

I was told to delete the logins that are mapped to a database ABC but unfortunately the database ABC has been dropped and no backups exist. Is it possible to find the logins that WERE mapped to ABC? ...
6
votes
2answers
264 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 ...
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 ...
3
votes
1answer
120 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
13 views

Feature of database-level audit in SQL Server 2012 Express edition

Like in SQL Server 2012 Enterprise edition, can we have database-level audit in SQL Server 2012 Express edition? Can we have Audit Folder in Security Tab?
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 ...