Two tables and select distinct values from joined table

Question

I have a products table (Product) and all products have assigned filters in a filters join table (Product_Filter_Join), (Classic, Round, Wood, moodern, dining, etc.). My problem is that I cannot figure out how to show the -distinct- values from the table Product and when listing this products all filter values have to match.

+---------------------+-------------+-----------+
| * Product_Table     |             |           |
| productID           | productName |           |
| 1                   | table 1     |           |
| 2                   | table 2     |           |
| 3                   | table 3     |           |
| 4                   | table 4     |           |
|                     |             |           |
| * Filter_Join_Table |             |           |
| FilterJoinID        | filterID    | productID |
| 1                   | 11          | 1         |
| 2                   | 12          | 1         |
| 3                   | 14          | 1         |
| 4                   | 11          | 2         |
| 5                   | 11          | 3         |
| 6                   | 12          | 3         |
| 7                   | 14          | 3         |
| 8                   | 13          | 4         |
|                     |             |           |
| ** Filter_Table     |             |           |
| filterID            | FilterName  |           |
| 11                  | Classic     |           |
| 12                  | Wood        |           |
| 13                  | modern      |           |
| 14                  | dining      |           |
+---------------------+-------------+-----------+

In this scheme the result will be: Table 1, Table 3 because they are the only products that share all the filters.

Hope that someone can provide some good, simple solutions on my problem.

I'm not clear on what you're asking for. Please provide your schemas and an example of your expected results. — Kritner, Mar 06 '15 at 18:21
Also, please add a tag indicating which RDBMS you're using (Oracle, SQL Server, MySQL, etc.). — Paul Rowe, Mar 06 '15 at 18:26
You might need to use one of the techniques listed here: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Isaac Moses, Mar 06 '15 at 20:04

score 1 · Answer 1 · answered Mar 07 '15 at 08:50

1

If you need a random row of Product_Filter_Join

SELECT TOP 5 p.*
FROM Product p JOIN Product_Filter_Join f ON f.ProductID = p.ProductID
WHERE p.Productname LIKE 'table'
AND NOT EXISTS ( SELECT 'a'
                 FROM Product_Filter_Join f2
                 WHERE f2.ProductID = f.ProductID
                 AND f2.FilterJoinID > f.FilterJoinID 
                 )

always if I have understood correctly you problem

answered Mar 07 '15 at 08:50

Javaluca

859
1
6
13

thank you so much, this really solved my problem. Spot on! But as far as i can see your solution will not display records randomly. One could use `Order By NewID()`. – Espen S. Mar 08 '15 at 21:50
I have been trying out this solution now and i encountered a challenge. Hope that @Javaluca or someone will kindly contribute. In the solution provided above from Javaluca products with the name 'table' that share some of the FilterJoinID's will be listed, thats fine. Now I'm trying to figure out how to use this to list products that match ALL filterjoinID's If a product named 'table' has the filters attached like classic, round, wood and brown i just want to list products that have the name 'table' and the same four filter values as above. – Espen S. Mar 11 '15 at 22:57

Two tables and select distinct values from joined table

1 Answers1