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

[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

[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.

[FJCC]

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" )

[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

[dreamquartz]

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

[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

[dreamquartz]

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