finding duplciate information in d/base fields

hi guys,

this is for CC.2.1

I need to find (and display) the records which contain a firstname & surname combination that are the same but the first name and surname are different fields (not a combined field). e.g. firstname='keith' surname = 'moon'

The trick is that I don;t know what the firstname and surname will be, I just need a list that shows them all

I can't get any SQL statement to work that involve 'COUNT' or 'HAVING' or anything else

Any thoughts

aj

Hi ajw3208,

I don't completely understand this but it sounds as though you just need to build an appropraite WHERE clause like WHERE `firstname` = '{firstname}' AND `lastname` = '{lastname}'

You can't use COUNT as that would return a single value, not a list for display.

I'm not familiar with HAVING but looking at the manual you might be able to enter it in the ORDER BY box (you can do that with GROUP BY).

Bob

Hi Bob,

what I need to achieve is a list that displays all the records where values {firstname} and values in {surname} are the duplicated but I don;t know the value to look for in each field. I want them all

In SQL query lingo, COUNT >1 (or having >1) is used to determine if there is more than 1 record. e.g. std SQL would be

select field1, field2, count(*) from TableName
group by field1, field2
having count(*) > 1

given we only have the 'where' operator in CC, how do I identify all records where the value in a field is the same (i.e. duplicated) e.g. all records that have aj in the {firstname} and 'w' in the {surname}

Hi ajw3208,

OK - now I get it. I don't think that you can do that at present :-(

You could add group by field1, field2 having count(*) > 1 in the ORDER box - that would work, but there is no way of adding the count(*) to the column list.

I do have a hacked version of ChronoForms that I'm working on in odd moments that will allow this - it has some extra code that lets you enter a column list in the WHERE box. I'll post this once I have a decently working beta version.

Bob

Hi Bob,

forgot to wish you the best for 2010. Hope the snow isn't too deep.

Anyway

the synatx I put in the order box is below

GROUP BY playerFirstname, playerSurname HAVING count(*) > 1

This didn't "blow up" but it didn't return any records and I know there is at least 2 records.

any thoughts?

aj

Hi aj,

Best wishes for 2010 to you too . . . and to everyone else here.

I was a bit too quick with that post. Because ChronoForms prepends ORDER BY you will need a 'dummy' column in there to stop the query breaking. Try

`playerFirstname` ASC GROUP BY `playerFirstname`, `playerSurname` HAVING COUNT(*) > 1

I'm still not sure if this will work without the COUNT defined in the column list but it's worth a try.

Bob

Back again,

Sadly, no go. still returns an empty list.

Happy to entertain any other mechanism. Is there a way we can do this in CF?

aj