CODEWITHSUNDEEP
sql
tomc0920
tomc0920

Reputation: 21

UniVerse TCL line JOIN syntax

I am trying to JOIN two tables; PRODUCT AND PSUB.

PRODUCT contains one row of data for each PRODUCT_ID

PSUB contains multiple rows of data for each PRODUCT_ID transaction, however PRODUCT_ID is not multivalued.

Here is the output of both tables on two specific IDs.

enter image description here

I would like to JOIN the tables, to run two reports:

  1. Show PRODUCT_IDS that are in PRODUCT table, however, exclude those that are in PSUB table.

  2. Show all PRODUCT_IDs that are in PSUB table, however, exclude those that are in PRODUCT table.

Here is what I attempted to use to get a COUNT, however, query basically locked up:

SELECT COUNT (*) FROM PRODUCT, PSUB WHERE PRODUCT.PRODUCT_ID <> PSUB.PRODUCT_ID;

Here are the individual COUNT queries.

enter image description here

Thank you for your help in advance!!

Tom

Upvotes: 0

Views: 196

Answers (1)

tvm
tvm

Reputation: 3449

1) Example using WHERE NOT EXISTS, can be also done just by using joins, etc.

SELECT
p.product_id
FROM
product p
WHERE NOT EXISTS ( SELECT product_id FROM psub );

2) Just like first example, you need only to swap the tables.

another solution using NOT IN:

SELECT
p.product_id
FROM
product p
WHERE p.product_id NOT IN ( SELECT product_id FROM psub );

and another one using LEFT JOIN

SELECT
p.product_id
FROM
product p
LEFT JOIN psub b ON p.product_ID = b.product_id
WHERE
b.product_id IS NULL;

Upvotes: 0

Related Questions