Link to home
Start Free TrialLog in
Avatar of SALA DONATELLA
SALA DONATELLAFlag for Italy

asked on

SQL Query

Hello Expert. I have this query:

SELECT        *
FROM            SalesOrderTemp AS t1
WHERE        (SONo IN
                             (SELECT        SONo
                               FROM            SalesOrderDetail AS t2)) AND (SerialNo IN
                             (SELECT        SerialNo
                               FROM            SalesOrderDetail AS t2)) AND (EventNo IN
                             (SELECT        EventNo
                               FROM            SalesOrderDetail AS t2)) or (EventNo IS NULL)

The result doesn't include the records having EventNo = NULL.
Where I'm am wrong?
Any help would be appreciated.
Thank you.
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try this instead:

SELECT *
FROM SalesOrderTemp AS t1
WHERE (
		(
			SONo IN (
				SELECT SONo
				FROM SalesOrderDetail AS t2
				)
			)
		AND (
			SerialNo IN (
				SELECT SerialNo
				FROM SalesOrderDetail AS t2
				)
			)
		AND (
			EventNo IN (
				SELECT EventNo
				FROM SalesOrderDetail AS t2
				)
			)
		)
	OR (EventNo IS NULL)

Open in new window

not sure why you would write IN clauses where there is no WHERE clause.

What are you really trying to achieve?
Avatar of SALA DONATELLA

ASKER

I want to achieve all records in t1 having the same SoNo, EventNo, ans SerialNo in t2 including  the t1.EventNo = NULL.
Does it make sense?

Thank you in advance for your help.
To start with, try the following.  But I'll need to know more about what database system (SQL Server, Oracle, MySQL, Access, ...) you are using to refine this.
Also, if any of the fields SoNo, SerialNo, or EventNo in T2 are NULL do you want to include those records?

SELECT * FROM SalesOrderTemp AS t1
LEFT JOIN (
SELECT DISTINCT SONo, SerialNo, EventNo
FROM SalesOrderDetail)
AS t2 on T1.SONo = T2.SONo AND T1.SerialNo = T2.SerialNo AND T1.EventNo = T2.EventNo
WHERE T2.ID IS NOT NULL
OR T1.EventNo is NULL

Open in new window

This will probably return some records you don't want, so we might need to refine the Join or add a WHERE clause
I think Dale's suggestion is good and can be rewritten to using Inner join instead, since the original query using the "AND" conditions.

SELECT * FROM SalesOrderTemp AS t1
INNER JOIN (
SELECT DISTINCT SONo, SerialNo, EventNo
FROM SalesOrderDetail)
AS t2 on T1.SONo = T2.SONo AND T1.SerialNo = T2.SerialNo AND T1.EventNo = T2.EventNo
WHERE  T1.EventNo is NULL

Open in new window

I'm using SQL Server 2017.
Yes, if any of the fields SoNo, SerialNo, or EventNo in T2 are NULL I want to include those records.
if any of the fields SoNo, SerialNo, or EventNo in T2 are NULL I want to include those records.

then I think a simple LEFT JOIN without the Where condition is what you needed.

like:

SELECT * FROM SalesOrderTemp AS t1
LEFT JOIN (
SELECT DISTINCT SONo, SerialNo, EventNo
FROM SalesOrderDetail)
AS t2 on T1.SONo = T2.SONo AND T1.SerialNo = T2.SerialNo AND T1.EventNo = T2.EventNo
WHERE  T1.EventNo is NULL

pls provide sample data to illustrate the issue when it's necessary.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks everybody for your help in this matter!