Use inner join in MySQL to implement intersect and set operation

Use inner join in MySQL to implement intersect and set operation
First, business background

We have a table designed as follows:

CREATE TABLE ' user_defined_value ' (  ' resource_id ' varchar) default NULL,  ' column_name ' varchar (default) NULL,  ' VALUE ' varchar (255) Default NULL,  KEY ' id_idx ' (' resource_id ')) Engine=myisam default Charset=utf8;

RESOURCE_ID is a unique identifier for a resource

COLUMN_NAME is a property name for a resource

Value is a property of a resource

Second, demand

Now you need to isolate the attribute a= ' 1 ' from the table and the resource ID of the property b= ' 2 '

Third, analysis

What you need to check here is that the resource IDs of both properties are met, and if so, SQL is simple

SELECT DISTINCT resource_id from User_defined_value WHERE (column_name = ' A ' and VALUE in (' 1 '))  OR (column_name = ' B ') and VALUE in (' 2 '))

When it is a relationship, it is not easy to replace the or keyword with and, you need to first identify the resources that satisfy each attribute, and then take the set

On Baidu found that SQL Server and Oracle support intersect, can directly two the same structure of the query results intersection, and MySQL does not intersect keyword, then how can we implement it?

Iv. using inner JOIN xxx using XXX

Select DISTINCT resource_id from User_defined_value INNER joins (select DISTINCT resource_id from User_defined_value where< c1/> (column_name = ' A ' and VALUE in (' 1 '))] t0 USING (resource_id) INNER JOIN (SELECT DISTINCT resource_id from User_defi Ned_value WHERE  (column_name = ' B ' and VALUE in (' 2 '))) T1 USING (resource_id)

The first sentence will identify all the resource IDs in the table.

Second sentence or isolate the resource ID of the attribute a= ' 1 ' and intersect with the query result of the first sentence

The third sentence or isolate the resource ID of the attribute b= ' 2 ' and intersect with the previous query result

In this way, you can also continue to add other properties

Copy to Google TranslateTranslation Resultsor