I’ve got a situation as follows (note this setup has been created as a minimal reproducible example). I have a table of houses, created like this: I have a large table called house_listings, like this: So, let’s say I run the following query: and I get the following output: and then I run the foll…
Tag: sql
MySQL query to Retrieve Main record from main table and Sub records from another table in a single SQL statement
I have following Two tables Table Name: category Table Name: product Now I am looking for single SQL statement which should retrieve everything in a single sql statement. Expected output given below: Answer You can use union all:
How to convert XML type return text into select columns
I’m trying to get the column names of a table using XML datatype and information_schema columns. When I tried to use the result in another select statement, I have the results with the repeated column name instead of the results set. I have even tried to cast it to varchar but it still failed. what have…
ORA-00906: missing left parenthesis – LIES?
I have stared at this until my eyeballs bleed, where am I missing a parenthesis? It does also say Error starting at line: 1 in command-“. The cause and action section of the error report is blank. Answer This just because you missed column name while declaring primary key. There is another problem: cons…
Why Row_Number in a view gives a nullable column
I have a view using a CTE and I want use a row number to simulate a key for my edmx in Visual Studio When I look in properties of column I see Id bigint … NULL And my edmx exclude this view cause don’t find a column can be used to key When I execute my view ID have
How to return data for specific day names from date column
Imagine I have a SQL Server table as shown below, named dbo.tblNAV: How can I select the data only for Mondays and Fridays? I am a beginner in SQL Server and almost have no idea to handle this. I would appreciate your help. I am using Microsoft SQL Server Management Studio. Answer SQL Server does not make thi…
How to calculate rows count in where statement in sql?
I have two tables in SQL Server: order (columns: order_id, payment_id) payment (columns: payment_id, is_pay) I want to get all orders with two more properties: How many rows where is_pay is 1: And the count of the rows (without the first filter) So I wrote this query: The problem is how to calculate the rows …
TSQL to perform aggregation with Exists
My sample data source is following I want SQL to check if there exists any row with cond=X or cond=Y by site and if yes, don’t perform any aggregation; if no, perform a sum of val. I am currently achieving it by following Method1 Method 2 I was wondering if there is any better way of achieving this. Edi…
Compare the triplet rows together and select max of them?
Consider the following time series data These are the values of y, and the x column starts from 1 respectively. I have come to the conclusion that if the data in the y-column are compared three by three, the maximum value in each triad, is the peak point. Does anyone have an idea how to specify peak points wi…
SQL- Retrieve records based on column’s specific value
I have 2 tables with below info Table# 1 – TaskDependecy TaskID DependentTaskID 1 2 1 3 3 6 3 7 5 8 Table # 2 – TaskStatus TaskID Task Status 1 In Progress 2 Completed 3 In Progress 5 In Progress 6 Completed 7 Completed 8 Completed I want to retrieve only taskIDs # 3 and 5 so that