Results 1 to 1 of 1
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Unanswered: MySql Query in the same table using 4.0.12

    Hi,
    this is an example table i am working with

    Company| Account| Project|Description
    --------------------------------
    3311 |9999 |8181 | abc
    1100 |9999 |8080 | bcd
    2200 |9999 |8181 | abc
    3300 |9999 |8282 | cde
    4400 |1111 |8080 | bcd
    5500 |1111 |8080 | bcd
    6600 |1111 |8080 | bcd
    7700 |1111 | |
    8800 |1111 | |
    1234 |2222 |8888 | def
    2345 |2222 |8888 | def
    3456 |2222 |8888 | def
    4567 |3333 | |
    6789 |3333 | |
    7890 |3333 | |
    The description field comes from another table by joining them together.
    I am supposed to be able to get a result where if all the projects under the same account are the same, I will only display one line indicating the account number and the project. So since accounts 2222 and 3333 have the same projects under them(empty fields are counted as one type of project),
    I should be able to get the result:

    Company| Account| Project|Description
    --------------------------------
    |2222 |8888|def
    |3333 | |
    And I will leave the company field just blank


    However, if the projects under the same account are different, I will have to display each and everyone of the account with the company name.

    This should be the result.
    Company| Account| Project|Description
    --------------------------------
    3311 |9999 |8181|abc
    1100 |9999 |8080|bcd
    2200 |9999 |8181|abc
    3300 |9999 |8282|cde
    4400 |1111 |8080|bcd
    5500 |1111 |8080|bcd
    6600 |1111 |8080|bcd
    7700 |1111 | |
    8800 |1111 | |

    So if i combine both case toether I should be able to see the table as shown:
    Company| Account| Project|Description
    --------------------------------
    3311 |9999 |8181|abc
    1100 |9999 |8080|bcd
    2200 |9999 |8181|abc
    3300 |9999 |8282|cde
    4400 |1111 |8080|bcd
    5500 |1111 |8080|bcd
    6600 |1111 |8080|bcd
    7700 |1111 | |
    8800 |1111 | |
    |2222 |8888|def
    |3333 | |

    I used this query while I am working on my computer which is using mySQL ver 4.0.13:

    SELECT distinct a.COMPANY,
    a.ACCOUNT,
    a.PROJECT,
    projectMap.DESCRIPTION
    FROM myTable b, myTable a left join projectMap on a.PROJECT=projectMap.PROJECT
    WHERE a.ACCOUNT = b.ACCOUNT and a.PROJECT<>b.PROJECT
    UNION

    SELECT
    ' ', myTable.PROJECT,
    myTable.ACCOUNT,
    projectMap.DESCRIPTON,
    FROM
    myTable left join projectMap on
    myTable.PROJECT=projectMap.PROJECT group by myTable.ACCOUNT having count(distinct myTable.PROJECT)=1

    However when I brought it over to another computer which was using ver 4.0.12, the results I got was wrong.
    I will get an extra line where account 1111 is also grouped together.
    Company| Account| Project|Description
    --------------------------------
    3311 |9999 |8181|abc
    1100 |9999 |8080|bcd
    2200 |9999 |8181|abc
    3300 |9999 |8282|cde
    4400 |1111 |8080|bcd
    5500 |1111 |8080|bcd
    6600 |1111 |8080|bcd
    7700 |1111 | |
    8800 |1111 | |
    |1111 |8080|bcd <-------extra line???
    |2222 |8888|def
    |3333 | |

    I think it is some bug in ver 4.0.12 that causes this, however , I cannot upgrade the version right now as I am afraid it may affect the rest of the queries which have been developed with this version. Is there anyway to correct this query to make it work?

    Thanks.
    Last edited by justfreak; 04-05-04 at 05:28.

Posting Permissions

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