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 design such as the following:

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

The need to check is that the same time the resource ID of the two properties, assuming or the relationship, then SQL is very easy

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 Orkeyword with and. The resources that satisfy each attribute need to be identified first. Re-fetch and set

On Baidu found that SQL Server and Oracle support intersect. Can directly intersect two of the same structure query results, and MySQL is not intersectkeyword. So what can we do?

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 the resource ID of the attribute b= ' 2 ' is isolated. and intersect with the previous query results

In this way, you can continue to add other properties

Copy to Google TranslateTranslation Resultsor

Use inner join in MySQL to implement intersect and set operation