I am trying to create a recursive query that relies on a LEFT JOIN condition, but I am not sure if it is possible, especially in Snowflake. I have three tables: ITEM, ITEMHIERARCHY, and ITEMVALUE My goal is to return a list of all ITEMs with values and sub-item values rolled-up: Note that even though Item6 is a roll-up from
Tag: left-join
Left join only on first row
I have the following sample query: and I’d like the following output I read many questions on the similar topic but I wasn’t able to make it work. Answer You can use row_number():
How to apply dense rank/ partition to animal table? SQL Server
Table: Okay so in this table I want the groups ranked 1, 2, 3. Because if animal height width footlength and brand is the same give the rows the same ranking. The way I have coded it in SQL I keep getting the same rank for each row. vs 3 different ranks. Any tips on how to better understand dense
Left outer join is working with SQLite while is not with MySQL
I have a table with products, orders, prices, date of sell. I need to create a table with the sells for a product in a single day. I used sqlite and this code worked just fine: /* Groups order date &…
Extra null rows when using LEFT OUTER JOIN
I have three tables SpringCount, SpringObservation, and MonitoringPoint. SpringCount and SpringObservation particpate in a one to many relationship. Some counts have no bird observations, some counts do. I want to return a table with all the counts represented and nulls in the appropriate fields when they don’t have a matching observation. I understand when doing a left outer join this
How to join two tables in sql according to a determined value?
I have the following tables and I have to get all orders of customer Meier. Table Customer with columns cid and name: cid name ————————- 13 M. Mueller 17 …
Replacing null based on a condition
I am having a table with many columns (but posting only col1, col2, col3 here for simplified post): I want to fill null values of source _id with values from id. For example, source_id a5 row has null which has to replaced with id a1 values, subsequently, source_id a6 row having null to be replaced with a5 row Output: Answer
MYSQL getting data from three tables
I am trying to find the drinker and total amount of money spent on drinks for all drinkers in February 2020. I also need to include drinkers who haven’t ordered a drink in this period. Here are the three tables: Here is my statement so far: I am very new to SQL, I know that there are a number of
MySQL: Counting active products that belong to active categories (and optionally to active subcategories)
i’m using MySQL 5.6. I need to get a list of categories, with a product count column. So far, a very easy and trivial task. Here’s where it gets a little complicated: A product has a category, and can also have a subcategory (optionally). Categories, subcategories and products can be active (flg_public=1) or disabled (flg_public=NULL). So, the query should return
SQL – Join with keys separated by pipe
TABLE_A TABLE_B How can I join table A and B to get all those who helped in the cargo? Will I need to write a function? I did not develop the program. I’m creating a dashboard and need that info. Using ORACLE 11g DB CONCLUSION — SOLVED After a bit of research and help of friends I was able to