-2

I have subscriptions table that looks roughly:

table subscriptions
id (auto generated id)
company_id
city
state
subscription_date

I would like to find records that:

A) are of different company (company id) but have same city/state

B) are of different company (company id) but have same subscription date

For address, I was doing:

select distinct on (t1.company_id) t1.company_id, t2.company_id, t1.city, t1.state
from subscription t1, subscription t2
where t1.company_id <> t2.company_id
and t1.city = t2.city
and t1.state = t2.state
order by t1.company_id

Is this efficient and accurate way to do it?

closed as off-topic by Paul White, RolandoMySQLDBA, StanleyJohns, Mark Storey-Smith, dezso Jul 6 '13 at 21:09

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, RolandoMySQLDBA, StanleyJohns, Mark Storey-Smith, dezso
If this question can be reworded to fit the rules in the help center, please edit the question.

4

I guess it depends on what you consider "duplicates" but here's what I came up with.

SELECT t1.company_id, t2.company_id, t1.city, t1.state, t2.subscription_date,
CASE WHEN t1.subscription_date = t2.subscription_date
then 'DateMatch' ELSE 'City/State Match' END AS MatchType
    FROM subscriptions t1
JOIN subscriptions t2
    ON (t1.city = t2.city
    AND t1.state = t2.state)
    OR (
      t1.subscription_date = t2.subscription_date
      )
WHERE t1.company_id <> t2.company_id
order by MatchType, t1.company_id;

http://sqlfiddle.com/#!1/bb04d/10

1

I create a table like yours there is My data :

mysql> select * from tab;
+------+---------+---------+-------+-------------+
| id   | company | city    | state | date        |
+------+---------+---------+-------+-------------+
|    1 | 1       | kenitra | good  | 06/07/13    |
|    2 | 2       | kenitra | good  | 08/08/13    |
|    3 | 3       | rabat   | good  | 06/07/13    |
+------+---------+---------+-------+-------------+

and I try this code

mysql> select t1.company_id company from tab t1, tab t2 where t1.city = t2.city and t2.state = t2.state and t1.company_id <> t2.company_id;

the result is:

+---------+
| company |
+---------+
| 2       |
| 1       |
+---------+

and for this :

 mysql> select t1.company_id company from tab t1, tab t2 where t1.date = t2.date and t1.comp
        any_id <> t2.company_id;

the result is :

+---------+
| company |
+---------+
| 3       |
| 1       |
+---------+

I hope this will be helpful for you

Not the answer you're looking for? Browse other questions tagged or ask your own question.