I have db and I am trying to create table called "sales_invoice_grid" I am having bellow scenarios: error 1: while creating the table "sales_invoice_grid" manually, it gives the message that it already exists. error 2: When select rows from "sales_invoice_grid" it gives the message that, the table is not exist

I've been told that when checking for the existence of a row in a table, this will work: EXISTS(SELECT 1 FROM....) but that a "better" way in regards to speed performance of the query would be to use this: EXISTS(SELECT TOP(1) NULL FROM......) I've searched online for anything that references this comparison and the only information I can find says that this is a personal preference and there actually is no performance gain of one over the other. There's always the "case by case" aspect of a question like this, but in general, is there any performance gain from using the second implementation of EXISTS() over the first one?

We have some really strange behaviour on MySQL (8.0.29) that we can't explain. We have 2 tables + 1 link table in-between, illustrated by this schema : We run a query whereby we need to fetch the id and organization_id from table1 for all the records that have a linked record in table2. However, if there are 2 linked records in table2, we still only want a single record from table1. We use Doctrine (PHP) integrated in a complex application so the query is generated for us. The resulting query is :

sql
SELECT
  table1.organization_id,
  table1.id AS id_0
FROM
  table1
  LEFT JOIN table2 ON (
    EXISTS (
      SELECT
        1
      FROM
        link1
      WHERE
        link1.table2_id = table2.uuid
        AND link1.table1_id IN (table1.id)
    )
  )
WHERE
  table1.location_id = 605
  AND table1.status IN ('confirmed')
  and table1.organization_id=1
ORDER BY table1.id DESC
LIMIT
  1000

This query is supposed to return 260 rows, but it returns only 1. Unless we just restarted MySQL, then it returns 0 and will continue to return 0 until we remove either the *LIMIT* clause or the *ORDER BY* clause. On MariaDB it gets worse : the query just uses CPU and we killed it after a few minutes. If you want to give it a go, the data dump is at https://gist.github.com/wimg/c8af87bd30b036c4de5e386e095f6416 Tried it on MySQL 8.0.29 (currently the most recent version). Anyone have any idea what's going on here ?

Below is an example of my pricing_supports table with more than 3 millions rows. There some rows having same values in the first_origin_id, delivery_id fields. If there are records with same first_origin_id, delivery_id but different source, I only want to select those records where source = 0 ID code first_origin_id delivery_id source 1 A 10 20 0 2 B 10 20 1 3 C 11 21 1 4 D 12 22 0 5 E 12 22 1 I would like result like that: ID code first_origin_id delivery_id source 1 A 10 20 0 3 C 11 21 1 4 D 12 22 0 How can I do for good performance?

Given the following database:

CREATE TABLE "Activity"
(
    "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    "designation" TEXT NOT NULL
);

CREATE TABLE "UserType"
(
    "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    "designation" TEXT NOT NULL
);

CREATE TABLE "User"
(
    "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    "name" TEXT NOT NULL,
	"typeId" INTEGER NOT NULL REFERENCES "UserType"("id")
	-- ... and other fields
);

CREATE TABLE "Activity_UserType"
(
    "id" INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY,
    "activityId" INTEGER NOT NULL REFERENCES "Activity"("id"),
    "userTypeId" INTEGER NOT NULL REFERENCES "UserType"("id")
);

How to write the following query: Select users that have access to a given activity. There is a a caveat, if no rows are present/linked in Activity_UserType to this activity then it means all UserTypes are allowed to access the activity. I tried the following query:

SELECT "User"."id", "User"."name", "User"."typeId"
FROM "User"
JOIN "UserType" ON "User"."typeId" = "UserType"."id"
JOIN "Activity_UserType" ON "UserType"."id" = "Activity_UserType"."userTypeId"
WHERE "Activity_UserType"."activityId" = 1;

