I'm using postgresql 13/14. lets say I have a table with column A,B,C,D,E I have created a trigger for the table that would run every insert/update/delete. (but in this problem, I only need it for update actually, the trigger function is just a bit generic) it has run good for now. problem is, column E is only for checking, and it's not needed to run the trigger if it's updated. my trigger function, is now like this:

CREATE FUNCTION public.fnc_check()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
	if NEW.AOLD.A and NEW.BOLD.B and NEW.COLD.C and NEW.DOLD.D then
         -- do something
    end if;

	RETURN NEW;
END;
$BODY$;

problem is, on my real table, there are like 20-30 columns and it's a hassle to make if for every columns except for column E. so, is there a way to make sure the trigger only run for column A,B,C,D and not E (without using if for each other columns except E) ? thx very much

We have some consultants working on expanding an inhouse data warehouse. I was doing a code review and ran across this pattern in all of the load procs: MERGE [EDHub].[Customer].[Class] AS TARGET USING ( SELECT FROM [dbo].[vw_CustomerClass] WHERE JHAPostingDate = @PostingDate ) AS SOURCE ON TARGET.BankId = SOURCE.BankId -- This join is on the business keys AND TARGET.Code = SOURCE.Code WHEN NOT MATCHED BY TARGET THEN WHEN MATCHED AND TARGET.IsLatest = 1 AND EXISTS ( SELECT SOURCE.[HASH] EXCEPT SELECT TARGET.[Hash] ) THEN The gist is, if we have a new business key, insert but if the business key exists and the hash of the attributes don't match our current row then update the old row and insert a new one (later in the code). It all works fine but I paused when I got to this code AND EXISTS ( SELECT SOURCE.[HASH] EXCEPT SELECT TARGET.[Hash] ) It seems overly complicated compared to SOURCE.[HASH] TARGET.[Hash]. The EXCEPT will do an accurate NULL comparison but in our case hashes will never be NULL (or we have bigger problems). I want our code to be easy to read so that when someone has to maintain it, it doesn't confuse. I asked our consultants about it and they speculated that it might be faster because of set operations but I decided to write a simple test (test code below). The first thing I noticed was the EXISTS/EXCEPT had a more complicated query plan but that's not always bad I ran each select client statistics on and the join yielded total execution time of 12,000 vs 25,000 with the EXISTS/EXCEPT. I want to take this to our consultants with the request to refactor that statement but wanted to get feedback here on: 1. is this a good test? - am I missing anything? 2. is there a case where EXISTS/EXCEPT would be a better comparison? Test script: CREATE TABLE r (hash VARBINARY(8000)) CREATE TABLE l (hash VARBINARY(8000)) SET NOCOUNT ON DECLARE @x INT = 10000 WHILE @x 0 BEGIN INSERT INTO dbo.r ( hash ) SELECT HASHBYTES('SHA2_256',CAST(NEWID() AS VARCHAR(200))) INSERT INTO dbo.l ( hash ) SELECT HASHBYTES('SHA2_256',CAST(NEWID() AS VARCHAR(200))) SET @x = @x-1 END INSERT INTO dbo.r ( hash ) VALUES ( NULL ) INSERT INTO dbo.l ( hash ) VALUES ( NULL ) SELECT COUNT(1) FROM dbo.l CROSS JOIN dbo.r WHERE ISNULL(r.hash,0) ISNULL(l.hash,0) SELECT COUNT(1) FROM dbo.l CROSS JOIN dbo.r WHERE EXISTS(SELECT r.hash except select l.HASH)

I have a table containing the names and filesystem locations of C source-files in a table, a list of relevant categories for the programming principals demonstrated by the source files in a second table and then a junction table that is a one-to-many for the categories associated with each source-file. (table definitions below question) What I need to do is get a list of file_ids from the source-file table that have no associated file_id entry in the junction table. I have done that using an except clause that relies on an inner join selecting records from the junction table where file_ids are present in the junction table, e.g.

select distinct file_id from c_file
  
  except (
    
    select distinct c_file.file_id from c_file 
      
      inner join c_file_topic_rel 
        on c_file_topic_rel.file_id = c_file.file_id 
        
        where exists (
          
          select distinct file_id from c_file_topic_rel
        
        )
  )
  
  order by file_id;

