This code works with cols that are not concatenated:
SELECT
item3
FROM
nconnectz t1
WHERE
NOT EXISTS
(
SELECT
item3
FROM
itemandsize t2
WHERE
t1.item3 = t2.item3
)
GROUP BY item3
I think this is the same query with concatenated columns
SELECT
CONCAT_WS(',', itemandsize, pkg_quantity) item4
FROM
nconnectz t1
WHERE
NOT EXISTS
(
SELECT
CONCAT_WS(',', itemandsize, '0.00') item4
FROM
itemandsize t2
WHERE
t1.item4 = t2.item4
)
GROUP BY item4
the error is: Unknown column ‘t1.item4’ in ‘where clause’
Based on your query, it would appear that you database design is incorrect. First start with posting your DB schema for us to review. That needs to be fixed first.
After spending a little time with my grandson, it occurred to me:
Voila!
SELECT
nconnectz.itemandsize, nconnectz.pkg_quantity
FROM
nconnectz
WHERE
NOT EXISTS
(
SELECT
*
FROM
itemandsize
WHERE
CONCAT_WS(',', nconnectz.itemandsize, nconnectz.pkg_quantity) = CONCAT_WS(',', itemandsize.itemandsize, '0.00')
)
item4 is a column alias that you assigned in the SELECT clause, and you cannot refer to a column alias in the WHERE clause because of the order of operations in sql
you should be using a LEFT OUTER JOIN in any case
SELECT CONCAT_WS(',', itemandsize, pkg_quantity) item4
FROM nconnectz t1
LEFT OUTER
JOIN itemandsize t2
ON t2.itemandsize = t1.itemandsize
WHERE t2.itemandsize IS NULL