Using SharePoint's Data Query Webpart to link two lists

Question

I have two SharePoint Lists: A & B. List A has a column where the user can add multilple references (displayed as hyperlinks) for each entry to entries in B

A:                            B:
... | RefB  | ...             Name | OtherColumns....
-----------------             -----------------------
... | B1    | ...             B1   |
... | B2,B3 | ...             B2   |
... | B1,B3 | ...             B3   |

Now I want to display all entries from list B that are referenced by an (specific) entry in A. I.e: I set the filter to [Entry 2] and the Web part displays all the stuff from entries B2 and B3. Is this even possible?

Answer 1

I think the problem you've got which is ruining some of the way's I'm thinking of solving it is that the RefB column is multi-valued. You may have some joy doing filtering with the DataView but it might get messy fast, as you try to split RefB on the comma and compare against the resulting array of values.

I think the problem could be made easier by having only a single value in the RefB column.

Three solutions come to mind.

1. Have only one value in RefB per item in Table A and repeat the other fields in Table A. You'd have to accept some data redundancy and would need to be careful with data entry.

2. The normal relational database way of solving your data redundancy problem would be to have a 3rd table joining tabe A to table B. If you're not familiar with relational database techniques, there are lots of straight-forward tutorials on data normalisation on the net. While there's some more work, it may lead to a cleaner solution. Be careful when trying to fake a relational database within SharePoint though - it's not meant for relational data. You may be better off using a SQL database.

3. Put everything in one table, though I think you've already ruled this one out.