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
1k views

Export just rows modified in last month (date defined by user)

I'm asking if it's possible in MySQL to export just rows modified in a period of time for example in the past month, if it is possible, how?
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 ...
0
votes
1answer
559 views

Need to find the number of employee who are not in all department

i have a table T1 which has two columns empname , department every emp must be in 37 department , but there are some employee who are in less than 37 department . I need to find the names of emp who ...
0
votes
1answer
23 views

Creation of a simple field failed in MySql [duplicate]

CREATE TABLE bdt.transactions ( `transaction no` int UNSIGNED NOT NULL AUTO_INCREMENT, data date , by int NOT NULL , hours int NOT NULL , ...
0
votes
1answer
4k views

Subtract values from SELECT in SQL

I have the following 2 queries.: shipManagementInvoiceNetValue: SELECT IFNULL (SUM (shipManagementInvoice.netto ), 0) AS shipManagementInvoiceNetValue FROM tckopf AS shipManagementInvoice WHERE ...
0
votes
2answers
122 views

How can i use the same query for all codes?

I use this query in PHPmyAdmin, i have date that contain(date,hour),supp, price and code_product (it repeated many times) : I want to return the last price for the last date of product code ...
0
votes
1answer
269 views

Trouble in Multi-Language DB

I'm working on a DB design for medicines in many countries with different languages. I created a table for the different languages I want and link it to another table of chemicals. But I find that ...
0
votes
1answer
73 views

Select a value for the max

I have a query which selects a max value over all the values of in a datetime column (dtimesent). select dtimesent, max(ResponseTime) AS maxtime FROM allrequests where Request like '%Requestsa%' ...
0
votes
2answers
983 views

SQL Server 2012. Update second table based on input from first table

I have two tables in two databases: Table01 in Database01 and Table02 in Database02. Both tables have a column called UserID. The UserID column is created when the user creates a new account on the ...
0
votes
2answers
2k views

How can I truly DELETE data from a SQL Server table - still shows up in notepad in backup file?

SQL Server DELETE only marks as DELETED though leaves as data in the data pages. Data pages are 1024 KB in size and this problem can be eliminated by adding a FILLFACTOR that ensures only 1 record ...
0
votes
3answers
125 views

How to convert query results into groups of common results

I have a query that returns results like this: MyValue MyGroup _______________|_______________ 56 | I _______________|_______________ 12 | I ...
0
votes
1answer
126 views

TSQL - Bringing Data Together from Different Sources …refactoring PK and FKs

I have various offices and one central head office. Each office has its own SQL Server instance so each office has its own data set with its own set of IDs. Each office has already imported data ...
0
votes
1answer
144 views

Dynamic Pivot with overlapping years [duplicate]

I have looked and cannot find how to show the last # months in a pivot table. Everything I seem to run across codes months statically. I'm seeking a solution that shows something similar to the ...
0
votes
3answers
65 views

Using start and end event logs to create a table/view containing spans between the times of each log

To be specific, I have an event table that logs when a user joins or leaves a team. It looks something like the following: ------------------------------------- | user | event | team | timestamp | -...
0
votes
1answer
426 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 ...
0
votes
3answers
4k views

INNER JOIN making COUNT(*) slow

I have a very simple query: SELECT COUNT(*) FROM messages INNER JOIN users ON messages.user_id = users.user_id With the join it takes 1146 ms and without it takes 220 ms (220 ms still seems slow to ...
0
votes
3answers
97 views

How to return last used record?

I have simple table called ips with 2 field ip and address, which schema is following CREATE TABLE IF NOT EXISTS `ips` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `address` varchar(255) ...
0
votes
3answers
3k views

ORA-00905: missing keyword when trying to create a synonym

I have a problem when I try to create a synonym like this: CREATE SYNONYM B2H.BILLING_USER_ACTIVITY@PACOMNET FOR B2H.BILLING_USER_ACTIVITY@PACOMNET.US.ORACLE.COM; Why does this happen and how can ...
0
votes
2answers
978 views

How to mix pairs of rows without JOIN (in a temporary table)?

I have a TEMPORARY table containing pairs of key/value as id col1 col2 type pair 1 key1 key2 key 1 2 value1 value2 value 1 3 key3 ...
0
votes
0answers
79 views

Why does the SQL Server 2012 SA account always show up with creation date of 4/8/2003?

Why does the SQL Server 2012 SA account always show up with creation date of 4/8/2003? I am not sure why this is happening and if this is something that needs to be resolved. I noticed that on ...
0
votes
2answers
2k views

Only allow one checked row in a Column in SQL Server [duplicate]

Background: I've only just begun programming in SQL... barely begun even. But I have been thrown into the deep-end left to find a way and learn to program SQL. Question: Is is possible to have a ...
0
votes
2answers
475 views

Get Date Limitation

So what I need is a limitation for the user of the funded contracts. So this is what I am trying to implement. I am trying to let the user enter dates within the year 2014, which will bring up how ...
0
votes
1answer
3k views

how do I merge two views

I have basically two views named customerV and productV. the SQL statement in the customerV view is : SELECT CUSTOMER_REGISTRATION.SURNAME, CUSTOMER_REGISTRATION.OTHER_NAME, ...
0
votes
1answer
41 views

INSERT INTO + Selects - PostgreSQL 9.2 [duplicate]

I wanna add some data into two columns in a table. Would be something like: INSERT INTO dm.billables_links (billable_id) VALUES ((SELECT billable_id FROM junk.wm_260_billables2 WHERE info ilike '%...
0
votes
0answers
64 views

SqlServer: can one table's PK be the FK of two table's?

I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id) My question is, is it possible if the project_id and ...
0
votes
1answer
668 views

MySQL table with multiple values in one field

I'm building a database for a real estate company. The real estate properties are managed by sales people using a CodeIgniter website. How should I proceed with the database design for this table? ...
0
votes
2answers
2k views

ERROR: could not find array type for datatype information_schema.sql_identifier

I am trying to run the below sql command: SELECT ARRAY( SELECT column_name FROM information_schema.columns WHERE table_name ='gis_field_configuration_stage' ); and I get the below ...
0
votes
4answers
314 views

Single SQL query to retrieve data in a particular order (not ASC/DESC)

I have a MySQL table, let's say called reviews review_id, product_id, language_id, rating, review, author language_id ranges from 1 to 10 for 10 different languages. Let's say I determine that my ...
0
votes
1answer
245 views

Use serial ID for multiple columns and return it from INSERT

I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value. This was done fine in ...
0
votes
1answer
57 views

Oracle: How can I create a where clause programmatically?

Suppose I have a table like: TBL_CONDITIONS | ID | WHERE_CLAUSE | |----|---------------------------------------| | 1 | fruit = 'apple' | | 2 | ...
0
votes
1answer
45 views

Postgis radius search

I have a table setup with the following columns, and it containts about 100K listing of data. Column: address | Type: Text Column: geo_point | Type: geometry(Point,4326) My question is for ...
0
votes
1answer
880 views

Optimising MySQL query with lots of self-joins

I have an entity_relationship table that describes relationships between entities. Each relationship will have 2+ entities involved. In some cases it can be said that one entity is a constituent of a ...
0
votes
1answer
1k views

Query improvements without UNION

I have the following scenario. A users table, a records table and a memberships table. A user can be associated to zero or more records either directly with the records table records.user_id = users....
0
votes
1answer
93 views

Logic Value of Assignment Expression

I'm doing the leetcode SQL problems and kind of confused with the code below: select distinct Num from ( select Num, case when @prevNum = Num then @count := @count + 1 ...
0
votes
3answers
438 views

Suppress display in a column if the value is equal to the value in another column

I have 2 date columns that display side-by-side. I have some dates that are equal. I do not want to display the "equal" dates/duplicate date values in one of the columns. How do I do that? Example ...
0
votes
1answer
63 views

Get equivalent SQL query for any Postgres meta-command

I hope the title is self-descriptive. I want to be able somehow to translate any Postgres meta-command into its corresponding/underlying SQL query, at least to learn more about Postgres and the way it ...
0
votes
1answer
156 views

Grouping rows by looking at two columns without considering the order

I have a table looks like this one below: +----+---------+---------+ | ID | VALUE 1 | VALUE 2 | +----+---------+---------+ | 01 | A | B | | 01 | B | A | | 02 | C | D ...
0
votes
0answers
104 views

automate import and export of process EXCEL -> SQL SERVER - > EXCEL without using SSIS

I have an ETL process that is entirely written using T-SQL scripts. The script grabs an Excel spreadsheet and imports it using openrowset. Once I get the transformed data I copy with headers and paste ...
0
votes
2answers
6k views

How to manage a huge database?

We are building a service witch require a huge database. We are actually planning on getting more than 100 millions rows in our biggest table. So, what are the best options to store all this data in ...
0
votes
1answer
44 views

Problem with foreign keys

I do ALTER TABLE bdt.auth ADD CONSTRAINT `depositor no.` FOREIGN KEY ( `depositor no.` ) REFERENCES bdt.depositors( `depositor no.` ) ON DELETE CASCADE ...
0
votes
0answers
60 views

Retrieving a record for the last date received and returning the value of another column in that row

I have the following query which is returning me total number of expected records : select a.New_FirstName + ' ' + a.New_LastName as 'Account Name', a.New_MPRNNumber,MAX(b.New_readdate) as 'Last SC ...
0
votes
1answer
5k views

Copy files using script to a network share

Is there a simple script I can use to copy files in a particular folder to a network share? This is for use with SQL Server Agent so I can run it as a step after backups. Thanks
0
votes
2answers
411 views

Calculating values from three related tables, without using join or union

I have a simple basic three tables on SQL Server database: What is needed is to get the quantity available, without using joins or unions which is calculated: Quantity Available = Items Opening ...
0
votes
2answers
144 views

Query Cache Implementation Details

I am new to DBA stack exchange & this is my first question. Kindly help me in finding a low level documentation for Query Cache module in MySQL since I am planning to work with the source code of ...
0
votes
1answer
61 views

How Can Get Random 2 rows FROM EACH GROUP FROM JOINED TABLE

EXAMPLE Table Dept: dept_id dept_name dept_status --------------------------------------- 1 Sales 1 2 Develpment 1 3 HR 1 ...
0
votes
1answer
166 views

PostgreSQL extending UPDATE query

I have the following update query to populate a table storing user stats users: int (pk) user_id date last_visit int (fk) local_site sites: int (pk) site_id site_stats: int (fk) site_id ...
0
votes
2answers
2k views

Set default value for all columns with specific name within a database

I've got a database that includes an int column project_id on many tables, and I want to script setting the default value to a specific value for all columns with that name in the database. Note that ...
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 ...
0
votes
2answers
1k views

How to index for a query with independent range conditions?

What is the best way to index a table that I query using independent range conditions? E.g. WHERE X < ? and Y > ?
0
votes
2answers
10k views

How to insert or update using single query?

I have a table test having columns id which primary key and auto incremented and name. I want to insert a new record if annd only if there are no records.For example input is id=30122 and name =john ...