[SQL] Matching One to many relationship, difficult.

Hi,

I shall try to be short and to the point, this is a problem that no
one IRL has yet been able to help me with thus far. Yet it feels as
thought this is something that many people must tackle often.

There are three tables with this simple structure:
________________________
# items
item_id | name

# labels
label_id | name

# labels_relationship
relationship_id | label_id | item_id
________________________

Items contains rows of unique 'items'. And each item can have X number
of labels related to them; by using the relations table
"labels_relationship" which simply lists all the relationships between
a label and item with no limitations as to how many labels can be
assigned to an item.

What I need to fetch from the DB, is all items, that has a
relationship with a predefined set of "labels" saved in the
labels_relationship-table.

I must be able to fetch the items that has an entry in
labels_relationship for i.e: label_id 1 AND label_id 2 AND label_id 3.
If the item does not have an entry for ALL of these labels in
labels_relationship it should be filtered out (not be selected).

My problem arises due to the fact that I have to match against
multiple rows in the same table. Me and several others have
experimented with several different approaches, and the latest theory
was that; if one create virtual columns for each label, it would be a
simple task of constructing a WHERE label_id=1 AND label_id=2 etc. But
that experiment ended up in the wall as well. All our Inner join-tests
also failed miserably.

I and my project group of fellow students need help from someone more
experienced in these things.

All replies, constructive pointers and other advice will be deeply
appreciated!

Re: [SQL] Matching One to many relationship, difficult.

well, the following strikes me as too inflexible, since it is specific to a
group of exactly 3 labels - but maybe it will spark ideas that help you come
up with a better solution:

SELECT tblItems.item_id, tblLabels_relationship.label_id
FROM tblItems INNER JOIN tblLabels_relationship ON tblItems.item_id =
tblLabels_relationship.item_id
WHERE (((tblItems.item_id) In (SELECT tblItems.item_id
FROM tblItems INNER JOIN tblLabels_relationship ON tblItems.item_id =
tblLabels_relationship.item_id
WHERE (((tblItems.item_id) In (SELECT tblItems.item_id
FROM tblItems INNER JOIN tblLabels_relationship ON tblItems.item_id =
tblLabels_relationship.item_id
WHERE (((tblLabels_relationship.label_id)=1));)) AND
((tblLabels_relationship.label_id)=2));
)) AND ((tblLabels_relationship.label_id)=3));

hth


"Varghjärta" wrote in message
news:1191979636.292173.159400@22g2000hsm.googlegroups.com...
> Hi,
>
> I shall try to be short and to the point, this is a problem that no
> one IRL has yet been able to help me with thus far. Yet it feels as
> thought this is something that many people must tackle often.
>
> There are three tables with this simple structure:
> ________________________
> # items
> item_id | name
>
> # labels
> label_id | name
>
> # labels_relationship
> relationship_id | label_id | item_id
> ________________________
>
> Items contains rows of unique 'items'. And each item can have X number
> of labels related to them; by using the relations table
> "labels_relationship" which simply lists all the relationships between
> a label and item with no limitations as to how many labels can be
> assigned to an item.
>
> What I need to fetch from the DB, is all items, that has a
> relationship with a predefined set of "labels" saved in the
> labels_relationship-table.
>
> I must be able to fetch the items that has an entry in
> labels_relationship for i.e: label_id 1 AND label_id 2 AND label_id 3.
> If the item does not have an entry for ALL of these labels in
> labels_relationship it should be filtered out (not be selected).
>
> My problem arises due to the fact that I have to match against
> multiple rows in the same table. Me and several others have
> experimented with several different approaches, and the latest theory
> was that; if one create virtual columns for each label, it would be a
> simple task of constructing a WHERE label_id=1 AND label_id=2 etc. But
> that experiment ended up in the wall as well. All our Inner join-tests
> also failed miserably.
>
> I and my project group of fellow students need help from someone more
> experienced in these things.
>
> All replies, constructive pointers and other advice will be deeply
> appreciated!
>

Re: Matching One to many relationship, difficult.

On 10 okt, 03:27, Varghjärta wrote:
> Hi,
>
> I shall try to be short and to the point, this is a problem that no
> one IRL has yet been able to help me with thus far. Yet it feels as
> thought this is something that many people must tackle often.
>
> There are three tables with this simple structure:
> ________________________
> # items
> item_id | name
>
> # labels
> label_id | name
>
> # labels_relationship
> relationship_id | label_id | item_id
> ________________________
>
> Items contains rows of unique 'items'. And each item can have X number
> of labels related to them; by using the relations table
> "labels_relationship" which simply lists all the relationships between
> a label and item with no limitations as to how many labels can be
> assigned to an item.
>
> What I need to fetch from the DB, is all items, that has a
> relationship with a predefined set of "labels" saved in the
> labels_relationship-table.
>
> I must be able to fetch the items that has an entry in
> labels_relationship for i.e: label_id 1 AND label_id 2 AND label_id 3.
> If the item does not have an entry for ALL of these labels in
> labels_relationship it should be filtered out (not be selected).
>
> My problem arises due to the fact that I have to match against
> multiple rows in the same table. Me and several others have
> experimented with several different approaches, and the latest theory
> was that; if one create virtual columns for each label, it would be a
> simple task of constructing a WHERE label_id=3D1 AND label_id=3D2 etc. But
> that experiment ended up in the wall as well. All our Inner join-tests
> also failed miserably.
>
> I and my project group of fellow students need help from someone more
> experienced in these things.
>
> All replies, constructive pointers and other advice will be deeply
> appreciated!

