I want to use with clause in order to shorten column names which will be used for calculations whenever I add with clause block of code to my SQL query it gives error: a column may not be outer-joined to a subquery, I’m not even using outer join, what’s the issue? Answer Didn’t test anything…
Tag: sql
SQL request in which the data of one field as a result is grouped in different columns
An example of a table of orders Is it possible to get the amount of orders grouped by dates without the use of subqueries, while separately the pickup amount and delivery amount? I’m not sure that it is possible Answer you can wrap you condition into aggregation for mysql syntax it can be as follows:
Postgresql find by two columns
I have join table tags_videos How can I select distinct video_id who have two specific tag_id For example my tag_ids is 1195 and 1198, i should get video_ids 15033 and 15036 (who have 1195 and 1198 tag_id) Answer Extract the unique (tag_id, video_id) pairs for the two tags in t CTE and select these video_id&#…
DBeaver adds single character when auto-completing a table name (for MariaDB databases at least)
DBeaver adds a character (the first letter of the table name) when auto completing table names, resulting in a query that looks like this: As far as i can tell, it works, and has no impact on the query/results… can anyone enlighten me as to what it (the c) is? Answer “C” is an alias name of …
PostgreSQL – I have a Syntax error in my SUBSTRING query
I’m trying to make use of the SUBSTRING() function to extract a substring from vm.location_path, starting at the second character and ending at the position of the ‘]’ character, minus two. I want to extract the text between the square brackets ([]) in vm.location_path but I’m hitting …
How do I correctly map letters in the database?
I have two tables. One table with the letters of different countries and a second table with a mapping of these letters to each other. I need to make a query to get the mapped letters of the two languages. Can you tell me how this can be done optimally? The letter table id letter language 1 A en 2
SQL – Finding Duplicate Records based certain criteria
I have these records in the table – employee_projects id employee_id project_id status 1 emp1 proj1 VERIFIED 2 emp2 proj2 REJECTED 3 emp1 proj1 VERIFIED 4 emp1 proj3 REJECTED 5 emp2 proj2 REQUIRED 6 emp3 proj4 SUBMITTED 7 emp4 proj5 VERIFIED 8 emp4 proj6 VERIFIED 9 emp3 proj4 REQUIRED Here are the crite…
Two foreign keys referring to one primary key on SQL SERVER query select items
I’d to create a database that records transactions between two users. A user can transfer points (think of it as money) to another user. user table looks like: And the transaction table should look like: just i needed this result: Could someone give hints to provide SQL Server code? Select from_user, to…
What is a runtime complexity of this sql query?
I would like to know how fast is SELECT * FROM user_table WHERE email = ‘test@gmail.com’ is this O(1) or O(n)? how does sql search for a particular row? Answer If there is no index on “email” column, the search complexity is O(N). If there was hash-based index on an “email”…
joining two columns sql query
world! I’m currently stuck on this problem where i want to join two columns and run the select statement of the two, but i’m getting errors; these are the columns i want to join: and where in the second, for every row, i count how many equal rows i have for each value. the two columns values: firs…