mysql - SQL Join Between Two Tables -

below 2 tables structure,

mysql> desc catalog_product_entity; +------------------+----------------------+------+-----+---------------------+----------------+ | field            | type                 | null | key | default             |          | +------------------+----------------------+------+-----+---------------------+----------------+ | entity_id        | int(10) unsigned     | no   | pri | null                | auto_increment | | entity_type_id   | smallint(8) unsigned | no   | mul | 0                   |                | | attribute_set_id | smallint(5) unsigned | no   | mul | 0                   |                | | type_id          | varchar(32)          | no   |     | simple              |                | | sku              | varchar(64)          | yes  | mul | null                |                | | created_at       | datetime             | no   |     | 0000-00-00 00:00:00 |                | | updated_at       | datetime             | no   |     | 0000-00-00 00:00:00 |                | | has_options      | smallint(1)          | no   |     | 0                   |                | | required_options | tinyint(1) unsigned  | no   |     | 0                   |                | | user_id          | mediumint(11)        | no   |     | null                |                | +------------------+----------------------+------+-----+---------------------+----------------+ 10 rows in set (0.00 sec)  mysql> desc catalog_product_entity_varchar; +----------------+-----------------------+------+-----+---------+----------------+ | field          | type                  | null | key | default |          | +----------------+-----------------------+------+-----+---------+----------------+ | value_id       | int(11)               | no   | pri | null    | auto_increment | | entity_type_id | mediumint(8) unsigned | no   |     | 0       |                | | attribute_id   | smallint(5) unsigned  | no   | mul | 0       |                | | store_id       | smallint(5) unsigned  | no   | mul | 0       |                | | entity_id      | int(10) unsigned      | no   | mul | 0       |                | | value          | varchar(255)          | no   |     |         |                | +----------------+-----------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) 

i want join 2 tables based on following criteria,

i want fetch values form second table catalog_product_entity_varchar entity_id should match first table catalog_product_entity , user_id first table 12.

below sql query -

select entity_id, value, count(*) count      catalog_product_entity_varchar cpev      inner join catalog_product_entity cpe on cpe.entity_id = cpev.entity_id      attribute_id = '960'      group value      having (count(*) > 1)      limit 1 

updates -

i have updated sql query, shows 1 result not multiple results.

select catalog_product_entity_varchar.entity_id,        catalog_product_entity_varchar.value, count(*) count,        catalog_product_entity.entity_id, catalog_product_entity.user_id catalog_product_entity_varchar,      catalog_product_entity attribute_id = '960'   , catalog_product_entity_varchar.entity_id = catalog_product_entity.entity_id   , catalog_product_entity.user_id = '12' group value having (count(*) > 1) limit 1; 

current result -

+-----------+---------------+-------+-----------+---------+ | entity_id | value         | count | entity_id | user_id | +-----------+---------------+-------+-----------+---------+ |  11062505 | 05-04100262-r |     2 |  11062505 |      12 | +-----------+---------------+-------+-----------+---------+ 1 row in set (36.67 sec) 

expected result -

+-----------+----------------+--------------+----------+-----------+---------------+ | value_id  | entity_type_id | attribute_id | store_id | entity_id | value         | +-----------+----------------+--------------+----------+-----------+---------------+ |  63606647 |             10 |          960 |        0 |  11062505 | 05-04100262-r | | 149826537 |             10 |          960 |        0 |  19987372 | 05-04100262-r | +-----------+----------------+--------------+----------+-----------+---------------+ 2 rows in set (12.84 sec) 

and can optimise query because current query taking time

what seem require simple join, far simpler example sql have posted.

something this:-

select cpev.value_id,         cpev.entity_type_id,         cpev.attribute_id,         cpev.store_id,         cpev.entity_id,         cpev.value catalog_product_entity_varchar cpev  inner join catalog_product_entity cpe on cpe.entity_id = cpev.entity_id   cpe.user_id = 12 

however suspect have missed in requirements.

edit

i think trying find values occur more once, , bring records have value. if use sub query values used more once, , join against main tables again:-

select cpev.value_id,         cpev.entity_type_id,         cpev.attribute_id,         cpev.store_id,         cpev.entity_id,         cpev.value catalog_product_entity_varchar cpev  inner join catalog_product_entity cpe on cpe.entity_id = cpev.entity_id  inner join (     select cpev.value,              count(*) value_count      catalog_product_entity_varchar cpev      inner join catalog_product_entity cpe on cpe.entity_id = cpev.entity_id      cpev.attribute_id = '960'      ,  cpe.user_id = 12     group value      having value_count > 1 ) sub0 on cpev.value = sub0.value cpev.attribute_id = '960'  ,  cpe.user_id = 12