This works fine, but I'm no SQL master and I'm skeptical I'm taking a very long way around to get the records from the c_file table NOT in the junction table c_file_topic_rel. Is there a better way to go about isolating the records with no entry in the junction table? (there are about 15,000 total entries in the c_file table, if the size matters -- it's relatively small) **Table Definitions** The Default column is removed to prevent excess scrolling. If relevant, I'm happy to add the full definitions. **c_file table**

Table "public.c_file"
 Column  |            Type             | Collation | Nullable |
---------+-----------------------------+-----------+----------+
 file_id | integer                     |           | not null | ...
 fname   | character varying(64)       |           | not null |
 dirname | character varying(128)      |           | not null |
 mtime   | timestamp without time zone |           | not null | ...
 created | timestamp without time zone |           | not null | ...
Indexes:
    "cfile_pkey" PRIMARY KEY, btree (file_id)
    "cfile_fname_key" UNIQUE CONSTRAINT, btree (fname)

**c_topics table**

Table "public.c_topics"
      Column       |            Type             | Collation | Nullable |
-------------------+-----------------------------+-----------+----------+
 topic_id          | integer                     |           | not null | ...
 topic             | character varying(32)       |           | not null |
 topic_description | character varying(96)       |           | not null |
 created           | timestamp without time zone |           |          | ...
 modified          | timestamp without time zone |           |          | ...
 seq               | smallint                    |           |          |
Indexes:
    "c_topics_pkey" PRIMARY KEY, btree (topic_id)
    "c_topics_topic_key" UNIQUE CONSTRAINT, btree (topic)
Referenced by:
    TABLE "c_file_topic_rel" CONSTRAINT "c_file_topic_rel_topic_id_fkey" FOREIGN KEY (topic_id) REFERENCES c_topics(topic_id)

**c_file_topic_rel junction table**

Table "public.c_file_topic_rel"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 file_id  | integer |           | not null | 
 topic_id | integer |           | not null | 
Indexes:
    "c_file_topic_rel_file_id_topic_id_key" UNIQUE CONSTRAINT, btree (file_id, topic_id)
Foreign-key constraints:
    "c_file_topic_rel_file_id_fkey" FOREIGN KEY (file_id) REFERENCES c_file_bf(file_id)
    "c_file_topic_rel_topic_id_fkey" FOREIGN KEY (topic_id) REFERENCES c_topics(topic_id)

I want to be able to write a .sql script that will carry out an action which fails, and the script should only report failure if the action _doesn't_ fail. For example, given initial table:

create table tbl(x integer, y integer);

I might update this table in a migration with the following:

alter table tbl add constraint unique_tst unique (x, y);

And want to write a test script for that migration, which will be similar to the following:

insert into tbl(x, y) values 
(1, 1),
(1, 1)
;

This will fail - which is expected given the constraint - but I'm not sure how to handle that failure in postgres. Something such as:

if does not fail: 
    insert into tbl(x, y) values 
    (1, 1),
    (1, 1)
    ; 
return:
    failure

But I have no idea if this exists. Note - I cannot install any extensions for this.

Given two columns, t1 with distinct values, but a superset of t2, how is it possible to operate over t1 to subtract the elements of t2 of t1? I utilize MySQL 5.7 CREATE TABLE IF NOT EXISTS t1 ( id_ INTEGER NOT NULL, value1 INT NOT NULL INSERT INTO t1 (id_, value1) VALUES (1), (2), (3), (4), (5), (6), (7), (8) CREATE TABLE IF NOT EXISTS t1 ( id_ INTEGER NOT NULL, value1 INT NOT NULL INSERT INTO t2 (id_, value2) VALUES (1), (2), (3), (4)

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) v (a) ) ,r as ( select a from cte where a in (1,2,3) union all select a from ( select a from cte except select a from r ) x ) select a from r I came across this while trying to answer a question on Stack Overflow.

I have two databases - Database1 and Database2. Both databases contain a table that has similar structure, exemplified as follows:

===========================================================================================
| ID | Name | PhoneNoFormat | DialingCountryCode | InternationalDialingCode | InternetTLD |
===========================================================================================
|    |      |               |                    |                          |             |
===========================================================================================

