CODEWITHSUNDEEP
mysqlsqlsql-serverpostgresqlms-access
Alvin Furyadi
Alvin Furyadi

Reputation: 23

SQL query to show data that exactly match criteria in another table's column

By this time I am implementing a system that perform matching between 3 tables and I am really need your help by now, suppose I have the following three tables:

Table1: Relation between name and item

User        Item
=====================
John Doe    Apple
John Doe    Orange
John Doe    Cat
John Doe    Dog
John Doe    Fish
Anna Sue    Apple
Anna Sue    Orange
Robinson    Banana
Robinson    Vessel
Robinson    Car


Table2: To categorized the item 

Item Type   Item
==================
Fruit       Apple
Fruit       Orange
Fruit       Banana
Animal      Cat
Animal      Dog
Vehicle     Vessel
Vehicle     Car
Vehicle     Truck


Table3: Matching of Item

Match ID    Item Type
======================
M001        Fruit
M001        Animal
M002        Fruit
M002        Vehicle


All I want to ask that how I could only show all users that having all criteria that exactly match with the designated match ID
For this case user John Doe that fulfill all criterias of having Item within the Fruit And Animal that relationship designated in the Match ID with the following format:

User            Match ID    Item Type   Item
================================================
John Doe        M001        Fruit       Apple
John Doe        M001        Fruit       Orange
John Doe        M001        Animal      Cat
John Doe        M001        Animal      Dog
Robinson        M002        Fruit       Banana
Robinson        M002        Vehicle     Vessel
Robinson        M002        Vehicle     Car

All solutions are highly appreciated, therefore thank you for your help.

Upvotes: 2

Views: 2431

Answers (5)

hanzpk
hanzpk

Reputation: 123

You have a flaw in table3: 2 IDs (assuming itemtype is going to be the foreign key) for 1 data ( fruit ), otherwise the query would be:

select *
from table1 
   join table2 using (item)
   join table3 using (itemtype)

assuming, of course

  1. itemtype is table 2 primary key

  2. itemtype is table 3 foreign key to table 2

  3. item is table 1 foreign key to table 2

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

For MySQL

fiddle

select t1.User,t3.MatchID,t3.ItemType as ItemType,t2.Item as Item 
from Table1 t1
inner join Table2 t2 on t1.Item = t2.Item
inner join Table3 t3 on t3.ItemType = t2.ItemType
inner join
(select user,MatchID
from 
(SELECT GROUP_CONCAT(ItemType ORDER BY ItemType) AS typesTomatch , MatchID
FROM Table3 GROUP BY MatchID) abc
inner join
(Select a.User, group_concat(distinct b.ItemType ORDER BY b.ItemType)
as typesofpeople
from Table1 As a
inner join Table2 As b on a.Item = b.Item
group by a.User order by b.ItemType) def
on abc.typesTomatch = def.TYPESOFPEOPLE) xyz
on xyz.User = t1.User and xyz.MatchID = t3.MatchID;

Upvotes: 0

spencer7593
spencer7593

Reputation: 108410

Here's one way to do it, but this is going to be a light dimming query on large sets.

SQL Fiddle demo here: http://sqlfiddle.com/#!2/63cd2/1

SELECT ui.user_name
     , tm.match_id
     , tm.item_type
     , ui.item
  FROM (SELECT uu.user_name
             , tm.match_id
             , COUNT(DISTINCT tm.item_type) AS cnt_item_type
          FROM (SELECT u.user_name FROM user_item u GROUP BY u.user_name) uu
         CROSS
          JOIN type_match tm
         GROUP BY uu.user_name, tm.match_id 
       ) n
  JOIN (SELECT hui.user_name
             , htm.match_id
             , COUNT(DISTINCT htm.item_type) AS cnt_item_type
          FROM user_item hui
          JOIN item_type hit ON hit.item = hui.item
          JOIN type_match htm ON htm.item_type = hit.item_type
         GROUP BY hui.user_name, htm.match_id
       ) h
    ON h.cnt_item_type = n.cnt_item_type
   AND h.match_id      = n.match_id
   AND h.user_name     = n.user_name
  JOIN user_item ui
    ON ui.user_name = h.user_name
  JOIN item_type it
    ON it.item = ui.item
  JOIN type_match tm
    ON tm.item_type = it.item_type
   AND tm.match_id = h.match_id
 ORDER
    BY ui.user_name
     , tm.match_id
     , tm.item_type
     , ui.item

The inline view aliased as n represents what a user needs to have, all the item_type that are required in order to satisfy each match_id.

The inline view aliased as h represents what user actually has, all of the item_type that user has for each match_id.

We can get a count of the distinct item_type in each of those sets, and compare the counts. If the count is equal, then we know the user has all of the required item_type for that match_id.

Finally, we can join that back to the item a user actually has, so we can display a result.

(Again, this is going to be horrendous light dimmer, although indexes will help some.)

Upvotes: 1

kabijoy
kabijoy

Reputation: 303

Use this, its worked :

select t1.[User],t3.matchid,t3.item_type,t1.item from table3 t3 left join table2 t2 on t3.item_type=t2.Item_type left join table1 t1 on t2.Item=t1.Item    where t1.[user]='JohnDoe' and t3.MatchId='m001' group by t1.[user],t1.item,t3.MatchId,t3.Item_Type

Upvotes: 0

Luis LL
Luis LL

Reputation: 2993

try this:

SELECT [User],  [Match ID], [Item Type],[Item]
From table1 
Inner join table2 on table1.item = table2.item
Inner join table3 on table2.[item type]= table3.[item type]
Where [User] = 'SOME USER NAME' AND table2.[item type] = 'SOME ITEM TYPE' AND table1.Item = 'SOME ITEM'

Upvotes: 0

Related Questions