Newest sql Questions

Q&A for professional and enthusiast programmers

SQL: Order by Tree BUT skip closed subtrees

I have the following table. This is a tree structure where the path column shows the full path from the root of the tree to the particular node: Path | ParentName | ChildName | includeSubTree 1 ...

Insert id retrieving data from different tables in SQL

I have a Table A with customers and a Table P with products. I also have a table C which represents the purchased product by customers. The table fields are as follow: A: user_id, Name, Surname P: ...

How can I optimise this SQL query

This query does the job, but I am sure it can be improved. Any tips would be much appreciated :) SELECT RIH.ItemName, RI.FieldAnswer, RIH.IsConditional FROM ReportItemHeader AS RIH INNER JOIN ...

Converting nvarchar (mm/yyyy) to DateTIme in Sql Server

I have a nvarchar column in Sql Server table which stores date values in mm/yyyy format. Example: 03/2017 Now how to convert this string to datetime so that I can compare this date time with current ...

count of Daily Uploads by Users

How can I get Count of Daily uploads by users. Let's say. Few users uploads data like below UserID|Username| UploadedOn |Status 768 |K9283 |01-02-2017 11:38:42 AM|Success 768 |K9283 ...

Select between dates on DD.MM.YYYY format?

How can I select all rows between two dates, where the dateformat is DD.MM.YYYY? For example between 15.12.2013 and 25.12.2013

SQL: How to select first element based on a condition?

I have a DB where i store several info about actions performed based on a date, so for example i have the following rows "John", "Action1, "2017-02-20" "John", "Action2, "2017-02-10" "Mark", "...

Select distinct values based on multiple column from table