However, due to some reason, (a) one of the tables in one of the databases has data that is not exactly the same as (b) that contained in the other table in the another database. So, how can I compare Database1.Table1 against Database2.Table1? I tried using the following query, but nothing happened so was wondering if I have to rewrite it: SELECT MIN(TableName) as TableName, ID, Name, PhoneNoFormat, DialingCountryCode, InternationalDialingCode, InternetTLD FROM ( SELECT 'Table A' as TableName, A.ID, A.Name, A.PhoneNoFormat, A.DialingCountryCode, A.InternationalDialingCode, A.InternetTLD FROM [D:\DATABASE1.MDF].[dbo].[Table1] AS A UNION ALL SELECT 'Table B' as TableName, B.ID, B.Name, B.PhoneNoFormat, B.DialingCountryCode, B.InternationalDialingCode, B.InternetTLD FROM [D:\DATABASE2.MDF].[dbo].[Table1] AS B ) tmp GROUP BY ID, Name, PhoneNoFormat, DialingCountryCode, InternationalDialingCode, InternetTLD HAVING COUNT(*) = 1 ORDER BY ID

I am trying to make an except in SQL but it doesnt exclude the data even though they are identical. SELECT A.payment_id, SUM(A.importe_trx) monto_total FROM [dbo].[ABONOS] A WHERE payment_id = '980190391415704' GROUP BY A.payment_id SELECT B.payment_id payment_id, SUM(B.precio_venta) monto_total FROM [dbo].[SALES_VISANET_USD] B WHERE payment_id = '980190391415704' GROUP BY B.payment_id I used that query to get the data and it's the same, but it doesn't exclude it with the EXCEPT command in the middle SELECT A.payment_id, SUM(A.importe_trx) monto_total FROM [dbo].[ABONOS] A WHERE payment_id = '980190391415704' GROUP BY A.payment_id EXCEPT SELECT B.payment_id payment_id, SUM(B.precio_venta) monto_total FROM [dbo].[SALES_VISANET_USD] B WHERE payment_id = '980190391415704' GROUP BY B.payment_id I use "sum" because in one of the tables it has different amounts with the same payment_id, but when adding and grouping them they give the same amount and the same data as the other table.

The following EXCEPT query produces a logical plan with a seemingly purposeless projection. This also occurs for INTERSECT. What is the purpose of the projection? e.g. Is there a different EXCEPT query where the outer projection would specify something? Query:

use AdventureWorks2017

select p.ProductId
from Production.Product as p
except
select pinv.ProductID
from Production.ProductInventory as pinv
option (recompile, querytraceon 8605, querytraceon 3604)

Converted Tree:

LogOp_Select

        LogOp_GbAgg OUT(QCOL: [p].ProductID,) BY(QCOL: [p].ProductID,)

            LogOp_Project -- << ?? PASSIVE PROJECTION ??

                LogOp_Project

                    LogOp_Get TBL: Production.Product(alias TBL: p) Production.Product TableID=482100758 TableReferenceID=0 IsRow: COL: IsBaseRow1000 

                    AncOp_PrjList 

                AncOp_PrjList 

            AncOp_PrjList 

        ScaOp_Exists 

            LogOp_Select

                LogOp_Project

                    LogOp_Get TBL: Production.ProductInventory(alias TBL: pinv) Production.ProductInventory TableID=914102297 TableReferenceID=0 IsRow: COL: IsBaseRow1001 

                    AncOp_PrjList 

                ScaOp_Comp x_cmpEq

                    ScaOp_Identifier QCOL: [p].ProductID

                    ScaOp_Identifier QCOL: [pinv].ProductID

I get this error: It arises when I try to execute the following query:

with expensive_service as (
    select s1.*
    from service s1, service s2
    where s1.price > s2.price
)
select * 
from service except expensive_service;

I was trying to implement WITH ... AS (link to PostgreSQL docs). This query gives me the desired output I'm looking for:

select * 
from service except ( 
    select s1.*
    from service s1, service s2
    where s1.price > s2.price
)

Any aid directing me where the error lies would be greatly appreciated!

I'm using SQL Server and when I run

SELECT * FROM TABLE1 EXCEPT SELECT * FROM TABLE2

it returns 1610 entries. When I query

`
SELECT COUNT(*) FROM TABLE1 
SELECT COUNT(*) FROM TABLE2

` I get 6399 and 4583, the difference of which is 1546, not 1610 as I would expect from the first query. What could be causing this discrepancy between the number of results returned and the difference of the individual table counts?

