Displaying a table column in addition to query results

[sitechooser]

Hi Everyone,

I am running the following MySQL query:

[code]

SELECT COUNT(*), OldCompanyName, NewCompanyName FROM moortown5
GROUP BY OldCompanyName, NewCompanyName
HAVING COUNT(*)>1;

[code]

This successfully returns matches and partial matches from the required column.

My problem is this. I want to display a column called 'Records' along with the results so that I know which table row the results belong to.

Can anyone help?

Thanks very much in advance.

I believe I need to display the 'Records' column by default with all query results somehow?

[Old Pedant]

If you want to be able to identify the table row, then *YOU* need to provide a field in the table that will do so. Records in relational databases (of which MySQL is one) do *NOT* have record numbers automatically attached to them.

And easy way to add such a number would be

   ALTER TABLE moortown5 ADD recordID INT AUTO_INCREMENT PRIMARY KEY

Now... the problem with doing

SELECT COUNT(*), recordID, OldCompanyName, NewCompanyName FROM moortown5
GROUP BY OldCompanyName, NewCompanyName
HAVING COUNT(*)>1;

is that only one of the duplicate recordid's will be found. (And if you weren't using MySQL, the query wouldn't even be legal.)

I assume that you want to get a list of *ALL* the duplicated recordid's no?

There are a couple of ways to do it, but this may be the best:

SELECT T.recordID, T.OldCompanyName, T.NewCompanyName 
FROM moortown5 AS T,
    ( SELECT COUNT(*), OldCompanyName, NewCompanyName 
      FROM moortown5
      GROUP BY OldCompanyName, NewCompanyName
      HAVING COUNT(*)>1 ) AS S
WHERE S.OldCompanyName = T.OldCompanyName
AND S.NewCompanyName = T.NewCompanyName
ORDER BY T.recordId

(the last ORDER BY there is arbitrary..change to what works best for you)

[Old Pedant]

Another way is this:

SELECT DISTINCT T.recordID, T.OldCompanyName, T.NewCompanyName 
FROM moortown5 AS T, moortown5 AS S
WHERE S.OldCompanyName = T.OldCompanyName
AND S.NewCompanyName = T.NewCompanyName
AND T.recordID <> S.recordID

I'm not sure which will be more efficient. You could try it both ways.

[sitechooser]

Thanks ever so much for your assistance, I'm all sorted now. Much appreciated.