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
20 views

error - order by must appear inthe list of statements contains a union,intersect or except operator

In sql i want to add 'All' in the list for that I used the following code : select 'all' as stateid,'all' as statename union select stateid,statename from state order by stateshortname
1
vote
0answers
70 views

MYSQL — Inventory View with calculated fields

Trying To create an inventory view. I have created this query using MS Access query builder since I am no pro at SQL and this is pretty convoluted. When translating this into MYSQL syntax I get an ...
0
votes
0answers
191 views

Reviewing ways to generate monthly tables using PL/SQL automatically

I am attempting to automate a set of tables that my colleagues produce monthly, using PL/SQL. The set of tables contain data about different metrics (e.g. Customer revenue, customer demographic ...
4
votes
1answer
3k views

Restore master database from SQL Server 2008 R2 on SQL Server 2012

I have a process that restores all my backups on a centralized server for testing. I have recently upgraded this server to SQL server 2012, and now I am getting an error when restoring master from SQL ...
1
vote
0answers
27 views

SQL Server 2008 R2 - Capture or be notified when an sql handle is ran

I'd like to monitor a particular SQL handle and find out who (process id) is running the SQL. Is there a way to retrieve either a list of proc ids who executed the SQL handle in last N minutes or be ...
1
vote
0answers
33 views

Sampling and matching samples with spatial conditions

The Problem I have two tables with start points of a route and possible end points of a route which need to be matched against each other. I also have a given distribution for the distance between ...
1
vote
2answers
177 views

Query to return zero if nothing exist

I have a table With currency, paymenttype and invoiceamount. I have to write a query to get currency, paymenttype and total invoiceamount made. This is pretty simple with group by. But actually I ...
1
vote
1answer
32 views

Only LIMIT rows with specific number

Sorry for the title, As I'm not that experienced I had a real hard time trying to come up with a proper title. So I have this query to fetch all the comments of a specific topic. Now for my paging I ...
0
votes
1answer
117 views

Best query to exclude existing friends from query

I have a User table / id_user / username / .. friends table (pair AB and BA): / friend_of / friend_to and a friend_request table (used for pending friend requests) / id_req/ sent_from / sent_to ...
0
votes
0answers
25 views

FACT table loading

I have the below table structure : Ticket# Tckt_type Amount Acct Num 1 abc 10 1001 1 def 20 1002 So, for the same ticket number, there ...
1
vote
1answer
53 views

Finding the current prices for n Fuelstations at a certain point in time

I have a Table where price information is stored in with approx 13 million rows stored in a PostgreSQL 9.5 database. CREATE TABLE public.de_tt_priceinfo ( id integer NOT NULL DEFAULT nextval('...
1
vote
1answer
465 views

Using LIKE or Full-text when searching database

I am fairly new to SQL and I am currently trying to create a search function for my website, much like the one on imdb.com. I have 3 tables and I need to search two rows (nvarchar) in each table when ...
1
vote
2answers
18k views

sql server management studio backup error [closed]

hello, i want a back up for my database in sql server management studio. i am using ms sql. but when i try to back up , i take the following error: =================================== Backup failed ...
1
vote
1answer
47 views

Is there a way to get disc space remaining using an SQL query on an Oracle database?

We experienced a problem recently where the disc space ran low and some queries started giving unpredictable results. The root cause was that we'd take too many restore points and filled up the disc. ...
0
votes
2answers
641 views

how to identify table name using a column name if we are not aware about the table name

how to identify table name using a column name if we are not aware about the table name Imagine if you have 10000 tables in Database I am aware about the column name , but i dont know the exact ...
-2
votes
1answer
2k views

Arithmetic overflow error for type varchar [closed]

This is my query: insert into [Consolidated_LoB].[dbo].[consold] ([shp_to_pstl_cd] ,[LoB] ,[catlg_nbr] ,[shpd_qty]) select [POSTAL_CODE] ,[LoB] ,[PART] ...
0
votes
0answers
35 views

How to fetch data on a single column from a table having composite key [duplicate]

I am working on a messaging application, where the users can have a group conversation with multiple recipients. So in my database, I have a table called user_message where user_id and message_id is ...
0
votes
1answer
2k views

How to create a stored procedure with columns from three different tables in SQL Server 2008?

I'm a fresher and I would like to get some suggestions on how to create a stored procedure in which I have to select columns from three different tables. 2 columns customername and customercode from ...
0
votes
1answer
425 views

Oracle update join tables

I want to update my db records, which are related one to many. Please consider the following example : A person can have many contacts Stating above implies that contact table has a person_id ...
1
vote
2answers
953 views

LIMIT to one row per ID

This query gives me multiple hits because of each assetid may contain many vb.title. How can I limit the result to only 1 hit for each assetid? 'select DISTINCT v.id, v.purchasedate, ...
-2
votes
1answer
68 views

What are the ways to handle temp table with more than 9000 rows? [closed]

I am creating a temp table in my stored procedure which has more than 9000 rows and I read some articles saying that its bad practice to use temp table with more than 9000 rows as it uses temb db for ...
0
votes
0answers
52 views

How to fix slow performed query with joins while pagination?

I've following tables with respective records. 1) wi_individual_g with 85K records 2) wi_organization with 550 records 3) wi_program with 2 records I've used following query for creating ...
0
votes
1answer
509 views

How can I reseed all identity columns?

I deleted all rows from 20 tables after disabling the constraints, triggers using the stored proc SP_MsForEachTable. Now before the import happens, I need to reseed all the identity columns back to ...
0
votes
1answer
765 views

What is the correct SQL for using where ID in List and the items are with AND operator

I need a SQL Statement to use IN operator within the items with "And" connected. SELECT * from tbl_Users WHERE id in [1,2,3] is equivalent to: SELECT * from tbl_Users WHERE (id=1) or (id = 2) or (...
2
votes
1answer
256 views

Search within same ID across several rows

My table has the following structure: +----+---------+------------+-------------+ | id | word_id | meaning_id | sentence_id | +----+---------+------------+-------------+ | 1 | 1 | 15333 | ...
1
vote
1answer
271 views

SSIS Transaction Problem

I am trying to use SQL transaction with my SSIS package so if any error comes changes are rollback. I am using an "Sequence Container" in Control Flow for this and setting its "Transaction Option" to ...
0
votes
1answer
20 views

How do I write this MySQL query?

I have a table with a column of numbers that are consecutive but not concurrent. So 91 may be followed by 92 or it may be followed by 96, for example) with intervening numbers not in the column. I ...
0
votes
2answers
88 views

WHERE clause converting INT to DATETIME

Looking for advice on how to match two differing column types: I have two tables: RT-A and DA-B. Under both tables there is a column named DeliveryDate, but the individual tables use differing ...
0
votes
0answers
48 views

Database Hangs while trying to run sql

I have a challenge with SQL Server. We are running SQL with an inhouse developed application. Lately we have been having a challenge where the DB hangs and users are unable to access the application. ...
1
vote
1answer
204 views

PostgreSQL isn't using indices in FULL OUTER JOIN

I have these tables I'd like to do a FULL OUTER JOIN between. They both have an index on (ts_minute, user_id, campaign_id, host_id) (which matches the join condition). In the following query, if I ...
2
votes
3answers
1k views

find all numbers that appear at least three times consecutively

This is a question from leetcode online judge I tried this SELECT Num FROM Logs GROUP BY Num HAVING COUNT (Num) >1 but got runtime error!! Anyhelp would be appreciated
0
votes
0answers
43 views

how to optimize a query with a lot of join

I have the following query: SELECT DISTINCT s0_.id AS id0, s0_.created AS created1, s0_.style AS style2, s0_.styleTitle AS styleTitle3, s0_.styleTitleDescription AS ...
0
votes
1answer
46 views

Optimizing a MySql query

Here's the query: set @_YEAR = 2014; set @_MONTH = 1; set @_PRODUCTS = '53'; set @_START = date(@_YEAR * 10000 + 1 * 100 + 1); set @_END = date_add(date(@_YEAR * 10000 + 1 * 100 + 1), interval 1 ...
2
votes
3answers
5k views

Find duplicate records across one-to-many relationship tables

Let's say I have two tables that have a one-to-many relationship: People and Attributes. I'm trying to find duplicates based on the first_name, last_name, and all attributes must match exactly. ...
1
vote
1answer
176 views

Join and count boolean columns by logical OR

There are two tables: user_table and user_schedule. Each user has four schedule rows (one for each 6 hour period per day) with one column for every workday. The desired result is to count the number ...
0
votes
1answer
216 views

set unique key checks 0 in mydql

In a table i have unique value check for the combination of box_number,price,start_val,end_val so there can not be a two box number with same number which have same price , start_val and end_val.. ...
-1
votes
1answer
209 views

Query optimization and takes less execution time

CASE-1: SELECT * FROM A JOIN B ON B.CustomerID=A.CustomerID CASE-2: SELECT * FROM A JOIN (SELECT * FROM B) C ON C.CustomerID =A.CustomerID Which CASE is efficient ?
0
votes
1answer
67 views

How to remove the duplicate records from primary filegroup after partitioning?

Basically, our database is created based on primary filegroup alone. Now, I have done partitioning for a large table which was approx 25 GB. The partitioning was done on secondary filegroups which ...
-1
votes
1answer
67 views

System.Data.SqlClient.SqlException when try to run simply queries

I've installed SQL Server 2012 Express. I'm doing some test with this and Server 2008 Express. With 2008, all is ok. But with the 2012, I get some troubles. I'm using it from my c# app, with code ...
1
vote
0answers
207 views

Multiple login mappings for linked server

Can we assign two logins for two different databases on target server. For example, I would like to create a Linked Server between ServerA and ServerB. ServerB has two databases on it and both using ...
0
votes
0answers
52 views

SQL Server 2012 Downgraded from Enterprise to Standard. Permission errors

We downgraded our SQL Server from Enterprise to Standard. Now on 3 of my 5 non system databases a lot of users no longer have effective permissions. My admin account is in the same boat. I have ...
0
votes
2answers
645 views

How to select the largest value of the same column in multiple tables?

I have multiple tables which have some columns with the same meaning, eg., event_timestamp. The schemas are like following: Table A: +-----------------+-----------+------+-----+---------+------------...
0
votes
2answers
27 views

Added a unique index, but cannot find it in TABLE_CONSTRAINTS

I'm running the following command in my DB: CREATE UNIQUE INDEX [UK_NAME1] ON [TABLE1] ([COLUMN1] ASC); Now, I'm running the following query: SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS ...
2
votes
1answer
479 views

INSERT multiple values via join

I have three tables, representing two entities and a connection relationship. Each of the entity relations has an artificial surrogate key used for foreign key constraints in place of natural keys. ...
1
vote
1answer
134 views

Multi table GROUP BY query optimisation

Currently I have a multi table/column GROUP BY query which is pretty slow. Below is the tables schema used by the query: report Table ...
0
votes
2answers
196 views

Process staging table by getting unique rows only (incremential)

I had simple problem with etl on staging table that was answered here: Process staging table by getting unique rows only Now after apllying solution I spotted that will be unusable in production ...
1
vote
1answer
500 views

Reuse SELECT query by adding results in array?

I have written a PostgreSQL function, which returns a specific order of products. Now I would like, not only to show, but to put the results of the first SELECT query to an array as well, so I can ...
-2
votes
1answer
90 views

Process staging table by getting unique rows only [closed]

I have several staging tables where I bulk import data with duplicates and sometimes I upload historical data mixed with real-time data, just importing everything without caring if it is already in ...
1
vote
1answer
11k views

Sum(Case When) Question

I'm trying to get the syntax correct on my query but I'm having issues. Currently my query will show the Household Total Value (HHValue) and then the NationalValue on one row, then the second row ...
0
votes
1answer
88 views

How can a primary key be inserted as foreign key?

Hi I am building an order intake form that will have information submitted belonging to 3 tables. The order table will have the primary key used for the property and customer table. How can I ...