I am having below dummy table select * from ( select 'A' as col1, 'B' as col2 from dual union select 'B' as col1, 'A' as col2 from dual union select 'A' as col1, 'C' as col2 from dual union select ...

Ajax request from select form

I'm trying to pull a value from my database when a select value is selected. This select is also populated from the same database. To be clear, I'm new to AJAX requests and don't really understand ...

Select rows with different conditions meanwhile all of them share the same key column

I already have a working solution, but i'm looking for a faster or better way to do it. key column | fruit | quantity ------------------------------ 11111 apple 100 11111 melon ...

Should I refrain from using a loop in SQL?

I created a stored procedure that would process data on those table. In order for me to processed those data I have to do a while loop, because I have to compute for the end date which is not ...

How to explode on varchar in hive

I have a requirement where i need to explode a varchar to get data into multiple lines. Sample data: 1, ab cd ef 2, ee dd Output expected: 1, ab 1, cd 1, ef 2, ee 2, dd Please suggest how ...

Remove 'ID' from SQL query

I have a query which adds or removes zeros from ID. SELECT LEFT(STUFF(SystemID + REPLICATE('0', 9), 7, 0, REPLICATE('0', 9 - LEN(SystemID))), 9) as OBJECT FROM Products So ...

Select Distinct for multiple joins

I am facing a problem here, I need to do a query that is like this following : select client.name, client18n.display_name FROM Client client JOIN TEAM team on (client.id = ...

How to do inner join in Vertica Update?

I was trying to do a inner join with 3 tables in a update query. I tried to find the solution in multiple sites but didn't get the solution. Following a sample query I am trying: UPDATE TGT SET C1 = ...

SQL Database multiple values for same attribute - Best practices?

I have found myself that some attributes from my Person table, need to hold multiple values/choices, which is not a good sql practice so I created a second table, like this: Before: Person table -ID ...

How to script copy and paste for a column with different values every few rows?

I have the table below in Link. Link To table On MS SQL How to copy and paste apples down the column until it hits banana and then copy banana down the column? Same with the years, I want to copy ...

Select rows for a date range applied on one datetime column

In the below table, how to get what statuses the user was in between '2016-12-12 00:00:00.0' and '2017-02-20 23:59:59.999'. The result should be emailVerified and active userid status ...

Update using subquery and Case statements

I was writing a update query with some case statements. when case doesn't satisfy it will update the column with NULL. Following is the query : UPDATE TGT SET C1 = CASE WHEN TGT.c2 = SRC.c2 AND ...

Stored procedure creation [on hold]

I am trying to create a database which has information of assets. In that database, I want to create some id's in which actually describes the asset Example: for monitor: MN0001 for keyboard: ...

Get data for specific month from MS Access 2016 db

Good day, I am struggling with an sql select query from my java application. My application connects with an MS Access database. I would like to retrieve all records logged during a specific month. ...

Ambiguous code in SQL Server

Select DocNo From thecat24 a inner join Thecat23 x on a.N_Solicitud = x.N_Solicitud where x.Codigo_Proyecto = N'2' and DocNo <> 33 It says DocNo is ambiguous and i cant see why? Any fix?

relationship of 3 tables in laravel

How, I can have the list of the skills of each user knowing that it was 3 tables: user, competences and competences_user with query-builder user : id, nom.... competences: id, titre... ...

Check form before submit

The form/table filled with data based on the the status. So the rows in the table are build dynamically. What i want is that the person who wants to submit the form, has filled in the three fields (...

How to order by just one part of a group and alias?

I want to just order by the LastName while still having the alias be the full name. The group functions prevents me from running the code below, I can only run this code if I have "ORDER BY (Employees....

Alternative of hive join

I have two tables in my hive +------------+ | Table_1 | +------------+ | hash | | campaignId | +------------+ +-----------------+ | Table_2 | +-----------------+ | campaignId | ...

passing table contents from SQL to HTML through Python and Jinja

I have a table in a database on phpLiteAdmin working on SQL and I'm trying to get that table through Python and then sending it to render in HTML where I want to add these columns and rows into a ...

How to work around a key string and restriction string

I am wondering - in a closed-connection system, where keys are not checked online but rather on the offline system - is there a way to decipher the pattern with some previous key-strings at hand. ...

Oracle Table Based Authentication

Is it possible to authenticate a user to allow them to log into an application using a user table and not having to use the built in user management system? i.e. if I had a table like; How would I ...

how to insert smalldatetime value to data base column

i have one from user table like : Id int Unchecked Name varchar(50) Checked EmailAddress varchar(200) Unchecked Host varchar(250) Unchecked Port int Unchecked POPHost varchar(250) ...

Add additional check to WHERE statement

Can I add additional check to WHERE statement if some bool check is true. Something like this: SELECT * FROM Employee WHERE (CASE WHEN ID = 1 THEN EmployeeName = @Name) I know that this example does ...

How to take sum of all columns in hive

Is is possible to take sum of all columns in hive table. I mean any single way to take sum Table col col_1 col_2 col_3 Ouptut sum(col), sum(col_1), sum(col_2) sum(col_3)

Working with arrays and sets in hive

I have a table. One of its columns is of type array<string>. I try to run a query on this table and then load the data in a file. This is the query Select concat(key, '|' , code, '|' , ...

SQL Order by string, numbers appear last

I have a query that return a string column which I would like to sort by. When I sort by ASC/DESC, I'm getting the same result. Here is an output example: AVI ksky site Secure East Shlomi VSEStaging ...

how to know column existing date format? [on hold]

suppose in a column a date is like 12/03/1993, how do i know which is either in DD/MM/YYYY or MM/DD/YYYY format ?

PostgreSQL - Start a transaction in specific time

I search for a way to start a Function or Transaction in a specific time (in order to test concurrent access), but i don't find a clear way i find here PostgreSQL - make two transactions run at the ...

How to find the rest between date ranges that overlaps

I have the following mysql table (rooms) id_user_room username room_name date_from date_to 1 mike A1 2017-02-01 2017-02-10 2 evans ...

How to apply pivot to result of query

There is my current query: SELECT Name, Code, Today , Account || Currency as Accounts FROM ( SELECT b.description AS Name , b.contragentidentifycode AS Code , c....

(MySQL) JOIN a table with individual date AND another with date range

I am new to SQL and am trying to join 2 tables on MySQL: one with a date and another with date range. Table 1 contains: Service date (e.g. 2017-01-01) Vendor City Vendor ID Vendor Name Sales ...

Select values not included in table

A friend of mine is making a database on MS Access, and he encountered the following problem. On his database, he has a table called "bookings". This table keeps track of who has booked each sport ...

Save data from text file to SQL database and display it in a HTML table

I have a very simple script that collects data from a text file and displays it in a HTML table like this: Name Date Length 0086.se 2017-02-21 4 The text file is located on a ...

Divide COUNT Column By COUNT(DISTINCT(Column To give avg order size SQL SERVER

Ok I have a list of the months orders so that bit is easy. SELECT COUNT(*) AS ITEMS, For the next part easy to: COUNT(DISTINCT(PICKSET_NO))AS PICKSETS, Its the next part I cant work out: SUM(...

Subquery with more than 1 value error. Case with multiple columns

I'm, trying to create a query that has 3 columns: 1 column represents the list Job numbers (not nulls values), the 2° and 3rd column represents a sum of values respectively before and after a ...

SQL Server error - Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

I write this stored procedure ALTER PROCEDURE [dbo].[CERRARVIAJE] ( @ViajeID INT, @Reloj MONEY, @Peaje MONEY, @Equipaje MONEY, @Adicional MONEY, @Plus MONEY, @...

c# fill listview with data from sql

I have a winform app in c# which loads items from comboboxes to a listview based on relations between them. That part works fine. Now the simpler part I can't get to work, when I load the form I want ...

Return details of the latest bill linked to the user

I have two different sources of information and I am trying to marry them together to get information relating to a customer and their last bill. I have managed to get details of each customer and ...

XML column in SQL Server table - combine recurring tag name into one column

I have to query a XML data column in SQL Server and need some help with how I can query some of the individual tags inside the XML. Here is a toy data set that I have been using to test my query: &...

Index seek changes to index scan on change of value in where clause

I have a table ORDER having 64 columns, OrderNo being the Primary Key. It has two more columns ParentOrderNo and Type. For lone orders, Type and ParentOrderNo will be NULL and for grouped orders, Type ...

Import big table from MS Azure storage

I have a big table that I can access through the MS Azure storage explorer. For some reason, I am not able to export the whole table (the app closes automatically after some time). Is there any way to ...

Filtering one Table with information in another two different tables

In one table I have the open and close times of different groups of stores on each day. In another table I have different products names and in which stores they are sold. In a third table I have the ...
Translating... 0%