How to obtain two values from a sub-query to be used in the where clause of sql
multiple column subquery in (select clause)
subquery with multiple columns in where clause
subquery returning multiple values in sql
sql subquery multiple tables
subquery in where clause
subquery in select statement
nested queries in sql w3schools
I have to use two values in the where clause to be tested for equality of two values obtained from a subquery. Since I am working on an existing application, I want to keep it as a subquery. The following is my query.
SELECT o.EMAIL_ADDRESS, c.FIRST_NAME, p.PARTY_ID FROM ORDER o WITH (NOLOCK) INNER JOIN PARTY p WITH (NOLOCK) ON o.ORDER_ID = p.PARTY_ID INNER JOIN CUSTOMER c WITH (NOLOCK) ON p.PARTY_ID = c.CUSTOMER_ID WHERE (o.EMAIL_ADDRESS, c.CUSTOMER_ID) IN (SELECT EMAIL_ADDRESS, CUSTOMER_ID FROM CUSTOMER_MASTER WHERE insert_date > '01/02/2019')
The problem I am facing is that the first value within the where clause, o.EMAIL_ADDRESS, throws the following error:
An expression of non-boolean type specified in a context where a condition is expected
When I use a single value within the where clause it works fine.
One method is with
EXISTS and a correlated subquery.
SELECT o.EMAIL_ADDRESS, c.FIRST_NAME, p.PARTY_ID FROM ORDER_HEADER oh WITH (NOLOCK) INNER JOIN PARTY p WITH (NOLOCK) ON o.ORDER_ID = p.PARTY_ID INNER JOIN CUSTOMER c WITH (NOLOCK) ON p.PARTY_ID = c.CUSTOMER_ID WHERE EXISTS( SELECT 1 FROM CUSTOMER_MASTER AS cm WHERE cm.insert_date > '01/02/2019' AND o.EMAIL_ADDRESS = cm.EMAIL_ADDRESS AND c.CUSTOMER_ID = cm.c.CUSTOMER_ID );
How to obtain two values from a sub-query to be used in the where , One method is with EXISTS and a correlated subquery. SELECT o. EMAIL_ADDRESS, c.FIRST_NAME, p.PARTY_ID FROM ORDER_HEADER� I have to use two values in the where clause to be tested for equality of two values obtained from a subquery. Since I am working on an existing application, I want to keep it as a subquery. The following is my query.
in allows single column list only. So create single column.
SELECT o.EMAIL_ADDRESS, c.FIRST_NAME, p.PARTY_ID FROM ORDER o WITH (NOLOCK) INNER JOIN PARTY p WITH (NOLOCK) ON o.ORDER_ID = p.PARTY_ID INNER JOIN CUSTOMER c WITH (NOLOCK) ON p.PARTY_ID = c.CUSTOMER_ID WHERE o.EMAIL_ADDRESS + cast(c.CUSTOMER_ID as varchar) IN (select EMAIL_ADDRESS + cast(CUSTOMER_ID as varchar) from CUSTOMER_MASTER where insert_date > '01/02/2019')
SQL : Multiple Row and Column Subqueries, Multiple row subquery returns one or more rows to the outer SQL statement. IN operator is used to checking a value within a set of values. To get 'ord_num', ' ord_amount', 'ord_date', 'cust_code' and 'agent_code' from the table 'orders' with following conditions : Here is the complete SQL statement: The EXISTS condition is used in combination with a subquery. It returns TRUE whenever the subquery returns one or more values. In its simplest form the syntax for the EXISTS condition is. WHERE EXISTS (sub query) Suppose we need to return all sales orders written by salespeople with sales year to date greater than three million dollars.
EXISTS. I would also advise fixing the date format:
SELECT o.EMAIL_ADDRESS, c.FIRST_NAME, p.PARTY_ID FROM ORDER o JOIN PARTY p ON o.ORDER_ID = p.PARTY_ID JOIN CUSTOMER c ON p.PARTY_ID = c.CUSTOMER_ID WHERE EXISTS (SELECT 1 FROM CUSTOMER_MASTER cm WHERE cm.EMAIL_ADDRESS = o.EMAIL_ADDRESS AND cm.CUSTOMER_ID = c.CUSTOMER_ID AND cm.insert_date > '2019-02-01' );
Your date constants should be in the YYYYMMDD format.
Based on your logic, though, I don't think you need all the
SELECT o.EMAIL_ADDRESS, c.FIRST_NAME, o.ORDER_ID FROM ORDER o JOIN CUSTOMER c ON o.ORDER_ID = c.CUSTOMER_ID WHERE EXISTS (SELECT 1 FROM CUSTOMER_MASTER cm WHERE cm.EMAIL_ADDRESS = o.EMAIL_ADDRESS AND cm.CUSTOMER_ID = c.CUSTOMER_ID AND cm.insert_date > '2019-02-01' );
PARTY doesn't seem necessary, because the orders can align directly to the customers.
Creating a subquery to retrieve data from more than one table, A subquery in a DELETE statement cannot retrieve data from the same table in In addition, just as you use the IN keyword in place of multiple OR statements in a query, you can This SQL query contains a subquery that uses the ANY keyword. Use the IN keyword for this subquery because you need to find values from� Using a Subquery in the FROM clause. When subqueries are used in the FROM clause they act as a table that you can use to select columns and join to other tables. Because of this, some people argue they really aren’t subqueries, but derived tables. I like to think of derived tables as a special case of subqueries… subqueries used in the FROM
Writing Subqueries in SQL, This lesson of the SQL tutorial for data analysis covers using subqueries in SQL with Subquery basics; Using subqueries to aggregate in multiple stages you wanted to take the sums of several columns, then average all of those values, for your subquery output aren't as stringent as when you use the WHERE clause. If a subquery (inner query) returns a null value to the outer query, the outer query will not return any rows when using certain comparison operators in a WHERE clause. Type of Subqueries. Single row subquery : Returns zero or one row. Multiple row subquery : Returns one or more rows. Multiple column subqueries : Returns one or more columns
The Power of Subqueries, A subquery SELECT statement if executed independently of the T-SQL the second subquery calculates the number of days between the two different OrderDates. Here we used the first subquery to return a column value in the final result set. In the following example, we used a subquery to find the number of books a� C) Oracle subquery with comparison operators example. The subqueries that use comparison operators e..g, >, >=, <, <=, <>, = often include aggregate functions, because an aggregate function returns a single value that can be used for comparison in the WHERE clause of the outer query.
Subqueries in the WHERE Clause, Subqueries can be used in place of expressions only in the specific instances outlined in the descriptions of Predicates in SQL. The syntax of the subquery is� Example: replacing a subquery with a JOIN. Suppose you're an SQL data analyst working at EverRed. The owner of the company wants you to obtain the names of the farms where the company is producing more apples in the current year than in the previous year (2017).