The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ? Does it do the same? I would like a simple explanation with an example.

Among some SQL Server developers, it's a widely held belief that **NOT IN is terribly slow**, and queries should be rewritten so that they return the same result but do not use the "evil" keywords. ([example](https://web.archive.org/web/20160912175949/http://www.componentworkshop.com/blog/2009/06/26/sql-server-basics-avoiding-in-and-not-in)) . **Is there any truth to that?** Is there, for example, some known bug in SQL Server (which version?) that causes queries using NOT IN to have a worse execution plan than an equivalent query that uses * a LEFT JOIN combined with a NULL check or * (SELECT COUNT(*) ...) = 0 in the WHERE clause ?

We're doing an ETL process. When all is said and done there are a bunch of tables that should be identical. What is the quickest way to verify that those tables (on two different servers) are in fact identical. I'm talking both schema and data. Can I do a hash on the table it's self like I would be able to on an individual file or filegroup - to compare one to the other. We have Red-Gate data compare but since the tables in question contain millions of rows each I'd like something a little more performant. One approach that intrigues me is this creative use of the union statement . But, I'd like to explore the hash idea a little further if possible. **POST ANSWER UPDATE** For any future vistors... here is the exact approach I ended up taking. It worked so well we're doing it on every table in each database. Thanks to answers below for pointing me in the right direction. CREATE PROCEDURE [dbo].[usp_DatabaseValidation] @TableName varchar(50) AS BEGIN SET NOCOUNT ON; -- parameter = if no table name was passed do them all, otherwise just check the one -- create a temp table that lists all tables in target database CREATE TABLE #ChkSumTargetTables ([fullname] varchar(250), [name] varchar(50), chksum int); INSERT INTO #ChkSumTargetTables ([fullname], [name], [chksum]) SELECT DISTINCT '[MyDatabase].[' + S.name + '].[' + T.name + ']' AS [fullname], T.name AS [name], 0 AS [chksum] FROM MyDatabase.sys.tables T INNER JOIN MyDatabase.sys.schemas S ON T.schema_id = S.schema_id WHERE T.name like IsNull(@TableName,'%'); -- create a temp table that lists all tables in source database CREATE TABLE #ChkSumSourceTables ([fullname] varchar(250), [name] varchar(50), chksum int) INSERT INTO #ChkSumSourceTables ([fullname], [name], [chksum]) SELECT DISTINCT '[MyLinkedServer].[MyDatabase].[' + S.name + '].[' + T.name + ']' AS [fullname], T.name AS [name], 0 AS [chksum] FROM [MyLinkedServer].[MyDatabase].sys.tables T INNER JOIN [MyLinkedServer].[MyDatabase].sys.schemas S ON T.schema_id = S.schema_id WHERE T.name like IsNull(@TableName,'%');; -- build a dynamic sql statement to populate temp tables with the checksums of each table DECLARE @TargetStmt VARCHAR(MAX) SELECT @TargetStmt = COALESCE(@TargetStmt + ';', '') + 'UPDATE #ChkSumTargetTables SET [chksum] = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM ' + T.FullName + ') WHERE [name] = ''' + T.Name + '''' FROM #ChkSumTargetTables T SELECT @TargetStmt DECLARE @SourceStmt VARCHAR(MAX) SELECT @SourceStmt = COALESCE(@SourceStmt + ';', '') + 'UPDATE #ChkSumSourceTables SET [chksum] = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM ' + S.FullName + ') WHERE [name] = ''' + S.Name + '''' FROM #ChkSumSourceTables S -- execute dynamic statements - populate temp tables with checksums EXEC (@TargetStmt); EXEC (@SourceStmt); --compare the two databases to find any checksums that are different SELECT TT.FullName AS [TABLES WHOSE CHECKSUM DOES NOT MATCH] FROM #ChkSumTargetTables TT LEFT JOIN #ChkSumSourceTables ST ON TT.Name = ST.Name WHERE IsNull(ST.chksum,0) IsNull(TT.chksum,0) --drop the temp tables from the tempdb DROP TABLE #ChkSumTargetTables; DROP TABLE #ChkSumSourceTables; END

