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

Discuss the database features

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

Postby dreamquartz » Wed Jan 16, 2019 3:27 am

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   Expand viewCollapse view
SELECT
    "vQuery8"."CompanyID",
    "vQuery8"."CertificateQualificationID"
FROM
    "vQuery8"
WHERE
    "CompanyID" AND
    "CertificateQualificationID"
       NOT IN
         (
             SELECT
                *
             FROM
                "vQuery9"
          )


Thanks in advance,

Dream
Last edited by dreamquartz on Fri Jan 18, 2019 7:21 pm, edited 2 times in total.
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 710
Joined: Mon May 30, 2011 4:02 am

Re: SELECT....NOT IN ()

Postby robleyd » Wed Jan 16, 2019 3:36 am

Did you try
Code: Select all   Expand viewCollapse view
    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.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2954
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: SELECT....NOT IN ()

Postby FJCC » Wed Jan 16, 2019 4:10 am

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   Expand viewCollapse view
WHERE
        "CompanyID" NOT IN (SELECT  "CompanyID" FROM "vQuery9" )
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7266
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: SELECT....NOT IN ()

Postby Villeroy » Wed Jan 16, 2019 1:33 pm

Code: Select all   Expand viewCollapse view
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SELECT....NOT IN ()

Postby dreamquartz » Fri Jan 18, 2019 7:21 pm

Villeroy wrote:
Code: Select all   Expand viewCollapse view
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
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 710
Joined: Mon May 30, 2011 4:02 am

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

Postby Villeroy » Fri Jan 18, 2019 8:16 pm

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   Expand viewCollapse view
SELECT columns
FROM A, B
WHERE A.X = B.Y

... is perfectly equivalent to ...
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27114
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby dreamquartz » Sat Jan 19, 2019 7:08 am

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   Expand viewCollapse view
SELECT columns
FROM A, B
WHERE A.X = B.Y

... is perfectly equivalent to ...
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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
LO 6.x and OO 4.x on Windows 7 PRO & Ubuntu 18.04 LTS.
dreamquartz
 
Posts: 710
Joined: Mon May 30, 2011 4:02 am


Return to Base

Who is online

Users browsing this forum: No registered users and 1 guest