However, this only works if the table "Activity_UserType" is not empty. I tried to apply a ´LEFT JOIN` but also without success. Here is a dbfiddle with comments (expected outputs): https://www.db-fiddle.com/f/nGHMWKw3ytYrCXePkzvizq/6

In the past, there was a lot of paranoia around IN. In particular, I remember claims that it can be optimised differently to an equivalent WHERE EXISTS. Is this claim still true? Or is it now guaranteed that IN and WHERE EXISTS will always give the same execution plans in cases where the two queries are logically equivalent? I am talking about queries of the following form

SELECT [...] FROM [FOO] WHERE [FOO_ID] IN (SELECT [FOO_ID] FROM [BAR])
-- Is logically equivalent to...
SELECT [...] FROM [FOO] WHERE EXISTS (SELECT 1 FROM [BAR] WHERE [BAR].[FOO_ID] = [FOO].[FOO_ID])

Not that I am not talking about NOT IN, NOT EXISTS, or using multiple IN clauses.

Recently one of our RDS Postgres databases was upgraded to version 11.19. Since then, we're receiving errors when running a database migration. I can't replicate this issue locally, it appears to be restricted to the RDS instance only. Running this query on an empty database:

ALTER TABLE IF EXISTS ONLY public.telescope_entries_tags 
   DROP CONSTRAINT IF EXISTS telescope_entries_tags_entry_uuid_foreign;

results in the following error on RDS:

Query 1 ERROR: ERROR:  relation "public.telescope_entries_tags" does not exist

However, when running this on a local database (also Postgres 11.19) I get the following response:

NOTICE:  relation "telescope_entries_tags" does not exist, skipping

Query 1 OK: ALTER TABLE

Note, this code was auto generated inside a pg_restore command, wrapped inside a laravel function, wrapped inside some CI/CD functionality. This is the simplest case I can boil it down to. The code was generated using pg_restore (PostgreSQL) 13.11. I've checked through the differences in all the settings that are shown when running "SHOW ALL;", and copied whatever I could to the local instance, and still can't get this code to show an error locally. I just don't understand why this is classified as an error on RDS. To my mind it should be a notice. Any advice would be appreciated.

Is there a best practice between using a LEFT JOIN or a NOT EXISTS format? What is benefit to using one over the other? If none, which should be preferred? SELECT * FROM tableA A LEFT JOIN tableB B ON A.idx = B.idx WHERE B.idx IS NULL ---------- SELECT * FROM tableA A WHERE NOT EXISTS (SELECT idx FROM tableB B WHERE B.idx = A.idx) I am using queries within Access against a SQL Server database.

Say I have below table | | | | | | | | | |-|-|-|-|-|-|-|-| | cis|aid|cid|apid|ntime|buid|flg|jid| |30|1208|229067737|1026|9|DDDD400C|0|0| |30|1209|229067737|1026|11|DDDD400C|0|0| |30| 0|229067737|1026|12|DDDD400C|1|100| |30|1210|229067737|1026|13|DDDD400C|0|0| |30| 0|229067737|1026|14|DDDD400C|1|101|

CREATE TABLE Aiarcle
(cis INT, aid INT, cid INT, apid INT, ntime INT, buid VARCHAR(MAX), flg INT, jid INT) 
INSERT INTO Aiarcle
(cis, aid, cid, apid, ntime, buid, flg, jid) 
VALUES (30, 1208, 229067737, 1026, 9, 'DDDD400C', 0, 0)
      ,(30, 1209, 229067737, 1026, 11, 'DDDD400C', 0, 0)
      ,(30,    0, 229067737, 1026, 12, 'DDDD400C', 1, 100)
      ,(30, 1210, 229067737, 1026, 13, 'DDDD400C', 0, 0)
      ,(30,    0, 229067737, 1026, 14, 'DDDD400C', 1, 101);

Now I need to do following here. First filter out rows WHERE ntime > 10.

SELECT * FROM Aiarcle WHERE ntime > 10

Then all I need to do is check in a single statement if flg 0 and flg 1 esists in my table and set both variables. This is what I did, but this does not works. DECLARE @flg0Exists BIT = 1 DECLARE @flg1Exists BIT = 1 SELECT @flg0Exists = CASE WHEN flg = 0 THEN 1 ELSE 0 END, @flg1Exists = CASE WHEN flg = 1 THEN 1 ELSE 0 END FROM Aiarcle WITH (NOLOCK) WHERE nTime > 10 SELECT @flg0Exists, @flg1Exists The output of which was **Note** that I don't want to split the statements, need to do above in a single statement.

I have a question about a topic that was already discussed: Let's say I have: | ID | Name|Account|Year | -------- | -------------- |------------|------------| | 1 | Jack |0001 |1928 | 2 | John |0001 |1908 | 3 | Mary |0001 |1918 | 4 | Paul |0002 |1899 | 5 | James |0002 |1913 | 6 | Laura |0003 |1925 By using EXISTS clause as shown in the link: SELECT d.ID, d.Name d.Account, d.Year FROM data a WHERE EXISTS ( SELECT 1 FROM data b WHERE Year < 1920 AND a.Account = b.Account ) ; I obtain the Output: | ID | Name|Account|Year | -------- | -------------- |------------|------------| | 1 | Jack |0001 |1928 | 2 | John |0001 |1908 | 3 | Mary |0001 |1918 | 4 | Paul |0002 |1899 | 5 | James |0002 |1913 What query should i use in order to obtain: | ID | Name|Account|Year | -------- | -------------- |------------|------------| | 4 | Paul |0002 |1899 | 5 | James |0002 |1913 If Year < 1920 at least for one line, no line from the "Account" field will not be selected.

I currently have two tournaments that have matches. Wimbledon and Atlanta open which I have one as a grand slam and the other as a masters 1000, and I want to get the players who played in both This is my code

Select distinct (first_name) 
from player, player_registration, tournament
where tournament.tournament_id = player_registration.tournament_id
and player.player_id = player_registration.player_id
and player.player_id in 
(Select player.player_id from tournament 
 where tourn_cat = "Masters 1000" and
 tourn_cat ="Grand Slam" );

It works when it's just masters 1000 or grand slam but i can't figure out how to get them together. With this query, nothing shows up I attached a photo of part of the database

I have four tables like this: **company**: | id | companyContactID | customerID | companyTeamID | | -------- | ---------------- | ---------- | ------------- | | 1 | 12 | 21 | 54 | | 2 | 14 | 12 | 78 | **document_associated_company**: | id | companyID | documentID | | -------- | --------- | ---------- | | 1 | 2 | 98 | | 2 | 1 | 12 | **document** | id | documentTypeID | | -------- | -------------- | | 98 | 67 | | 12 | 87 | **document_type** | id | name | | -------- | ------ | | 67 | NDA | | 87 | SOW | And I have an SQL query like this:

SELECT CASE
  WHEN company."customerID" IS NOT NULL THEN 'customer'
  WHEN company."companyContactID" IS NOT NULL THEN 'lead'
  WHEN company."companyTeamID" IS NOT NULL THEN 'lead'
  ELSE 'company'
  END AS status
FROM   company;

I have used a CASE expression here to create a new column named status. Basically, according to the several conditions I set the status of the company. Additionally, I need to check whether the company has a document attached with a certain type. For example, if the company have a document with the type NDA attached, then its status would be 'active'. For this I would have to put several JOIN statements. Any idea how to integrate this into the CASE expression? **Expected result:** | status | | ------ | | active | | customer |

Lets take an array of ids of size n in the application level. for instance [132,3425,13,13,... 392] where n is bigger than 100k entries. Some of this entries are repeated. I need to check if all of these registers are contained in a MySQL table containing more than 5M entries. Now I'm checking one after another, but this process takes a very long time. How can I make this verification in one single check, or maybe make this more efficient. I would like to know if all of them exists on the table, and, if possible, know which don't already exists on that table.

I'm trying to perform a common task, deleting duplicates from a table with the aim of adding a unique constraint.

CREATE TABLE IF NOT EXISTS item_identifier (
    pk       BIGSERIAL PRIMARY KEY,
    prefix   INTEGER NOT NULL,
    suffix   VARCHAR(1024) NOT NULL
);
CREATE INDEX temp_prefix_suffix_idx ON item_identifier (prefix, suffix);

I want to delete duplicates using a common query that can be found in many answers on this site. I think the rate of duplicates runs to about 1%, so there are not many to remove. Index is provided purely to help this de-duplicate and will be dropped later. Though, as you see, it isn't even used by PostgreSQL! There are 2,759,559,168 rows. The temp_prefix_suffix_idx index itself is ~ 100 GB. The CREATE INDEX took 12 hours so I don't expect the DELETE to be quick. But from a 10% sample set I extrapolated that it would take 20 hours, and it's already taken 40 hours. It's probably still within the margin of error for my sample method, but I am worried that this will take exponential time due to it not using indexes. This EXPLAIN has Seq Scan on item_identifier a and Seq Scan on item_identifier b.

EXPLAIN DELETE FROM item_identifier a
  WHERE EXISTS
    (SELECT FROM item_identifier b
       WHERE a.prefix = b.prefix
       AND a.suffix = b.suffix
       AND a.pk > b.pk);
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Delete on item_identifier a  (cost=1168440103.12..1233224771.45 rows=0 width=0)
   ->  Merge Semi Join  (cost=1168440103.12..1233224771.45 rows=919853056 width=12)
         Merge Cond: ((a.prefix = b.prefix) AND ((a.suffix)::text = (b.suffix)::text))
         Join Filter: (a.pk > b.pk)
         ->  Sort  (cost=584220051.56..591118949.48 rows=2759559168 width=52)
               Sort Key: a.prefix, a.suffix
               ->  Seq Scan on item_identifier a  (cost=0.00..57175596.68 rows=2759559168 width=52)
         ->  Materialize  (cost=584220051.56..598017847.40 rows=2759559168 width=52)
               ->  Sort  (cost=584220051.56..591118949.48 rows=2759559168 width=52)
                     Sort Key: b.prefix, b.suffix
                     ->  Seq Scan on item_identifier b  (cost=0.00..57175596.68 rows=2759559168 width=52)

Can I assume that PostgreSQL is making the right choice with not using an index? As another point of reference, another commonly suggested method gives similar results:

explain DELETE FROM item_identifier
WHERE pk IN (SELECT pk FROM (
        SELECT pk, row_number() OVER w as rnum
        FROM item_identifier
        WINDOW w AS (
            PARTITION BY prefix, suffix
            ORDER BY pk)
    ) t
WHERE t.rnum > 1);
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Delete on item_identifier  (cost=833491464.98..955347491.91 rows=0 width=0)
   ->  Merge Semi Join  (cost=833491464.98..955347491.91 rows=919853056 width=38)
         Merge Cond: (item_identifier.pk = t.pk)
         ->  Index Scan using item_identifier_pkey on item_identifier  (cost=0.58..101192612.10 rows=2759559168 width=14)
         ->  Sort  (cost=833476299.40..835775932.04 rows=919853056 width=40)
               Sort Key: t.pk
               ->  Subquery Scan on t  (cost=574787964.56..671372535.44 rows=919853056 width=40)
                     Filter: (t.rnum > 1)
                     ->  WindowAgg  (cost=574787964.56..636878045.84 rows=2759559168 width=54)
                           ->  Sort  (cost=574787964.56..581686862.48 rows=2759559168 width=46)
                                 Sort Key: item_identifier_1.prefix, item_identifier_1.suffix, item_identifier_1.pk
                                 ->  Seq Scan on item_identifier item_identifier_1  (cost=0.00..57175596.68 rows=2759559168 width=46)

The EXISTS method has a cost of 1,168,440,103 .. 1,233,224,771. The PARTITION method has a cost of 833,000,000 .. 955,000,000 (and uses the index, though not the one I thought would be uesful for the purpose!). They are close enough that I think PostgreSQL isn't making an error in its analysis of EXISTS. And is this doing a one-off doble table-scan of approx O(n*2) or is it nesting them, resulting in something more like O(n^2)?

I have an IF EXISTS 'upsert' running fine by itself in it’s own stored proc. But when I try and use the same statement referencing a CTE, it doesn't recognize the CTE. I see in related post that I'm not allowed to use the CTE as the subquery. I'm curious why is that, and how else could I accomplish this? Working stored procedure using IF EXISTS: ALTER Procedure [dbo].[sproc_receive] @StockCode VARCHAR(50), @Qty DECIMAL(18,6) AS --source: https://weblogs.sqlteam.com/dang/2007/10/28/conditional-insertupdate-race-condition/ SET NOCOUNT, XACT_ABORT ON BEGIN TRAN IF EXISTS(SELECT * FROM tblReceivedQty WITH (UPDLOCK, HOLDLOCK) WHERE StockCode = @StockCode) BEGIN UPDATE tblReceivedQty SET ReceivedQty = ReceivedQty + @Qty WHERE StockCode = @StockCode END ELSE BEGIN INSERT INTO tblReceivedQty (StockCode, ReceivedQty) VALUES (@StockCode, @Qty) END COMMIT RETURN @@ERROR GO And here is my attempt to repurpose the IF EXISTS in another stored proc which takes a json string as input. USE [] GO /****** Object: StoredProcedure [dbo].[sproc_PutAway] Script Date: 6/13/2022 4:14:02 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[sproc_PutAway] (@json NVARCHAR(MAX) = '') AS BEGIN -- Create CTE from JSON input WITH json_received(StockCode, Qty) AS ( SELECT StockCode, Qty FROM OPENJSON(@json) WITH ( StockCode VARCHAR(30) '$.StockCode', Qty DECIMAL(18,6) '$.Qty' ) ) SET NOCOUNT, XACT_ABORT ON BEGIN TRAN IF EXISTS(SELECT * FROM tblReceivedQty WITH (UPDLOCK, HOLDLOCK) WHERE tblReceivedQty.StockCode = json_received.StockCode) BEGIN UPDATE tblReceivedQty SET tblReceivedQty.ReceivedQty = tblReceivedQty.ReceivedQty - ( SELECT Sum(Qty) FROM json_received WHERE tblReceivedQty.StockCode = json_received.StockCode GROUP BY json_received.StockCode ) END ELSE BEGIN INSERT INTO tblReceivedQty (StockCode, ReceivedQty) VALUES (json_received.StockCode, (-1 * json_received.Qty)) END COMMIT RETURN @@ERROR GO This gives me a syntax error after the CTE, and a 'multipart identifer could not be bound' on all references to the CTE. Appreciate any hints!

Faced weird behaviour with EXISTS (also applies for NOT EXISTS) generating different execution plans for WHERE EXISTS(...)

EXPLAIN ANALYZE
SELECT * FROM books
WHERE EXISTS (SELECT 1 FROM authors WHERE id = books.author_id AND name LIKE 'asd%');

| QUERY PLAN                                                                                                     |
| -------------------------------------------------------------------------------------------------------------- |
| Hash Join  (cost=218.01..454.43 rows=56 width=40) (actual time=0.975..0.975 rows=0 loops=1)                    |
|   Hash Cond: (books.author_id = authors.id)                                                                    |
|   ->  Seq Scan on books  (cost=0.00..206.80 rows=11280 width=40) (actual time=0.010..0.010 rows=1 loops=1)     |
|   ->  Hash  (cost=217.35..217.35 rows=53 width=4) (actual time=0.943..0.943 rows=0 loops=1)                    |
|         Buckets: 1024  Batches: 1  Memory Usage: 8kB                                                           |
|         ->  Seq Scan on authors  (cost=0.00..217.35 rows=53 width=4) (actual time=0.942..0.943 rows=0 loops=1) |
|               Filter: ((name)::text ~~ 'asd%'::text)                                                           |
|               Rows Removed by Filter: 10000                                                                    |
| Planning Time: 0.361 ms                                                                                        |
| Execution Time: 1.022 ms                                                                                       |

vs. WHERE EXISTS(...) = TRUE

EXPLAIN ANALYZE
SELECT * FROM books
WHERE EXISTS (SELECT id FROM authors WHERE id = books.author_id AND name LIKE 'asd%') = True;

| QUERY PLAN                                                                                                           |
| -------------------------------------------------------------------------------------------------------------------- |
| Seq Scan on books  (cost=0.00..93887.20 rows=5640 width=40) (actual time=2.054..2.054 rows=0 loops=1)                |
|   Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)                                                              |
|   Rows Removed by Filter: 10000                                                                                      |
|   SubPlan 1                                                                                                          |
|     ->  Index Scan using authors_pkey on authors  (cost=0.29..8.30 rows=1 width=0) (never executed)                  |
|           Index Cond: (id = books.author_id)                                                                         |
|           Filter: ((name)::text ~~ 'asd%'::text)                                                                     |
|   SubPlan 2                                                                                                          |
|     ->  Seq Scan on authors authors_1  (cost=0.00..217.35 rows=53 width=4) (actual time=0.931..0.931 rows=0 loops=1) |
|           Filter: ((name)::text ~~ 'asd%'::text)                                                                     |
|           Rows Removed by Filter: 10000                                                                              |
| Planning Time: 0.298 ms                                                                                              |
| Execution Time: 2.129 ms                                                                                             |

Of particular interest is Hash Join vs. simple Seq Scan and the 2x time diff. Fiddle https://www.db-fiddle.com/f/a7kedUeFmCu4tWLFRoxMJA/2

I have a queue system that has two tables, jobs and jobs_queues. jobs is where we put jobs to run and job_queues we populate with queue names so that when we pick jobs we can discard those that are already running, so queues runs sequentially. My problem lies with the sub query to check locked jobs in que job_queues table. The query looks like this: explain analyze select jobs.queue_name, jobs.id from "graphile_worker".jobs where (jobs.locked_at is null or jobs.locked_at LockRows (cost=0.43..2817155.90 rows=166667 width=33) (actual time=149.335..149.335 rows=1 loops=1) -> Index Scan using jobs_priority_run_at_id_locked_at_without_failures_idx on jobs (cost=0.43..2815489.23 rows=166667 width=33) (actual time=149.332..149.332 rows=1 loops=1) Index Cond: (run_at LockRows (cost=4.28..8.31 rows=1 width=10) (actual time=0.002..0.002 rows=0 loops=79993) -> Bitmap Heap Scan on job_queues (cost=4.28..8.30 rows=1 width=10) (actual time=0.001..0.001 rows=0 loops=79993) Recheck Cond: (queue_name = jobs.queue_name) Filter: ((locked_at IS NULL) OR (locked_at Bitmap Index Scan on job_queues_pkey (cost=0.00..4.28 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=79993) Index Cond: (queue_name = jobs.queue_name) Planning time: 0.313 ms Execution time: 149.391 ms If I were to remove the locked_at in the where part of the sub query it gets much faster:

Limit  (cost=0.43..17.32 rows=1 width=33) (actual time=0.062..0.063 rows=1 loops=1)
  ->  LockRows  (cost=0.43..2814655.91 rows=166667 width=33) (actual time=0.061..0.062 rows=1 loops=1)
        ->  Index Scan using jobs_priority_run_at_id_locked_at_without_failures_idx on jobs  (cost=0.43..2812989.24 rows=166667 width=33) (actual time=0.056..0.057 rows=1 loops=1)
              Index Cond: (run_at   LockRows  (cost=4.28..8.30 rows=1 width=10) (actual time=0.032..0.032 rows=1 loops=1)
                      ->  Bitmap Heap Scan on job_queues  (cost=4.28..8.29 rows=1 width=10) (actual time=0.021..0.021 rows=1 loops=1)
                            Recheck Cond: (queue_name = jobs.queue_name)
                            Heap Blocks: exact=1
                            ->  Bitmap Index Scan on job_queues_pkey  (cost=0.00..4.28 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)
                                  Index Cond: (queue_name = jobs.queue_name)
Planning time: 0.232 ms
Execution time: 0.142 ms

A weird thing that I am seeing is that with each queue I lock at the job_queues table, the query gets heavier, here are some query plants with different number of locked queues (by lock I mean setting locked_at and locked_by columns in the job_queues and job tables): 0 jobs: https://explain.depesz.com/s/vM9g 1 job: https://explain.depesz.com/s/a0Lv 2 jobs: https://explain.depesz.com/s/fJ7I 3 jobs: https://explain.depesz.com/s/ouW9 8 jobs: https://explain.depesz.com/s/s9Yb My question then is why is it so slow and why it gets slower the more rows I set locked_at and how can I optimize it.

I want to get all archive records related a transfer ID where at least one archive record has a date in the report month (in this case September). The Archive Table SELECT PTA.PlnndTrnsfrID, PTA.PlnndTrnsfrArchveID, PTA.fromdate, PTA.ToDate FROM [Support60].[dbo].PlannedTransferArchive PTA My query so far: SELECT PTA.PlnndTrnsfrID, PTA.PlnndTrnsfrArchveID, PTA.FromDate, PTA.ToDate FROM [Support60].[dbo].PlannedTransferArchive PTA WHERE EXISTS (SELECT PTAA.PlnndTrnsfrID FROM [Support60].[dbo].PlannedTransferArchive PTAA WHERE PTAA.PlnndTrnsfrID = PTA.PlnndTrnsfrID AND PTA.FromDAte >= '2021-09-01 00:00:00' and PTA.Fromdate = '2021-09-01 00:00:00' and PTA.ToDate <= '2021-09-30 23:59:00') Order by pta.FromDate desc Additionally, Thank you ypercube, the condition checking the main instead of sub was my problem. I still have a problem with the dates. I need any record where the from or to date is any date in September. For Example, I want to capture these records in the subquery: But not these:

I have a query that looks like this: SELECT PubKey, Title FROM Publication UNION SELECT NoteKey, Title, FROM Note Which works fine. My trouble start when I try to add an EXIST to it: SELECT PubKey, Title FROM Publication UNION SELECT NoteKey, Title, FROM Note WHERE EXISTS (SELECT * FROM UserPublication WHERE UserPublication.PubKey = Publication.PubKey) That throws this error: The multi-part identifier "PubKey" could not be bound. Could it be written another way?

I have two tables with 1 identical data column for comparison. One table "species" is a wide list of species and each data row has one unique id called "suid" add. to its primary id as usual. The second table "printpipe" is a shopping cart for printouts and each row has the suid from the species and a unique personal id from the user "guid". Now I want to get all rows from "species" having empty field "stockimg" and not "noimage.png" in field "stockimg" AND do not exist in table "printpipe" with same suid AND having memb_guid identical to users guid I have as variable $guid My query I tried is: $get_allspecies = $mysqli->query("SELECT * FROM species WHERE stockimg 'noimage.png' AND stockimg '' AND NOT EXISTS (SELECT * FROM printpipe WHERE printpipe.suid = species.suid AND memb_guid = '$guid') ORDER BY name_sc, name_de"); But it doesn't work. What am I doing wrong?