SQL join condition either A or B but not both A and B
one or the other (but not both) sqlzoo
sql where multiple conditions
sql not in multiple conditions
two conditions in inner join
sql exclude multiple conditions
oracle join syntax
I have sales data by years and quarters and for the last year I want to fill missing quarters with last available value.
Say we have source table:
+------+---------+-------+--------+ | year | quarter | sales | row_no | +------+---------+-------+--------+ | 2018 | 1 | 4000 | 5 | | 2018 | 2 | 6000 | 4 | | 2018 | 3 | 5000 | 3 | | 2018 | 4 | 3000 | 2 | | 2019 | 1 | 8000 | 1 | +------+---------+-------+--------+
+------+---------+-------+------------------------+ | year | quarter | sales | | +------+---------+-------+------------------------+ | 2018 | 1 | 4000 | | | 2018 | 2 | 6000 | | | 2018 | 3 | 5000 | | | 2018 | 4 | 3000 | | | 2019 | 1 | 8000 | | | 2019 | 2 | 8000 | <repeat the last value | | 2019 | 3 | 8000 | <repeat the last value | | 2019 | 4 | 8000 | <repeat the last value | +------+---------+-------+------------------------+
So the task is to make Cartesian of year and quarter and left join to it the sales either corresponding or the last.
This code gets me almost there:
select r.year, k.quarter, t.sales from (select distinct year from [MyTable]) r cross join (select distinct quarter from [MyTable]) k left join [MyTable] t on (r.year = t.year and k.quarter=t.quarter) or row_no=1
How to correct the last line (condition of join) so thet the 2018 is not doubled?
One method uses outer apply:
select y.year, q.quarter, t.sales from (select distinct year from [MyTable]) y cross join (select distinct quarter from [MyTable]) q outer apply (select top (1) t.* from [MyTable] t where t.year < y.year or (t.year = y.year and t.quarter <= q.quarter) order by t.year desc, t.quarter desc ) t;
For your volume of data, this should be fine.
A more efficient method -- assuming you are only assigning values to the end -- would be:
select y.year, q.quarter, coalesce(t.sales, tdefault.sales) from (select distinct year from [MyTable]) y cross join (select distinct quarter from [MyTable]) q left join [MyTable] t on t.year = y.year and t.quarter = q.quarter cross join (select top (1) t.* from [MyTable] t order by t.year desc, t.quarter desc ) tdefault
mysql - SQL Condition to get "a" or "b" but not both, SQL Condition to get “a” or “b” but not both I am new to sql queries. FROM MOVIE m JOIN ON (n.movie_title=m.movie_title AND n.release_year=m. release_year) JOIN on XOR : Returns TRUE if either component condition is TRUE . AND connects two conditions and returns true only if both conditions are true. Table 4.3 shows the possible outcomes when you combine two conditions with AND . The table’s left column shows the truth values of the first condition, the top row shows the truth values of the second condition, and each intersection shows the AND outcome.
A very different approach using a CTE and some window functions. This doesn't require 2 scans of the table, nor a triangular join.
WITH VTE AS( SELECT * FROM (VALUES (2018,1,4000,5), (2018,2,6000,4), (2018,3,5000,3), (2018,4,3000,2), (2019,1,8000,1)) V([Year],[Quarter],sales, row_no)), CTE AS( SELECT Y.Year, Q.Quarter, V.sales, V.row_no, COUNT(CASE WHEN V.sales IS NOT NULL THEN 1 END) OVER (ORDER BY Y.[Year], Q.[Quarter] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Grp FROM (VALUES(2018),(2019)) Y([Year]) CROSS JOIN (VALUES(1),(2),(3),(4)) Q([Quarter]) LEFT JOIN VTE V ON Y.[Year] = V.[Year] AND Q.[Quarter] = V.[Quarter]) SELECT C.[Year], C.[Quarter], MAX(C.sales) OVER (PARTITION BY C.Grp) AS Sales FROM CTE C;
This will only work on SQL Server 2012+ (as
ROWS BETWEEN was introduced with SQL Server 2012), however, hopefully you're not using 2008- which are all (almost) entirely out of support.
SQL select condition Either A or B but not both, Thread: SQL select condition Either A or B but not both. share-icon Member. Join Date: Jul 2005; Location: Tokyo Japan; Posts: 34� Select all records from Table B, along with records from Table A for which the join condition is met (if at all). Select all records from Table A and Table B, regardless of whether the join condition is met or not. Examples of SQL Join Types
I would simply do
SELECT TT.YEAR, TT.Quarter, COALESCE(T.SALES, MAX(T.SALES) OVER (PARTITION BY TT.YEAR)) AS sales FROM (SELECT DISTINCT T.YEAR, TT.Quarter FROM [MyTable] T CROSS JOIN ( SELECT DISTINCT TT.Quarter FROM [MyTable] TT ) TT ) TT LEFT JOIN [MyTable] T ON TT.YEAR = T.YEAR AND TT.Quarter = T.Quarter;
EDIT : I just misread the question for additional
quarters so, you need
OUTER JOIN :
SELECT TT.YEAR, TT.Quarter, COALESCE(T.SALES, T1.SALES) AS Sales FROM (SELECT DISTINCT T.YEAR, TT.Quarter FROM [MyTable] T CROSS JOIN ( SELECT DISTINCT TT.Quarter FROM [MyTable] TT ) TT ) TT LEFT JOIN [MyTable] T ON TT.YEAR = T.YEAR AND TT.Quarter = T.Quarter OUTER APPLY ( SELECT TOP (1) T.* FROM [MyTable] T WHERE T.YEAR = TT.YEAR ORDER BY T.Quarter DESC ) T1;
Database Principles and Design, This is equivalent to using the ( + ) notation on the left of the join condition. Branch B WHERE E.BranchCode(+) = B.BranchCode SQL> SELECT Name, BranchName join but uses either the NATURAL or the INNER keyword, but not both. Just to clarify why I have posted after other answers is that doing the union in a subquery reduces the number of table scans on products and will made the query more efficient. In this there are 3 table scans and 1 join compared to the other solutions which require 4 tables scans (products twice) and 2 joins. – GarethD Jan 27 '12 at 10:50
Sql Server 2005 Bible, O joins are often combined with multiple condition joins involving non - key two data sets , it makes sense that multiple conditions are possible at the join . B . col JOIN C ON B . col = C . col AND A . col = C . col Non - Key Joins Joins are not Either way , the query - optimization plan is identical , so use the method that is � The SQL AND, OR and NOT Operators. The WHERE clause can be combined with AND, OR, and NOT operators. The AND and OR operators are used to filter records based on more than one condition: The AND operator displays a record if all the conditions separated by AND are TRUE.
A step-by-step walkthrough of SQL Inner Join, There are four basic types of Joins in SQL Server – Inner, Outer (left, right, full) Inner Join clause in SQL Server creates a new table (not physical) by Assume, we have two tables, Table A and Table B, that we would like to join using SQL operator '=' either in the Join clause or in the WHERE condition. conditions should be satisfied: display a record when both are present. display a record when only mailid is present. display a record when only phonenumber is present. expected output: single record (as userid is unique) with userid, phonenumber and mailid . both or any one if present. i have tried the query as :
Joins, Database SQL Reference Most join queries contain at least one join condition, either in the FROM clause or in the WHERE clause. If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. Oracle If A and B are joined by multiple join conditions, then you must use the (+)� If select * from Employee where (design= 'SE' and dt = 20070702) = A gives rows = 0 then below condition should execute select * from Employee where (design= 'SE' and = 20070629) = B If A has rows then A exit end if A has no rows then B exit end Either A or B but not both. I am using SYBASE Please suggest me how to write a query for above condition.
- Are you sure that the fact that Sales in last period is accidentally highest does not influence your query?
- Say time passed, and we have additional quarter in source table. What if 2019 Q2 Sales=7000 (so less then 2019 Q1). Then MAX partition by year would produce 8000 (value for 2019 Q1) which is not what I want. I want last available value which in that case would be 2019 Q2.
- @PrzemyslawRemin. . . Correct i misread the question forget the scenario that you had. Thank you.