[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_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!

[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_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" <va********@gmail.comwrote in message
news:11**********************@22g2000hsm.googlegro ups.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!

[floronDBA]

On 10 okt, 03:27, Varghjärta <[e-mail]:

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!

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.

[Captain Paralytic]

On 10 Oct, 02:27, Varghjärta <[e-mail]:

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!

What is the field relationship_id for?

[Neil]

On Oct 9, 6:27 pm, Varghjärta <[e-mail]:

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!

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 = 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) = 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.