When changing a database to partially contained I am getting the following error: > Cannot resolve the collation conflict between "Latin1_General_CI_AS" > and "Latin1_General_100_CI_AS_KS_WS_SC" in the EXCEPT operation. > > Errors were encountered in the procedure 'RSExecRole.DeleteExtensionModuleDDL' > during compilation of the > object. Either the containment option of the database 'VeeamOne' was > changed, or this object was present in model db and the user tried to > create a new contained database. > ALTER DATABASE statement failed. The containment option of the database 'VeeamOne' could not be altered because compilation errors > were encountered during validation of SQL modules. See previous > errors. > ALTER DATABASE statement failed. (.Net SqlClient Data Provider) The object this is reporting on I think is from SSRS. However the DB I am changing the collation on is a completely separate application. Does anyone have any suggestions on how to resolve this? ========================================================================= OK this is the code for the proc, not sure what about it causes it to no be able to be contained though USE [VeeamOne] GO /****** Object: StoredProcedure [reporter].[DeleteExtensionModuleDDL] Script Date: 02/12/2015 12:06:19 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [reporter].[DeleteExtensionModuleDDL] @EMID int AS BEGIN SET NOCOUNT ON; declare @Debug bit; set @Debug = 0; declare @Emulate bit; set @Emulate = 0; declare @reportPackDestructorFunctionName nvarchar(max) exec @reportPackDestructorFunctionName = [reporter].GenerateExtensionModuleDestructorName @EMID if exists(select * from sys.objects where (object_id = OBJECT_ID(@reportPackDestructorFunctionName) and type in (N'P', N'PC'))) begin exec @reportPackDestructorFunctionName declare @objectsToDelete as table (Name nvarchar(2048), Type nvarchar(2048)) insert @objectsToDelete exec @reportPackDestructorFunctionName if @Debug = 1 begin select * from @objectsToDelete end declare @TablesToDelete as table(ObjectID int, Name varchar(max)) declare @FunctionsToDelete as Table(Name nvarchar(max)) declare @StoredProceduresToDelete as Table(Name nvarchar(max)) declare @AssembliesToDelete as Table(Name nvarchar(max)) declare @ViewsToDelete as Table(Name nvarchar(max)) insert into @TablesToDelete select object_id(Name), Name from @objectsToDelete where Type = 'Table' insert into @FunctionsToDelete select Name from @objectsToDelete where Type = 'Function' insert into @StoredProceduresToDelete select Name from @objectsToDelete where Type = 'Procedure' union select @reportPackDestructorFunctionName insert into @AssembliesToDelete select Name from @objectsToDelete where Type = 'Assembly' insert into @ViewsToDelete select Name from @objectsToDelete where Type = 'View' declare @DependencyTree as Table(ForeignKeyObjectID int, ForeignKeyObjectName nvarchar(max), ParentTableID int, ParentTableName nvarchar(max), ChildTableID int, ChildTableName nvarchar(max), Generation int) declare @Generation int; set @Generation = 0; insert into @DependencyTree select distinct(fk.object_id) as ForeignKeyObjectID, fk.name as ForeignKeyObjectName, fk.referenced_object_id as ParentTableID, parent.name as ParentTableName, fk.parent_object_id as ChildTableID, child.name as ChildTableName, @Generation from sys.foreign_keys as fk inner join sys.objects as parent on fk.referenced_object_id = parent.object_id inner join sys.objects as child on fk.parent_object_id = child.object_id where fk.referenced_object_id in (select ObjectID from @TablesToDelete) while @@ROWCOUNT > 0 begin set @Generation = @Generation + 1 insert into @DependencyTree select fk.object_id as ForeignKeyObjectID, fk.name as ForeignKeyObjectName, fk.referenced_object_id as ParentTableID, parent.name as ParentTableName, fk.parent_object_id as ChildTableID, child.name as ChildTableName, @Generation from @DependencyTree dt inner join sys.foreign_keys as fk on fk.referenced_object_id = dt.ChildTableID inner join sys.objects as parent on fk.referenced_object_id = parent.object_id inner join sys.objects as child on fk.parent_object_id = child.object_id except select ForeignKeyObjectID, ForeignKeyObjectName, ParentTableID, ParentTableName, ChildTableID, ChildTableName, @Generation from @DependencyTree end declare @clearScript as table(ID int primary key identity (0,1), ScriptText nvarchar(max)) insert into @clearScript select 'alter table [reporter].[' + ChildTableName + '] drop constraint [' + ForeignKeyObjectName + ']' from @DependencyTree where ParentTableName in (select Name from @TablesToDelete) insert into @clearScript select 'drop table [reporter].[' + Name + ']' from @TablesToDelete insert into @clearScript select 'drop function [reporter].[' + Name + ']' from @FunctionsToDelete insert into @clearScript select 'drop procedure [reporter].[' + Name + ']' from @StoredProceduresToDelete insert into @clearScript select 'drop assembly [reporter].[' + Name + ']' from @AssembliesToDelete insert into @clearScript select 'drop view [reporter].[' + Name + ']' from @ViewsToDelete if @Debug = 1 begin select * from @clearScript end declare @str nvarchar(max) declare @ID int; set @ID = 0; declare @MaxID int select @MaxID = MAX(ID) from @clearScript print '' while @ID <= @MaxID begin select @str = ScriptText from @clearScript where ID = @ID if @Emulate = 1 print(@str) else exec sp_executesql @statement = @str set @ID = @ID + 1 end end END

Typically when I see SQL that uses something like: select * from employees where epmloyeeTypeId in (select id from type where name = 'emp') I replace the where with this: select e.* from employees e inner join type t on t.id=e.epmloyeeTypeId and t.name = 'emp' Is it possible to do the same with the inverse in case its is a not in (like below) instead of an in clause? INSERT into Subscriptions(ProjectId, RecordTypeCID, NTID, Active, Added, LastUpdate, UpdateBy) SELECT @ProjectId, RecordTypeCID, @NTID, 1, GETDATE(), GETDATE(), @NTID FROM @Check CHK WHERE CHK.ActiveStatus=1 And Not Exists (SELECT SubscriptionId FROM Subscriptions WHERE ProjectId=@ProjectId and NTID=@NTID and RecordTypeCID = CHK.RecordTypeCID ) **Additional considerations** Can I do this: INSERT INTO Subscriptions(ProjectId, RecordTypeCID, NTID,Active, Added, LastUpdate, UpdateBy) SELECT @ProjectId, RecordTypeCID, @NTID,1, GETDATE(), GETDATE(), @NTID FROM @Check CHK LEFT JOIN Subscriptions subs ON subs.RecordTypeCID = CHK.RecordTypeCID AND NTID = @NTID AND ProjectId = @ProjectId AND CHK.ActiveStatus = 1 AND subs.SubscriptionId IS NULL

I just want to ask how to compare two database tables, staging vs. operational tables. After we did the transformation and convert it to correct datatype like convert flag column (Y/N) nvarchar to bit or to int, how to compare data between the two tables?

I have a function here that is supposed to take two tables as arguments and check if they are the same. create or replace function testing.equal_tables( varchar, varchar) returns void as $$ begin execute 'select * from (select * from ' || $1 ||' except select * from ' || $2 || ') a union (select * from ' || $2 || ' except select * from ' || $1 || ');' ; end; $$ language plpgsql; When I call it with these two tables, one with 20 rows and one with 10 rows, I get only the empty set, which is not the correct result: select testing.equal_tables('ee1', 'ee2'); When I modified the function to return a string of the statement, it came back correctly, but that still doesn't help because I'd like to execute the return string using a function, prepared statement, or something. Is there any way to make this function work?

I have a junction table between categories and values that indicate which categories are associated with which each value. I would like to find the values that are not associated with each category. Category: ID CategoryName -- ------------ 1 category1 2 category2 3 category3 CategoryValue: CategoryID ValueID ---------- ------- 1 1 1 2 2 1 3 2 Value: ID ValueName -- --------- 1 value1 2 value2 3 value3 The output I would like for the query is the following: CategoryID ValueID ValueName ---------- ------- --------- 1 3 value3 2 2 value2 2 3 value3 3 1 value1 3 3 value3 I'm pretty stuck on how to approach this as a query. The "junction" table seems to prevent the usual LEFT JOIN WHERE null approach. So any advice would be much appreciated.

I have two tables, stock and location, with a third table that retains the level of stock at each location, stock_loc_info. Each stock item should have a row for each location. What SQL query would show that the row B1 is missing from stock_loc_info? stock.stockcode --------------- A B location.locno -------------- 1 2 stock_loc_info.fkstockcode stock_loc_info.fklocno -------------------------- ---------------------- A 1 A 2 B 2