Questions tagged [sql]
Structured Query Language (SQL) is a language for querying databases. Questions should include code examples, table structure, sample data, and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used. If your question relates solely to a specific ...
455,599 questions
1
vote
2answers
22 views
CASE statement still returning multiple rows
the following query is returning two rows per ID.
select distinct ID,
count (COURSE_IDENTIFICATION) as tot_course_id,
COUNT(case when course_level = 'G'
then (COURSE_IDENTIFICATION)
else '...
0
votes
4answers
20 views
Left join 3 tables error #1066
I'm trying to left join 3 table but getting the following error
"#1066 - Not unique table/alias: 'uid'"
I tried some of the suggestions i found online such as using an alias tbl_add_unit.uid AS ...
1
vote
3answers
27 views
Errors using UNION in SQL Server
I tried to understand how UNION works in SQL Server by executing the following script:
select *
from Production.ProductModel
where ProductModelID IN (3,4, 5, 6)
UNION
select *
from Production....
1
vote
1answer
14 views
Oracle SQL Tuning Advisor with changing query
We have an C# app which will search the DB for activity in a couple of tables. The user can specify to search for certain criteria, which in turn build upon the base SQL query.
This query takes a ...
0
votes
0answers
11 views
unable to update correct image data (binary) into image type column
SQL Server 2012
Update [dbo].[TestTable]
Set [TESTFILE]= 0xD0CF11E0A1B11AE10000000000000000000000000000000
where FILETYPE = 7
here column TESTFILE is 'image' datatype
After running the above ...
0
votes
2answers
13 views
DB2 SQL multiple JOIN issue
I have a query with two JOIN and it does not work. I get no errors. It just does not return any records. If I separate my query then it works. What Am I doing wrong here?
When I split up the query I ...
0
votes
1answer
20 views
finding distinct customer_id when the part numbers are not one per row but has delimiter “/”
Have a data set similiar to this.
Customer_id PART_N PART_C TXN_ID
B123 268888 7902/7900 159
B123 12839 82900/8900 1278
B869 12839 8203/890025/7902 ...
-2
votes
1answer
22 views
SQL Server Temp Table to a Select Distinct Count Distinct quetsion
Ok, basically I've got a lot of temp tables I've created and I'm trying to create Validation for the ProvDiff table.
DROP TABLE #ProvDiff;
IF OBJECT_ID ('temp.dbo.#ProvDiff') IS NOT NULL
DROP ...
0
votes
0answers
9 views
Hierarchical sample MySQL or cluster sample [duplicate]
A table contain data like
And i want it like this
I want all the text in one row, without repetition of user_id using MY_SQL.
0
votes
1answer
18 views
Derive additional columns based on existing column
I have 500 records with 10 columns. Based on the Date column I want to create two additional columns (date_id, record_id). The date column is unique.
Date_id column value should be: F + date + unique ...
0
votes
0answers
23 views
SQL: calculate monthly averages from arbitrary intervals
I have a log table that stores events in the form of
timestamp, object_id, state
2018-08-12 13:45 123 10
2018-08-13 15:56 183 25
2018-08-13 15:58 123 10
2018-08-15 16:...
0
votes
1answer
15 views
Is it recommended to add a separate status field to check for completion or is completer id and date enough?
I have a messaging board where users can leave feedback and if the comments are completed you click a check button. I need to retrieve who marked it completed and when. Do you recommend having 3 ...
1
vote
5answers
36 views
Select from nothing with Join [on hold]
Does SQL support using Joins with Select statements having no table reference? For example, we can say
SELECT 1,2,3
with no table reference and get a result set with those values.
But is ...
0
votes
3answers
42 views
Speeding ORDER BY clause with index
I have a query with an ORDER BY clause that is slow due to the table having over 11 million rows.
I can speed it up dramatically by adding a clustered index on the column in the ORDER BY clause. ...
2
votes
1answer
25 views
Why is my multicolumn index-only scan fetching all rows in the index and resorting just to return one row in PostgreSQL?
I have the following multicolumn index in my Postgres database:
create index activity_get_latest_idx on activity using btree (
type,
space_id,
navigable_block_id,
collection_id,
...
0
votes
0answers
4 views
Database group by column
SZ | name | Date | back_number
1234 | John | 18.8.2018 | 5512345
2391 | Bels | 18.8.2018 | 6685800
3498 | ESON | 18.8.2018 | 6685800
4531 | me | 18.8.2018 | 5512345
4531 | me | 18.8.2018 | ...
3
votes
2answers
27 views
Select record that has specific records related, but no additional records related
For a many to many relationship, for example Classes and Students, I'd like to select all Classes with exactly a given membership.
Students Classes StudentClass
========= ========...
1
vote
1answer
55 views
I need to figure out how to delimit a string in SQL
So, I'm at work atm and I had a co-worker create some SQL code for me to extract out text from a larger description field. The problem I'm running into is it doesn't stop extracting where I need it to....
3
votes
4answers
54 views
SQL Server - Case
CREATE TABLE test
(
sts_id int
, [status1] int
, [status2] int
, [status3] int
, [status4] int
)
INSERT INTO test values
('1','999','0','0','0'),
('1','100','0','0','0'),
('2','200','999','0','0'),
('...
0
votes
2answers
44 views
How to calculate the percent of the total per year
In SQL Server, I have a query
SELECT
fundingyear, tran_type,
COUNT(tran_type) AS total_tran
FROM
vw_LoanRpt
GROUP by
fundingyear, tran_type
ORDER BY
FundingYear
It returns ...
0
votes
1answer
28 views
DISTINCT key but it returns duplicates; Query with multiple JOINS - Bug in my WHERE statement?
Expectation:
I would like to get all customers who bought an article from an specific category (i. e. drones) since the beginning of the year.
Problem:
I get duplicates; i. e. the customer purchased ...
1
vote
2answers
27 views
SQL Query not working with certain times in where condition
I have a table created in DB2. This table has 4 fields ID(Integer), Shift(Integer), Start_time(Time) and End_time(Time). Data in this table looks like this. When I run the this select * from ...
0
votes
1answer
13 views
Getting/Limiting N number of results for each unique value in MySQL [duplicate]
Is there an easy way of using the LIMIT clause to only pull the top N values for each different unique values? For example, if we have 30 different, numerical rows for each A, B, and C, how can we ...
0
votes
0answers
19 views
INSERT and UPDATE data on Remote Server table
I am trying to insert and update data on a remote server table. I don't want to create a linked server connection. Below is what I want to have. Can someone please suggest how I can achieve this? I am ...
0
votes
0answers
9 views
How to create DSL.rowField() from RowN?
I am currently playing around with JOOQs DSL.arrayAgg(..) and want to query all PROJECTS with each queried PROJECT row having a nested row of its PROJECTMEMBERUSERS. I have to join a USER for each ...
-2
votes
0answers
22 views
Query selection: data with comma separated [on hold]
This is my table data:
______________________________________
|_________________data_________________|
|Manufacturer: Browning, |
|Series: X-Bolt, |
|Model: X-...
2
votes
3answers
40 views
How to escape apostrophes from user input
So I'm importing information from a .csv file into a database. However I'm having trouble with the user inputted description in the csv file as they contain apostrophes which is breaking the SQL ...
-1
votes
0answers
41 views
MoreLinq Full Join
I am attempting to do a full outer join of 2 tables, but am having trouble converting my Linq code to MoreLinq. Linq doesn't seem to have support for full outer joins, so i decided to try MoreLinq. ...
0
votes
1answer
32 views
Using the identity column to add a value to a computed column
At times I need to store a temporary value to a field. I have a stored procedure that adds it using:
Insert new record first then
SELECT @Record_Value = SCOPE_IDENTITY();
UPDATE ...
0
votes
1answer
43 views
How do I perform a logic comparison using dynamic T-SQL with an empty string?
When executing the sample code included below, with the variables I've included where name/pw are set to blank strings (''), the iif comparison operator is not returning null under any circumstance as ...
0
votes
2answers
61 views
Replacing empty string in SQL using SELECT
I'm trying to replace empty strings with a value and I can't seem to find the best way to do this.
The issue is that SOME values in the phone_number column are in a format without the numbers. For ...
0
votes
4answers
41 views
Identify if a value is appearing for the first time in a column
I'm trying to identify if the value of a column (let's call it status for now) is appearing for the first time for a given ID, or if it's previously held that value before. If its the first time that ...
-1
votes
0answers
14 views
Query to show two columns with content from a single table that is related to other 2 tables
I would like to have a query that shows the contents of all the products in a table that have combinations of products, but I am getting errors.
I'm working with Prestashop and CRM.
Those are the ...
0
votes
3answers
54 views
Get top 10 airport names from starts and landings
My scenario seems very easy but when I tried to realise it, it got complicated: I want to offer the customer his "Favorite airports".
Maybe I'm just foolish and fail to notice just a triffle…
What ...
0
votes
2answers
23 views
T-SQL syntax on creating temp table from union
This runs find by itself
(select * from #T1
union
select * from #T2)
This does not (added first line):
select *
into #T3
from
(select * from #T1
union
select * from #T2)
It ...
-4
votes
0answers
33 views
Change the column to nullable in an Oracle table ever make the existing values to null?
This seems to be a duplicate question for you but I am not asking how do I change the column to nullable . I knew the answer which is like:
ALTER TABLE table_name
MODIFY column_name type DEFAULT ...
-3
votes
1answer
25 views
Student's report
I need a student's mark report.
I have the following tables:
- user table with: user_id, first name, last name, course_id
- a course table with: course_id, name
- a subject table with: subject_id, ...
0
votes
0answers
29 views
Getting error in SQL program [on hold]
Consider:
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
var_id INTEGER;
BEGIN
FOR i IN 1..3 LOOP
SELECT length(NAME) INTO var_id FROM FULLNAME WHERE ID = i;
INSERT INTO NAMEDETAILS VALUES(i, ...
-2
votes
3answers
41 views
How to get last insert id from mysql database in php?
I want to get last insert id from my table cart in my else statement after the insert. But I am not getting the id.
Please check my code and suggest what am I doing wrong:
// Check to see if the cart ...
1
vote
1answer
12 views
Return Max Value Date for each group in Netezza SQL
+--------+---------+----------+------------+------------+
| CASEID | USER ID | TYPE | OPEN_DT | CLOSED_DT |
+--------+---------+----------+------------+------------+
| 1 | 1000 | MA ...
0
votes
4answers
28 views
Create full table view with min and max value lookup based on column groups
I have a table t1 with 4 columns:
f1
f2
quant
rate
I need to create a view that contains all records in t1 but lists the min and max quant value with the corresponding rate for each f1 and f2 group.
...
-1
votes
2answers
32 views
How to see if an ID has a certain value in other table in MySQL
I want to know if a certain ID has a certain function turned on. but the functions are in other table.
Table 1:
userID Name
1 aaa
2 bbb
3 ccc
Table 2:
functionID useID
1 ...
-4
votes
0answers
24 views
updating a SQL table from my app
browser to local host - ok
android app to local host - ok
browser to online host - ok
android app to online host - NO
Code requires to add to Manifest?
Thank you
0
votes
1answer
34 views
SQL select case as
I'm writing a sql statment. It looks like this
select case when c.used = 1 then 'Used'
else 'Unused'
end
from my_table
How can I name the col with the as keyword. I tried it after ...
0
votes
1answer
23 views
SQL WHERE statement multiple columns LIKE one value
Trying to get a SQL query where you define the value once and it searches multiple columns. I got this working for the equal operator, but I need this for a LIKE operator as well. This is were the ...
0
votes
2answers
14 views
Adding different rows in SQL based on column value as new row and new column value
I'm entirely willing to admit that I am going about this incorrectly, but here's the situation. I was given a table like this:
qtr cust_type cust_cnt
QTR1 new_cust 100
...
0
votes
1answer
40 views
SQL Server Execute Order
As I know the order of execute in SQL is
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
So I am confused with the correlated query like the below code.
Is FROM WHERE ...
-1
votes
1answer
28 views
Read a Fixed Length position based file and insert into a oracle Table in Java
Currently I am working on a solution to read a 454 character/line based huge file (minimum 50000 rows) via Java.As per the requirement it is a positioned based file, we first need to read the file , ...
0
votes
1answer
32 views
How to calculate date difference for all IDs in Microsoft SQL Server
How can I do to check if the difference between date 1 and date 2 for every ID is more than 6 months? Let me illustrate with an example.
So I have a table like this one:
+----+---------+
| ID | Y-...
1
vote
1answer
37 views
Decode not working
I can't seem to figure out why this won't work - can someone please help? This is part of a larger query, so I don't want to have to update the one that already exists - just wanna add to it -
...