Not exists with concatenated columns

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?

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.

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

By definition tbl design is less relevant IMHO

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

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