Here is a complex sub-query on how to write it using two tables explained. One is the customer table, and the other one is the order table.
Sub-queries are two types. Single row return and Multiple-row return. Below is the multiple-row return example that explains in detail.
Sub-query Question
A subquery should get everything from the Customer_Table if the customer has an order in the Order_Table of greater than $400.
Customer_Table
| CUstomer_number | Customer_name |
|---|---|
| 123 | Rao |
| 567 | Kyte |
| 897 | Robo |
| 901 | Tup |
| 991 | Vek |
Order_table
| Order_number | Customer_number | Order_total |
|---|---|---|
| 1001 | 123 | 100.76 |
| 1002 | 567 | 200 |
| 1003 | 897 | 310 |
| 1004 | 901 | 450 |
| 1005 | 991 | 566 |
Here is Sub-query
select *
from customer_table
where customer_number in
(select customer_number
from order_table
where order_total > 400);
How it works
The sub-query is inside the parenthesis. First, it gets the customer_number from the order_table (the order_total greater than 400).
The outer query gets all the details of customer_table for all the matching customer numbers.
Summary
I chose to use sub-query, which ultimately reduced complexity, and we got the desired result.
Related posts
Get new content delivered directly to your inbox.
How to Create a Generic Stored Procedure for KPI Calculation (SQL + AWS Lambda)
In modern data engineering, building scalable and reusable systems is essential. Writing separate SQL queries for every KPI quickly becomes messy and hard to maintain. A better approach?👉 Use a Generic Stored Procedure powered by Dynamic SQL, and trigger it using AWS Lambda. In this blog, you’ll learn: What is a Generic Stored Procedure? A…
Unlocking the Power of Databricks Genie: A Comprehensive Guide
Databricks Genie is a collaborative data engineering tool built on the Databricks Unified Analytics Platform, enhancing data analytics for businesses. Key features include collaborative workspaces, efficient data processing with Apache Spark, built-in machine learning capabilities, robust data visualization, seamless integration, and strong security measures, fostering informed decision-making.
Secure S3 File Upload Using API Gateway, Lambda & PostgreSQL (Complete AWS Architecture Guide
Modern applications often allow users to upload files—documents, invoices, images, or datasets. But a production-grade upload pipeline must be secure, scalable, and well-organized. In this article, we will build a complete end-to-end architecture where: We will implement this using Amazon API Gateway, AWS Lambda, PostgreSQL, and Amazon S3. This architecture is widely used in cloud-native…
Srinimf 