Hi all,
I have recently ported my dbase from MySQL to sqlite and i am having problems porting my sql statements over to sqlite. I cannot use the count (distinct ) feature, and need to workaround this.... what i need is an alternative to this query without the use of count(distinct) .... i need to obtain all the unique x elements that have correlating y elements that exist in table 1 and occur more than once.
table 1 has y
table 2 has x,y
i want to know retrieve all the x's where in table 1 the y values for the same x exceed one element (not the value but the number of y elements in table 1 that have the same x exceeding 1)....i hope this makes my question cleare..thanks again
| SELECT
| | x
| FROM
| | (
| | SELECT DISTINCT
| | | (2.x),
| | | count(DISTINCT(1.y)) AS count
| | FROM 1,
| | | 2
| | WHERE 1.y= 2.y
| | GROUP BY 2.x
| | ) AS collectionOFx
| WHERE count > 1
I have recently ported my dbase from MySQL to sqlite and i am having problems porting my sql statements over to sqlite. I cannot use the count (distinct ) feature, and need to workaround this.... what i need is an alternative to this query without the use of count(distinct) .... i need to obtain all the unique x elements that have correlating y elements that exist in table 1 and occur more than once.
table 1 has y
table 2 has x,y
i want to know retrieve all the x's where in table 1 the y values for the same x exceed one element (not the value but the number of y elements in table 1 that have the same x exceeding 1)....i hope this makes my question cleare..thanks again
| SELECT
| | x
| FROM
| | (
| | SELECT DISTINCT
| | | (2.x),
| | | count(DISTINCT(1.y)) AS count
| | FROM 1,
| | | 2
| | WHERE 1.y= 2.y
| | GROUP BY 2.x
| | ) AS collectionOFx
| WHERE count > 1
