Selecting records

rowantrimmer

Hello,

This might sound like a simple request. I have a members table (mysql) and I use php for my scripting. I have a field called membercode and I need to select from the members table all occurences where the value in the membercode field is the same.

Basically I need to pull out all the records where the membercode value is the same in more than just 1 record.

I hope I have explained myself OK?

Thanks

Rowan

laserlight

It sounds like you want a query similiar to:

SELECT * FROM members GROUP BY membercode HAVING COUNT(membercode) > 1

bradgrafelman

My SQL's a bit rusty, but something of this nature should work:

SELECT col1, col2
FROM members
WHERE membercode IN
(
    SELECT membercode
    FROM members
    GROUP BY membercode
    HAVING COUNT(membercode) > 1
)

Not sure if there's a better/more efficient way of doing it than that. If the query seems to run slowly, make sure the 'membercode' column is indexed in some fashion.

EDIT: Looks like laserlight posted while I was typing...

@: Wouldn't your query only return 1 row per each membercode, rather than showing all rows that share that membercode?

laserlight

bradgrafelman wrote:
@: Wouldn't your query only return 1 row per each membercode, rather than showing all rows that share that membercode?

Yes, it would.

rowantrimmer

is there anyway to get it to display all the duplicate membercode records?

Thanks

Rowan

rowantrimmer

I am asking a lot here but how can I also NOT include membercodes where the first digit is an E ?

Thanks again.

Rowan

bradgrafelman

rowantrimmer;10947823 wrote:
is there anyway to get it to display all the duplicate membercode records?

Thanks

Rowan

Yes, that's what my example query was intended for.

rowantrimmer wrote:
I am asking a lot here but how can I also NOT include membercodes where the first digit is an E ?

You could add that in a WHERE clause in the subquery of my query example.

To compare the first letter, you could use MySQL's LEFT() function (specifying 1 as the length, of course).

Res

rowantrimmer;10947824 wrote:
I am asking a lot here but how can I also NOT include membercodes where the first digit is an E ?

Thanks again.

Rowan

SELECT * FROM `members` WHERE `membercode` = `membercode` AND `membercode` LIKE 'E%'

Or if your preference is as Laserlight showed, returning one record for the duplicates not all the duplicate rows

SELECT * FROM members GROUP BY membercode HAVING COUNT(membercode) > 1 AND `membercode` LIKE ('E%')

The big difference is you have to put what would normally be your WHERE into the HAVING clause when using the GROUP BY.

rowantrimmer

Hello Res,

Thanks you so much for SELECT * FROM members GROUP BY membercode HAVING COUNT(membercode) > 1 AND membercode LIKE ('E%')

that works a treat. What I am actually trying to do is a table clean up.

What I would really like is to see all rows that share that membercode in membercode order?

I have tried to amend the sql but I can't seem to get it to work? Could you possibly point me in the right direction?

Regards

Rowan

Res

In that case you need to drop the group by/having clause, as we are telling mysql to group them by the membercode currently. We can add a little check onto the first one to make sure there is duplicate records.

SELECT *, count(`membercode`) as `member_count` FROM `members` WHERE `membercode` = `membercode` AND `membercode` LIKE 'E&#37;' AND `member_count` > 1

Really though the count check is kinda redundent, since membercode has to equal membercode, we will always have more then one record in the resultset. But anyhow, that will cause it not to group and give you each entry instead of one entry.

rowantrimmer

Thank you Res,

I will give that a go.

Regards

Rowan

rowantrimmer

Hello Res,

I am getting the following error? by the ay in your posting you said member_count which is not one of my column names so I renamed them as membercode. Have I gotten it all wrong?:eek:

Error
SQL query:

SELECT * , count( membercode ) AS membercode
FROM members
WHERE membercode = membercode
AND membercode LIKE 'E%'
AND membercode >1
LIMIT 0 , 30

MySQL said:

#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Res

I don't know why I added the count deal, it messing that query up and is completly unneeded.

SELECT `membercode`
FROM `members`
WHERE `membercode` = `membercode`
AND `membercode` LIKE 'E%'

The count isn't needed, but you DO want to use a name that isn't in the table when doing things like that. What we were saying to mysql was "Take the count of membercodes, and create a field on the fly called member_count with that value", so if we were to name it membercode, it would have a conflict.

In any case the count was uneeded and broke it ><