Page 1 of 1

[Solved] SELECT....NOT IN ()

Posted: Wed Jan 16, 2019 3:27 am
by dreamquartz
Hello All,

Can someone guide me in the right direction with the code below?
I need to have 2 criteria verified at the same time.

Code: Select all

SELECT
	 "vQuery8"."CompanyID",
	 "vQuery8"."CertificateQualificationID"
FROM
	 "vQuery8"
WHERE
	 "CompanyID" AND
	 "CertificateQualificationID"
		 NOT IN
			(
				 SELECT
					 *
				 FROM
					 "vQuery9"
			 )
Thanks in advance,

Dream

Re: SELECT....NOT IN ()

Posted: Wed Jan 16, 2019 3:36 am
by robleyd
Did you try

Code: Select all

    SELECT
        "vQuery8"."CompanyID",
        "vQuery8"."CertificateQualificationID"
    FROM
        "vQuery8"
    WHERE
        "CompanyID" NOT IN (SELECT  * FROM "vQuery9" )
       AND
        "CertificateQualificationID" NOT IN (SELECT  * FROM "vQuery9" )
I'd suspect that WHERE "CompanyID" would select where the value of CompanyId was TRUE, then AND with the next part of the WHERE clause.

Edit: and perhaps select the specific field you want to match in the NOT IN queries, rather than a wildcard.

Re: SELECT....NOT IN ()

Posted: Wed Jan 16, 2019 4:10 am
by FJCC
robleyd wrote: and perhaps select the specific field you want to match in the NOT IN queries, rather than a wildcard.
Yes, the sub query must return only one column.

Code: Select all

WHERE
        "CompanyID" NOT IN (SELECT  "CompanyID" FROM "vQuery9" )

Re: SELECT....NOT IN ()

Posted: Wed Jan 16, 2019 1:33 pm
by Villeroy

Code: Select all

SELECT
    "vQuery8"."CompanyID",
    "vQuery8"."CertificateQualificationID"
FROM "vQuery8" LEFT JOIN "vQuery9" 
    ON "vQuery8"."CompanyID"="vQuery9"."CompanyID"
        AND "vQuery8"."CertificateQualificationID"="vQuery9"."CertificateQualificationID"
WHERE "vQuery9"."CompanyID" IS NULL AND "vQuery9"."CertificateQualificationID" IS NULL
assuming that the corresponding column names in vQuery9 are the same as in vQuery8

Re: SELECT....NOT IN ()

Posted: Fri Jan 18, 2019 7:21 pm
by dreamquartz
Villeroy wrote:

Code: Select all

SELECT
    "vQuery8"."CompanyID",
    "vQuery8"."CertificateQualificationID"
FROM "vQuery8" LEFT JOIN "vQuery9" 
    ON "vQuery8"."CompanyID"="vQuery9"."CompanyID"
        AND "vQuery8"."CertificateQualificationID"="vQuery9"."CertificateQualificationID"
WHERE "vQuery9"."CompanyID" IS NULL AND "vQuery9"."CertificateQualificationID" IS NULL
assuming that the corresponding column names in vQuery9 are the same as in vQuery8
Yup,
This is the answer.

Tried everything with NOT IN ().
Never thought about LEFT JOIN.

Thank you so much,

Dream

Re: [SOLVED] SELECT....NOT IN ()

Posted: Fri Jan 18, 2019 8:16 pm
by Villeroy
NOT IN (SELECT...) should work as well, possibly a little bit slower. Your mistake was that you try to compare one field of one record set with all fields of another record set.

Code: Select all

SELECT columns
FROM A, B
WHERE A.X = B.Y
... is perfectly equivalent to ...

Code: Select all

SELECT columns
FROM A INNER JOIN B 
  ON A.X = B.Y
The INNER keyword is optional. A JOIN B means the same.

Code: Select all

SELECT columns
FROM A LEFT OUTER JOIN B 
  ON A.X = B.Y
... returns the same records as the inner join plus all the non-matching records of the left table with Null values for the columns of the right table.
The OUTER keyword is optional. A LEFT JOIN B means the same.

Code: Select all

SELECT columns
FROM A RIGHT OUTER JOIN B 
  ON A.X = B.Y
... returns the same records as the inner join plus all the non-matching records of the right table with Null values for the columns of the left table.
The OUTER keyword is optional. A RIGHT JOIN B means the same.

The following is not supported by HSQL 1.8

Code: Select all

SELECT columns
FROM A OUTER JOIN B 
  ON A.X = B.Y
It should return the same records as the inner join plus all the non-matching records of the right table with Null values for the columns of the right table plus all the non-matching records of the left table with Null values for the columns of the left table.

With HSQL 1.8 you get an OUTER JOIN from this:

Code: Select all

SELECT columns
FROM A LEFT OUTER JOIN B 
  ON A.X = B.Y
UNION SELECT columns
FROM A RIGHT OUTER JOIN B 
  ON A.X = B.Y
Of course you knew that already.

P.S.: Tutorial with attachment: viewtopic.php?f=83&t=96654

Re: [SOLVED] SELECT....NOT IN ()

Posted: Sat Jan 19, 2019 7:08 am
by dreamquartz
Villeroy wrote:NOT IN (SELECT...) should work as well, possibly a little bit slower. Your mistake was that you try to compare one field of one record set with all fields of another record set.

Code: Select all

SELECT columns
FROM A, B
WHERE A.X = B.Y
... is perfectly equivalent to ...

Code: Select all

SELECT columns
FROM A INNER JOIN B 
  ON A.X = B.Y
The INNER keyword is optional. A JOIN B means the same.

Code: Select all

SELECT columns
FROM A LEFT OUTER JOIN B 
  ON A.X = B.Y
... returns the same records as the inner join plus all the non-matching records of the left table with Null values for the columns of the right table.
The OUTER keyword is optional. A LEFT JOIN B means the same.

Code: Select all

SELECT columns
FROM A RIGHT OUTER JOIN B 
  ON A.X = B.Y
... returns the same records as the inner join plus all the non-matching records of the right table with Null values for the columns of the left table.
The OUTER keyword is optional. A RIGHT JOIN B means the same.

The following is not supported by HSQL 1.8

Code: Select all

SELECT columns
FROM A OUTER JOIN B 
  ON A.X = B.Y
It should return the same records as the inner join plus all the non-matching records of the right table with Null values for the columns of the right table plus all the non-matching records of the left table with Null values for the columns of the left table.

With HSQL 1.8 you get an OUTER JOIN from this:

Code: Select all

SELECT columns
FROM A LEFT OUTER JOIN B 
  ON A.X = B.Y
UNION SELECT columns
FROM A RIGHT OUTER JOIN B 
  ON A.X = B.Y
Of course you knew that already.

P.S.: Tutorial with attachment: viewtopic.php?f=83&t=96654
Thank you Master Villeroy