Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jan 2013
    Posts
    17
    Thanks
    6
    Thanked 0 Times in 0 Posts

    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.

    I believe I need to display the 'Records' column by default with all query results somehow?
    Last edited by sitechooser; 01-09-2013 at 11:31 AM. Reason: Clarity

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    sitechooser (01-14-2013)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,184
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    sitechooser (01-14-2013)

  • #4
    New Coder
    Join Date
    Jan 2013
    Posts
    17
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thanks ever so much for your assistance, I'm all sorted now. Much appreciated.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •