CODEWITHSUNDEEP
sqlsql-serverjoinmany-to-many
user1860447
user1860447

Reputation: 1434

SQL Server query many-to-many join

I have a table which has customer orders. it can contain multiple records for the customer. i have another table that has details or features about the customer: age, region, gender etc. This table has multiple records per table. But only one records per customer feature. Like 

cust_id, feature_id, value ; 
cust_id, feature_id, vaule; 
cust_id, feature_id, value. 
...
...
etc.

cust_id and feature_id is the primary key on the table. Say for example feature_id for gender is 200, feature_id for age is 201, feature_id for region is 230 etc

Now I have create a report that has to to total number of customers in some of the demographic. So I wrote the following query:

SELECT * 
FROM   CUST_ORDER co 
       JOIN FEATURE_VALUES fv 
         ON fv.CUST_ID = co.CUST_ID 
WHERE  ( fv.FEATURE_ID = 200 
         AND fv.VALUE = 1 ) 
       AND ( fv.FEATURE_ID = 201 
             AND fv.VALUE > 25 ) 
       AND ( fv.FEATURE_ID = 230 
             AND fv.VALUE > 3 )

This does not return any records and I know why but dont know how to get the results. Would appreciate any help or direction.

Here is some sample data

1   200 1   
1   201 40
1   230 3
2   200 2   
2   201 27
2   230 2
etc

Thanks

Upvotes: 0

Views: 1733

Answers (1)

Marco
Marco

Reputation: 57573

You must use OR, not AND.

SELECT * FROM cust_order co
INNER JOIN feature_values fv ON fv.cust_id = co.cust_id
WHERE
   (fv.feature_id = 200 and fv.value = 1 )
OR (fv.feature_id = 201 and fv.value > 25 )
OR (fv.feature_id = 230 and fv.value > 3 )

Using AND you are asking results that at the same time fullfill those conditions, while this is impossible!

UPDATE: new query after user comment:

SELECT * FROM cust_order co
INNER JOIN feature_values fv1 ON fv1.cust_id = co.cust_id
INNER JOIN feature_values fv2 ON fv2.cust_id = co.cust_id
INNER JOIN feature_values fv3 ON fv3.cust_id = co.cust_id
WHERE
    (fv1.feature_id = 200 and fv1.value = 1  )
AND (fv2.feature_id = 201 and fv2.value > 25 )
AND (fv3.feature_id = 230 and fv3.value > 3  )

Upvotes: 2

Related Questions