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

learn more… | top users | synonyms (2)

1
vote
1answer
5 views

Optimize MySQL Query with multiple subqueries

I have the following Query: SELECT p.id AS post_id, p.status, p.user_id AS post_user_id, (SELECT pl.id FROM postlocks AS pl WHERE pl.post_id = p.id) AS postlock_id, (SELECT COUNT(rn.id) FROM ...
0
votes
0answers
8 views

Group by account_id sum(size) / Postgres 9.2

The below query shows me the account_id, size, name (File name) and also ino (File's id). WITH accounts AS ( SELECT id FROM clients WHERE NOT ( (price = 0) ...
-1
votes
0answers
24 views

Which columns need index in recursive table and soft delete [on hold]

I have a Category table like below : I have a recursive relationship on this Category table. I use soft delete in my project. I have 2000 rows in Category table. When I want to show data in a UI ...
-3
votes
1answer
33 views

Could someone explain how PHP, MySQL, SQL work together?

I feel so confused on what they are and how they work. From what I've read PHP is a server-side language. Therefore, it's a language basically designed to talk with the web server to get it to do ...
-1
votes
0answers
10 views

Update column with different value for multiple rows [on hold]

I often find myself using excel to create update queries. When I need to update multiple rows with different values for a column, I simply use excel to create the update queries (multiple). Here's ...
1
vote
1answer
20 views

Why doesn't Oracle show my privileges?

I have a user that is able to drop, alter, create tables and indexes. But when I list the privileges for my Oracle user it does not show any CREATE TABLE or other such privileges. How am I able to do ...
-1
votes
0answers
14 views

Grant access schema to user [on hold]

Getting error while writing the following query SQL>grant select, insert, update, alter, delete on schema::schema_name to user; showing as missing keyword –
1
vote
1answer
43 views

Syntax Error Help [on hold]

I have this script that works just fine but when I change the directory paths it seems to get this syntax error "unclosed quotation mark after the character string" I have looked over this for hours ...
1
vote
1answer
15 views

MySQL trigger to update a key if record exists

I'm trying to build a trigger in MariaDB to follow this logic: 1. If the primary key val exists, increment the counter column 2. Else, insert the primary key into the table Here is my prosed ...
0
votes
1answer
14 views

How to create tables with geometry in Postgres?

I need to create 2 tables with distinct geometry. One needs to be a table that has point information. The other needs to be a table that has line information. They do not need to be populated as ...
1
vote
2answers
51 views

How can I represent a list of words and related words in a relational structure?

I have a list of header words and words as below and I am trying to understand how best to represent these in a relational table: Header Other Words Words abandon abandoned, ...
0
votes
1answer
19 views

How to create a table with data from other tables in Postgres?

How to create a table that with data from 6 other tables that have the same columns? The table names are: ab1_2, ab3, ab4, ab5 and ab6. This is a spatial data with the Postgis extension enabled so ...
0
votes
0answers
26 views

Can I rely purely on an auto-generated unique primary key for CRUD? [on hold]

I am in the process of developing a large web-based application that uses a SQL server database for data storage. There will be multiple accounts all sharing one database for generic account related ...
4
votes
1answer
42 views

QUERY - join two tables based on date and filter results

My apologies for the cryptic title. I guess my inability to condense my question into a title is indicative of my inability to come up with an answer to the following: I have data in two tables (MS ...
-1
votes
0answers
23 views

Decomposition of complex queries [on hold]

select c.ssn,c.cname,c.address,c.ph,c.age from customer c,account a,acc_cust ac,loan_cust lc where c.ssn=ac.ssn and a.accno=ac.accno and c.ssn=lc.ssn and a.atype='joint' group by ...
1
vote
3answers
125 views

When to use DISTINCT [on hold]

When is it good to use SELECT DISTINCT .. and when not? Basically whenever I see DISTINCT keyword I start to get cautious as most of the cases it is ends up finding that actual query has some ...
0
votes
0answers
32 views

Deadlocking on two update statements

Server Info: Microsoft SQL Server 2005 - 9.00.5057.00 (X64) Standard Edition (Build 7601: Service Pack 1) I have a table called LAB it has about 38 million rows in it. The primary key is a single ...
0
votes
2answers
40 views

Update Grouped records

I have the following query that returns duplicate values: SELECT [t].[Field1], [t].[Field2], COUNT([t].[Field2]) FROM [dbo].[Table1] AS [t] WHERE [t].[Field2] <> '' GROUP BY ...
3
votes
3answers
62 views

Get last two IDs from rows for last two days

I have a table that looks like this: +------------+------------+--------+----------+--------------+ | terminalid | date | toolid | toolname | workcenterid | ...
0
votes
0answers
21 views

Getting count mismatch for output records [on hold]

I want count of incorreclty routed task .first query count is correct 1. select * from statuschanges where incorrectlyrouted='t' count=388 and when I modify it according to requirement as ...
0
votes
1answer
34 views

Accessing computed column in a nested query

I am using nested sets to represent tree-type date in SQL-Server and basing what I'm doing on this article: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ by Mike Hillyer. In ...
0
votes
1answer
22 views

SQL Server Msg 2812, level 16 state 62 line 30

i want to make automatic backup and restore my database on SQL Server 2012. i have made automatic backup with script in my windows server. now, i want to make auto restore with script, i have found ...
0
votes
2answers
19 views

Query Amount of entries per column from multiple tables + provide table name

I have five tables with all having the same column (objart): I queried for the number of entries and combined the result with UNION, which worked well. Two questions: 1) Is there a shorter/ better ...
3
votes
2answers
35 views

How to assign a constant to a value on all rows, if that value is adjacent to some value on any row

I have data like this (STATUS, and NAME are the column headers): STATUS NAME ACTIVE A1 INACTIVE A1 INACTIVE B1 INACTIVE B1 I want to include a CUSTOM field that returns a ...
2
votes
1answer
22 views

Postgres - Regroup rows with at least one common attribute

I have the following table : Letter | Figure -------|------- A | 1 B | 1 B | 2 C | 2 C | 3 D | 3 E | 4 I would like to group rows that share ...
1
vote
1answer
15 views

What run-time configuration parameters are used for concurrent index creation

Suppose I were to run the following. The first line shows the server's default maintenance_work_mem value. mhildreth=# show maintenance_work_mem; maintenance_work_mem ---------------------- 16MB (1 ...
5
votes
3answers
342 views

Capitalize only the first letter of each word of each sentence in SQL Server

I do want to capitalize only the first letter of each word of each sentence from a SQL column. i.e: if the sentance is 'I like movies' then I need the output 'I Like Movies' Query: ...
-4
votes
0answers
30 views

if increase table in a database (20 to 20000) on there database performance is effected or not in sql [on hold]

in that time in my database having only 20 table but now our business model change and there need 2000 table in future. so i cant understand that database performance is effected or not.if effected ...
0
votes
0answers
24 views

Query Across Multiple MySQL Databases

The majority of our clients use the same MySQL schema and a number (subject to the usual conditions) are willing to give us a copy of their data for research. I have written a utility which merges the ...
1
vote
2answers
30 views

Dynamic script to Drop Indices of a specific column

I´m currently writing an SQL script to update our database and for that I need a script to drop indices that rely on a specific column (because I also want to drop the column). In my script I have ...
0
votes
0answers
30 views

How to run a execution plan and how to make use of it [on hold]

I am new to the SQL. Can you please explain about EXECUTION PLAN and EXPLAIN PLAN in TOAD with some examples?
0
votes
1answer
29 views

Raise Multiple Exceptions Postgres

I am using Postgres 9.5, and I am trying to create exception handling in a stored procedure/function. I can't quite figure out how to raise the error should any of the 'INSERT' statements fail. If ...
4
votes
4answers
323 views

Summing data based on distinct dates

I am trying to get an accurate count of total days a tool is out for rental. Here is a data sample: CREATE Table #tmpToolRentalDays ( ToolId BIGINT, StartDate DATETIME, ...
-1
votes
2answers
121 views

Retrieve data from two tables where table 2 has multiple entries for one value

I am looking for the best way to create a SELECT statement to pull data from 2 tables. Example below is the table structures: Table 1 | Table 2 ...
-1
votes
0answers
17 views

oracle join from multiple tables [closed]

I have to extract data(Table1.Name,Table1.ID,Table2.desc,Table4.desc) from four tables. ->Table1(Name,ID,Group_Id) ->Table2(Group_Id,desc) ->Table3(Group_Id,perm_id) ->Table4(perm_id,desc) In ...
0
votes
0answers
21 views

Searching a text with space in mySql match against Query

I want to search a text in mysql DB with [space] (Ascii 32) as a part of it. But I am not getting the correct result. My MySql Query is: SELECT count(id) as total , MIN(product_price) as ...
-3
votes
2answers
41 views

Select in select [closed]

I am doing some test on the internet as preparation for my test tomorrow. I would like to know what I am doing wrong here. Thanks a lot!
3
votes
2answers
164 views

select distinct one column with all instances of another column in a second column [duplicate]

If I have a table with the following information: ParentID | ChildID ---------+-------- 6 | 6 6 | 7 6 | 8 6 | 9 6 | 10 I need to ...
0
votes
0answers
14 views

Extract an image from an SQL database using PHP [migrated]

I am trying to extract an image from my database using this code: HTML : ><img src="imagePrevie.php?ID=<?php echo $lastImage2['id'] ; ?>"><?php $lastImage2 return the true value ...
-1
votes
0answers
18 views

how can I show all elements [duplicate]

Hey guys I have a probleme at showing all elements informations .. I have a formulaire where i add elements and quantities, then i calculate the total price orderer by the Id. the probleme is that ...
0
votes
2answers
21 views

Unique pair checking query improving in pivot table

First of all, I have a solution, but I do not know that query can be transformed to uses join or to more effective query. I just want to improve my skill. I have seen an example online. So I have a ...
0
votes
0answers
14 views

$ sign showing in fields [closed]

I am working on my SQL for university, I have managed to save data from PHP to SQL but on retrieving the data there is $ sign in some fields and what I did not understand how come out of 2 same fields ...
-3
votes
0answers
45 views

What functions should I use to display data like that?

What functions in SQL Server 2012 should I use to achieve the result as shown below?
4
votes
1answer
58 views

Is it possible to create a dynamic join?

I'm using PostgreSQL if it matters. Assuming these two tables: id | run ----+------- 1 | run_1 2 | run_2 3 | run_3 and sample | elapsed | run -------+---------+------- samp1 | 1:09 ...
0
votes
0answers
67 views

select * from table where column contains “string”? [on hold]

I'm trying to pull rows of data that contain a word or string from a column. Example: Col1 dog cat mouse cat dog mouse mouse dog rabbit So, I want it to pull * of data that contain the word ...
0
votes
0answers
31 views

Building MySQL InnoDB clustered indexes

Assuming that you have a table like CREATE TABLE `t1` ( `search_id` int(11) NOT NULL, `provider_id` bigint(20) unsigned NOT NULL, `status` text NOT NULL, `imported_at` int(11) NOT ...
1
vote
1answer
44 views

MySQL syntax for order by not accepted in SQL Server

I'm working through the SQL snippets for nested sets in this article by Mike Hillyer. The article is using MySQL, but I'm using SQL Server. I get the following error reported when attempting to ...
3
votes
2answers
37 views

Count rows with max per group and additional condition

I have the following table: horse_main_id | race_id | horse_name | rating100 | race_result ---------------+---------+-------------------+-----------+------------- 23 | 159 | ...
4
votes
2answers
136 views

Why a SQL Server Table takes 1GB more after adding 55MB of data?

I've found that one customer database size exploded between yesterday and today. After investigation I've found a design problem in a table schema but after doing some math I know that 55MB of data ...
1
vote
2answers
39 views

Read TSQL variable from Temp Table

I am hacking around trying to write a script to restore a backup from a known folder location. The location will only ever contain one backup file. I have come up with this so far, but I don't know ...