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

    Unanswered: Mysql Query within the same table

    Hi, this is an example of the table I am working with.

    Company| Account| Project
    --------------------------------
    1100 |9999 |8080
    2200 |9999 |8181
    3300 |9999 |8282
    4400 |1111 |8080
    5500 |1111 |8080
    6600 |1111 |8080

    I need a query to get only the entries with the same Account and Project
    Is this possible?

    Thank you.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    select t1.Company
         
    t1.Account
         
    t1.Project
      from yourtable t1
    inner
      join yourtable t2
        on t1
    .Account t2.Account
       
    and t1.Project t2.Project
    group
        by t1
    .Company
         
    t1.Account
         
    t1.Project
    having count
    (*) > 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    6
    Thank you.
    Perhaps my example was not good enough.

    By using this code:
    select t1.Company
    , t1.Account
    , t1.Project
    from yourtable t1
    inner
    join yourtable t2
    on t1.Account = t2.Account
    and t1.Project = t2.Project
    group
    by t1.Company
    , t1.Account
    , t1.Project
    having count(*) > 1

    If my table has one value like this
    Company| Account| Project
    --------------------------------
    3311 |9999 |8181
    1100 |9999 |8080
    2200 |9999 |8181
    3300 |9999 |8282
    4400 |1111 |8080
    5500 |1111 |8080
    6600 |1111 |8080

    my result would have been:
    Company| Account| Project
    --------------------------------
    3311 |9999 |8181
    2200 |9999 |8181
    4400 |1111 |8080
    5500 |1111 |8080
    6600 |1111 |8080

    when I only want the entries if and only if all the projects under one account is the same.

    I need to be able to get this
    Company| Account| Project
    --------------------------------
    4400 |1111 |8080
    5500 |1111 |8080
    6600 |1111 |8080

    as a result instead.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That is actually what I thought you meant in your original posting, but usually when Rudy comes up with a solution that makes no sense to me it is because he has a better understanding of what the user really wanted. My suggestion would be:
    PHP Code:
    SELECT a.*
       
    FROM myTable AS a
       WHERE 1 
    < (SELECT Count(*)
          
    FROM myTable AS b
          WHERE  b
    .account a.account
             
    AND b.project a.project); 
    I think this will do what you are asking for, or at least it should produce the sample result set you've posted.

    P.S. Note that this assumes you are running a version of MySQL that supports sub-queries. If not, you'd need to use:
    PHP Code:
    SELECT a.companya.accounta.project
       FROM  myTable 
    AS a
       JOIN myTable 
    AS b
          ON 
    (b.account a.account
          
    AND b.project a.project)
       
    GROUP BY a.companya.accounta.project
       HAVING 1 
    Count(*); 
    ...which should get you the same results without using a correlated sub-query.

    -PatP
    Last edited by Pat Phelan; 03-24-04 at 22:49.

  5. #5
    Join Date
    Mar 2004
    Posts
    6
    Hi,
    Thanks for helping out. However I got the same result as when I use the code given by Rudy.

    I have tried to get a list of Account which has differing Project, after which I will try to get all entries whose Account is not in this list.
    Here is my code:

    select
    company,account,project
    from
    mytable
    where account not in (select distinct
    T1.account
    from mytable T1, mytableT2
    where T1.account = T2.account and T1.project<>T2.project
    )

    but I get syntax error when I try to run it.
    The version of mysql I am using is mysql-3.23.38.
    I suspect this version cannot support subquery?
    Is there anyway to rewrite this code?

    Thank you.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, thanks for the kind words, i thought it very complimentary that your second query was exactly the same as mine

    now that i understand the requirements...
    PHP Code:
    select t1.Company
         
    t1.Account
         
    t1.Project
      from yourtable t1
     where Account in
           
    select Account
               from yourtable
             group
                 by Account
             having count
    (distinct Project) = 1
           

    and i'm sorry, i'm too tired to try the join, and i fear it may require a three-way self join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2004
    Posts
    6
    Thank you everybody for your help.

    Yup Rudy, it worked Thanks a lot.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by r937
    and i'm sorry, i'm too tired to try the join, and i fear it may require a three-way self join
    Obviously I'm further gone from lack of sleep than I realized. Sorry!

    They keep telling me that things are going to get better...

    -PatP

Posting Permissions

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