Not exists with concatenated columns


#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.