[SQL] Matching One to many relationship, difficult.

[=?iso-8859-1?B?VmFyZ2hq5HJ0YQ==?=]

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_relation ship
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_relatio nship" 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_relation ship-table.

I must be able to fetch the items that has an entry in
labels_relation ship 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_relation ship 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!

[tina]

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_i d, tblLabels_relat ionship.label_i d
FROM tblItems INNER JOIN tblLabels_relat ionship ON tblItems.item_i d =
tblLabels_relat ionship.item_id
WHERE (((tblItems.ite m_id) In (SELECT tblItems.item_i d
FROM tblItems INNER JOIN tblLabels_relat ionship ON tblItems.item_i d =
tblLabels_relat ionship.item_id
WHERE (((tblItems.ite m_id) In (SELECT tblItems.item_i d
FROM tblItems INNER JOIN tblLabels_relat ionship ON tblItems.item_i d =
tblLabels_relat ionship.item_id
WHERE (((tblLabels_re lationship.labe l_id)=1));)) AND
((tblLabels_rel ationship.label _id)=2));
)) AND ((tblLabels_rel ationship.label _id)=3));

hth
"Varghjärta " <va********@gma il.comwrote in message
news:11******** **************@ 22g2000hsm.goog legroups.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_relation ship
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_relatio nship" 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_relation ship-table.

I must be able to fetch the items that has an entry in
labels_relation ship 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_relation ship 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!

[floronDBA]

On 10 okt, 03:27, Varghjärta <varghja...@gma il.comwrote:

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_relation ship
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_relatio nship" 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_relation ship-table.

I must be able to fetch the items that has an entry in
labels_relation ship 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_relation ship 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!

You want to select items, based upon the number of matches in
labels_relation ship.
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.

[Captain Paralytic]

On 10 Oct, 02:27, Varghjärta <varghja...@gma il.comwrote:

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_relation ship
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_relatio nship" 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_relation ship-table.

I must be able to fetch the items that has an entry in
labels_relation ship 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_relation ship 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!

What is the field relationship_id for?

[Neil]

On Oct 9, 6:27 pm, Varghjärta <varghja...@gma il.comwrote:

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_relation ship
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_relatio nship" 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_relation ship-table.

I must be able to fetch the items that has an entry in
labels_relation ship 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_relation ship 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!

Try something like this:

SELECT i.item_id,i.nam e
FROM items i
WHERE EXISTS (SELECT lr1.item_id
FROM labels_relation ship lr1
WHERE i.item_id = lr1.item_id
AND
lr1.label_id IN (SELECT lr2.label_id
FROM labels_relation ship lr2
WHERE lr2.label_id IN (484,501,505))
GROUP BY lr1.item_id
HAVING COUNT(label_id) = 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.