You want to select items, based upon the number of matches in
labels_relationship.
First count those matches (I'm pretty sure you know the summary
function to use). Then apply a select (but it's called differently) to
the
summarised results.

Re: Matching One to many relationship, difficult.

On 10 Oct, 02:27, Varghjärta wrote:
> Hi,
>
> I shall try to be short and to the point, this is a problem that no
> one IRL has yet been able to help me with thus far. Yet it feels as
> thought this is something that many people must tackle often.
>
> There are three tables with this simple structure:
> ________________________
> # items
> item_id | name
>
> # labels
> label_id | name
>
> # labels_relationship
> relationship_id | label_id | item_id
> ________________________
>
> Items contains rows of unique 'items'. And each item can have X number
> of labels related to them; by using the relations table
> "labels_relationship" which simply lists all the relationships between
> a label and item with no limitations as to how many labels can be
> assigned to an item.
>
> What I need to fetch from the DB, is all items, that has a
> relationship with a predefined set of "labels" saved in the
> labels_relationship-table.
>
> I must be able to fetch the items that has an entry in
> labels_relationship for i.e: label_id 1 AND label_id 2 AND label_id 3.
> If the item does not have an entry for ALL of these labels in
> labels_relationship it should be filtered out (not be selected).
>
> My problem arises due to the fact that I have to match against
> multiple rows in the same table. Me and several others have
> experimented with several different approaches, and the latest theory
> was that; if one create virtual columns for each label, it would be a
> simple task of constructing a WHERE label_id=3D1 AND label_id=3D2 etc. But
> that experiment ended up in the wall as well. All our Inner join-tests
> also failed miserably.
>
> I and my project group of fellow students need help from someone more
> experienced in these things.
>
> All replies, constructive pointers and other advice will be deeply
> appreciated!

What is the field relationship_id for?

Re: Matching One to many relationship, difficult.

On Oct 9, 6:27 pm, Varghjärta wrote:
> Hi,
>
> I shall try to be short and to the point, this is a problem that no
> one IRL has yet been able to help me with thus far. Yet it feels as
> thought this is something that many people must tackle often.
>
> There are three tables with this simple structure:
> ________________________
> # items
> item_id | name
>
> # labels
> label_id | name
>
> # labels_relationship
> relationship_id | label_id | item_id
> ________________________
>
> Items contains rows of unique 'items'. And each item can have X number
> of labels related to them; by using the relations table
> "labels_relationship" which simply lists all the relationships between
> a label and item with no limitations as to how many labels can be
> assigned to an item.
>
> What I need to fetch from the DB, is all items, that has a
> relationship with a predefined set of "labels" saved in the
> labels_relationship-table.
>
> I must be able to fetch the items that has an entry in
> labels_relationship for i.e: label_id 1 AND label_id 2 AND label_id 3.
> If the item does not have an entry for ALL of these labels in
> labels_relationship it should be filtered out (not be selected).
>
> My problem arises due to the fact that I have to match against
> multiple rows in the same table. Me and several others have
> experimented with several different approaches, and the latest theory
> was that; if one create virtual columns for each label, it would be a
> simple task of constructing a WHERE label_id=3D1 AND label_id=3D2 etc. But
> that experiment ended up in the wall as well. All our Inner join-tests
> also failed miserably.
>
> I and my project group of fellow students need help from someone more
> experienced in these things.
>
> All replies, constructive pointers and other advice will be deeply
> appreciated!

Try something like this:

SELECT i.item_id,i.name
FROM items i
WHERE EXISTS (SELECT lr1.item_id
FROM labels_relationship lr1
WHERE i.item_id =3D lr1.item_id
AND
lr1.label_id IN (SELECT lr2.label_id
FROM labels_relationship lr2
WHERE lr2.label_id IN (484,501,505))
GROUP BY lr1.item_id
HAVING COUNT(label_id) =3D 3)

It uses group by and counting the label_ids in the groups.

This problem is a "relational division" problem. Google for "sql
relational division" for more info on the topic. Relational division
problems are rare and can be quite difficult.