Not exists with concatenated columns

Databases
1

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’

what’s causing the error?

2

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.

3

This is a thought experiment on the use of concatenated cols.

By definition tbl design is less relevant IMHO

4

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')
        )
5

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

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.