Mysql Query within the same table

justfreak · #1 (**permalink**) 03-24-04, 05:06

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.

r937 · #2 (**permalink**) 03-24-04, 07:33

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(*) > 1

justfreak · #3 (**permalink**) 03-24-04, 20:42

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.

Pat Phelan · #4 (**permalink**) 03-24-04, 21:45

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.company, a.account, a.project

   FROM  myTable AS a

   JOIN myTable AS b

      ON (b.account = a.account

      AND b.project = a.project)

   GROUP BY a.company, a.account, a.project

   HAVING 1 < Count(*);

...which should get you the same results without using a correlated sub-query.

-PatP

justfreak · #5 (**permalink**) 03-24-04, 22:03

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.

r937 · #6 (**permalink**) 03-25-04, 00:38

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