I’m working on a little SQL exercise, and am scratching my head an this problem. I am trying to find all the Employees to whom no other employee reports to. This is what the employees table looks …
Tag: join
SQL Server: table join based on record-dependent values
There’s a general type of query I’m trying to perform, and I’m not sure how to express it in words so that I can find a discussion of best practices and examples for executing it. Here’s an example …
Query to Id referencing to an ‘Id’ to the same table
I have a table AMZ_EMPLOYEE_DETAILS with Employee Id, Employee Name and Supervisor1 and Supervisor 2. The Supervisors are employee itself but represented by their name. My task was to replace the Supervisor names with their Ids. I have used the following query to obtain the solution but it uses sub-queries an…
SQL – joining 3 tables and choosing newest logged entry per id
I got rather complicated riddle to solve. So far I’m unlocky. I got 3 tables which I need to join to get the result. Most important is that I need highest h_id per p_id. h_id is uniqe entry in …
Tuning a query converting subquery
I was trying to change an update statement where there is a subquery in the WHERE clause to a join in order to improve performance. DDL to sample table/data CREATE TABLE [dbo].[Table1]( [pre_plan_id]…
Unknown column in field list when use join MySQL
I have 2 tables like this: product_master: campaign_product: This tables link with foreign key product_id I want to get all product data with campaign_code column. I don’t want to get 2 campaign_product row with same product_id. So I use this query: It got error when I run: Error in query (1054): Unknow…
Merge Multiple Queries Into 1 Query – On the same Row
I have 1 table called itemmovement : It has Item Id , Quantity In , Quantity Out , Invoice Id, Date. I need to make in one query to show how many pieces are sold and beside the sold column there will …
Query works on SQLite but fails on SQL Server
The following query works for me in SQLite. But in SQL Server, it gives me an error. Invalid column name ‘VehId’. And the editor puts a red squiggly line under the last VehId (but not the first). As you might expect, Vehicles.Id is the primary key. I’m trying to find all foreign keys in Tran…
Add nested column to BigQuery table, joining on value of another nested column in standard SQL
I have a reasonably complex dataset being pulled into BigQuery table via an Airflow DAG which cannot easily be adjusted. This job pulls data into a table with this format: | Line_item_id | Device …
SQL perform AVG after MAX
I have two tables. Table1: | ID1 | ID2 | ID3 | ID4 | |—–+—–+—–+—–| | 200 | 125 | 300 | 201 | | 206 | 128 | 650 | 261 | | 230 | 543 | 989 | 403 | and Table2: | ID1 | ID2 | ID3 | ID4 | …