CodingForums.com - Displaying a table column in addition to query results

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Displaying a table column in addition to query results (http://www.codingforums.com/showthread.php?t=285512)

Displaying a table column in addition to query results

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.:confused:

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

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

Code:

ALTER TABLE moortown5 ADD recordID INT AUTO_INCREMENT PRIMARY KEY

Now... the problem with doing

Code:

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:

Code:

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)

Another way is this:

Code:

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.

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