I only know of one Sahil Malik, so that's safe. But I know two people named Andrew Connell (true story!). Can this trick be done by joining on the basis of a numerical ID field, rather than using the person's name?

Hey Steve - Yes you can use IDs as well. But I bet Andrew will still be buying Mansions and Mercedeses!! Sahil

This is cool! Do you know if it's possible to aggregate values from the subview; for example to display only the number of orders for each customer? Mark

Great. I have same question as Mark. I see that it is done at dashboard.aspx at the budget and trace for multiple project template to get the count from the subview. I cannot figure it out how it is done. Do you have any idea?

Hi, Do you know if there is a way to filter the data in the aggregated datasource..i.e. by a query string value (ID=2) whereby you would only show a Customer with the ID of 1? I have tried but have been unable to pass filter values to the aggregated datasource. Thanks, John Haigh

PLEASE I NEED HELP ALWAYS PROGRAME IN VB5, AND NOT WANT TO CHANGE THE VERSION. BUY NOW AN LATOP THAT HAVE WINDOWS VISTA, INCOME AND WHEN A "COMPONENTS" I DO AND CLICK ON "MICROSOFT FORM 2.0 OBJECT LIBRARY" CLICK AND DO IN OK ME HE DENIES PERMISSION. I DO THAT?

That is a great feature. I actually discovered that a couple of months ago, but now I need to do a join to accomplish a lookup from one table in order ot update another table. If you go to Data View -> Insert Data View, then you get several options when showing data from one table. the options you get are: Single Item View Multiple Item View Single Item Form Multiple Item Form New Item Form if you do the same thing on a joined datasource then you only get the first two, no form options. Do you know if something like that is possible?

What is the benefit to joining the two lists when you can achieve the same results by just creating a lookup from one list to another, grouping the customer name and then selecting a total to display?

Great post. I now have the aspx file that I can browse from my sharepoint site. Is there a way to add it as a webpart? Maybe I could have three webparts, the customer list, the order list and then the aggregate (customer-order) as a third view in a custom page with 3 columns. I'm off now to view you dnr tv sharepoint shows!

John - It *is* already a webpart. :) If you are asking, if this can now be packaged, and dropped via a browser. The answer to that unfortunately is No :(. It has to be a sharepoint designer thing. SM

Thanks for your great share. May I ask if I could post this joined data as one of my customized views on MOSS site. Or I just need to package it as a web part and insert it on MOSS site.

I'm following your instructions word for word, but the graphic representations are not showing. I would like to be able to see those as I think they will help in concluding the project I have going. Any way I can get those? Thank You,
Steve Sherman

I've created a view with a joined subview as described. It works great. I do have a problem applying conditional formatting though. I am using an html view style available in SPD (second selection). I can set up the filter conditions for the formatting (I am showing or hiding a pic based on a field value), but the pic just disappears (wether it is supposed to or not). I tried applying font and cell background changes but they don't work either. Any ideas that might help me? Thanks

Thank you very much for the post. Im using this tip to show a table with results from two joined list, for example "project" and "customer". One of my lists ("project") contains a lookup field to the other one ("Customer"). In order to avoid integrity problems I have configured that column to use de ID of the item instead of, for example the title, as you do in your post. That solves the problem but it generates another one: you have to develop custom forms for each list. I mean, you cannot present the user the default EditForm.aspx for the list "project" because the dropdown control to select the corresponding customer will show the list of IDs. As you can imagine it will be great to show here the "Title" field from the customer (or even something like ID-Title"). ¿Can anyone point me in the right direction to solve this problem with the minimun work? Actually is it any way of acomplish this using two webparts on the form connect somehow? Im a bit lost trying diferent things so, any tip you could provide will be a great help. Thank you!

Is there a way of using the context menu (View Properties, Edit Properties, Check out, Workflow etc) like seen in standard document libraries for each row? I can't seem to get this working with a Data View web part (without converting from a Lits View web part to a Data View web part, but when I add another data source it stops working).

Hi Malik,
Nice post. I have the same situation but i want to use the join feature in Add New Item/Edit Item mode. It is possible to linked 2 look up list and filter the child list after item selection in parent list ? Thank you.

good post. I got it working immediately

I am trying to perform this operation but everytime I try to access the data (show data)I receive the following message:
You do not have permission to do this operation. Ask your web site administrator to change your permissions and then try again, or log on with a user account that has this permission.
My server admin states: "There was a service pack released a very good while ago that caused the data view web par, and some otherst to not work for administrators of sharepoint sites as it should. I opened a ticket with MS on this, they confirmed it was a bug, were able to recreate the scenario in their lab, and said they were addressing it, right along with the “we don’t know when the hotfix will be out” statement." I am able to set up sharing data between lists through the sharepoint site interface and a lookup field will pull data from the other list but I am unable to get the data related to the lookup field to pass. What field should I be setting up to display this information? A text box? In other words if I select a customer in the selection field, I want other fields to populate automatically with that customers info. We are on server 2003. Any ideas?
Thank you,
Michael

I'm accustomed to joining tables in a query via SQL Server and Access and am looking to replicate the results of a simple SELECT statement, whereby the results are returned in a simple tabular form. Is it possible to replicate this via SharePoint?

Adam - Yes you can. Look into SPQuery and CAML.

Hi Sebastian, Showing data in Parent/Child Mode is possible if you use ListViewWebParts and then link them. I have done it and it works well. Thanks,
Nitin

Hi.
For example, there're two lists, one of them has a lookup field referenced to the other list's field. Is there way to retrieve information from two lists by one query? Something like this SQL statment does: "SELECT List1.field1,... List1.fieldN, List2.field1,... List2.fieldM FROM List1, List2 WHERE List1.Lookup=List2.Id".

Salik, this is very helpful, do you know how to take this a step further by having lookup fields in a list/library that are related? e.g., After the State field is choosen a subset of Cities is available to choose from when entering metadata for a document

Is it possible to make some example of using controls from Ajax Control Kit with Sharepoint Webservices to create cascading filters for lookups to Sharepoint lists? Greetz!
TSz
http://www.it-dev.pl

Just what I have been looking for - only When I click "Joined Subview" instead of giving me the Join Subview menu, it just ads the information from the second list in front of the information from the second list - onthe same line. How do I get the Join Subview Menu to pop up? Thanks!

OK - I was able to get the "Joined Subview" to come up sometimes, but even when it came up, it was only on 2 lists. What if I want to join 3 lists? Such as: List A
List B
List C Where List C has a lookup to List B and List B has a lookup to List A.

Hi, Do you know if there is a way to filter the data in the aggregated datasource..i.e. by a query string value (ID=2) whereby you would only show a Customer with the ID of 1? I have tried but have been unable to pass filter values to the aggregated datasource. Thanks, John Haigh

Hi, Thanks for an awesome post Sahil. Do you know if this would also work for lookup fields with multiple selection? E.g. what if customer column in Orders list could hold both Scott and Andrew for the order of banana in the same record. (hey Andrew could also have a banana =) Xurgum

Can I create an editable form with joined data? I want to create a editform.aspx that allows me to provide a way for the user to provide child items to a parent item (somehow grabbing the ID of the parent for the user). thank you for posts like these.

Can do the same but using two lists placed into different sites?

I'll repeat the question Tritata wrote... Did anyone managed to join tu lists in to one as we're able to do with SQL statements? Something like Tritata already wrote: "SELECT List1.field1,... List1.fieldN, List2.field1,... List2.fieldM FROM List1, List2 WHERE List1.Lookup=List2.Id".

this is a great post i just wanted to ask that i want this to be done in the sps 2003 site using spd 2007
but i m not able to get the "create a linked source" hyperlink when i open sps 2003 site in spd 2007
sujjest something ????

hi,
I used the same approach, only thing is i had "thumbnail"column of a picture column as a second list, which i wanted to use.....
now when i join the thumbnail dosent show up,,instead it gives a numeric value.....
why is that and what is the work around?

If I understand your solution, you are sending the join to a SharePoint page (URL). In a sense this is just a virtual join. How do I send the join to a new or existing SharePoint list?

I got lost at step 6. [Great, now while in the <form> tag in your ASPX, click on Data View --> Insert Data View, your UI should now say "Current Data Source - Customer - Orders" - "next insert fields to create a view".] I don't know what you mean by "in the <form> tag of you ASPX". When I Data Source Detials tab I have my Customers - Orders as the Current Data Source and I have but the "Insert selected fields as..." button is greyed out. What am I doing wrong? Am I supposed to have something in particular displayed the main part of the SPD gui? Sorry I am not hip to your nomenclature, but "my ASPX" is referring to linked sources that I just made in steps 3-5?

Hi, My developers have been trying to do this for months... but with your article it just took 10 mins to have this up and running. very valuable indeed. thanks.

Ok - you owe me Chai Samosa then!

How do you get the formatting results as displayed? The customer name appears at the bottom of each grouping for me. How to place it at the top? ALSO: How to display the label Orders just once in each grouping?

Hi Can I join a sharepoint list and a database table!? Do you have samples? Thanks!

Hi, can we use the ID of an lookup field instead of the value?? Because, there are some special characters that does not work with your example. If you replace "Andrew Connell" by "Johnson & Johnson", it says that could not find any order for this customer... the problem is the "&" character. Any ideas? Thanks,
Fabio

see the tables below.
I want to create db schema in lists. means lists should be linked to each other as RDBMS work. can i use the lists instead of rdbms db. actually i dont want to use db. these lists will hold thousands of records and i also want to perform re-search and Tracking on created lists.
------------------------------------------------------------------------------------------ USE [parameter_DB]
GO
/****** Object: Table [dbo].[search_details] Script Date: 08/26/2008 14:34:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[search_details](
[order_search_detail_id] [bigint] IDENTITY(1,1) NOT NULL,
[search_id] [bigint] NOT NULL,
[county_field_id] [bigint] NOT NULL,
[timestamp] [datetime] NOT NULL CONSTRAINT [DF_search_details_timestamp] DEFAULT (getdate()),
[Value] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[record_order_flag] [bit] NULL,
CONSTRAINT [PK_order_feature_junction] PRIMARY KEY CLUSTERED
(
[order_search_detail_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] GO
SET ANSI_PADDING OFF
GO
USE [parameter_DB]
GO
ALTER TABLE [dbo].[search_details] WITH CHECK ADD CONSTRAINT [FK_order_feature_junction_county_field] FOREIGN KEY([county_field_id])
REFERENCES [dbo].[county_field] ([county_field_id])
GO
ALTER TABLE [dbo].[search_details] WITH NOCHECK ADD CONSTRAINT [FK_order_feature_junction_order] FOREIGN KEY([search_id])
REFERENCES [dbo].[search_history] ([search_id])
GO
ALTER TABLE [dbo].[search_details] CHECK CONSTRAINT [FK_order_feature_junction_order] USE [parameter_DB]
GO
/****** Object: Table [dbo].[search_history] Script Date: 08/26/2008 14:35:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[search_history](
[search_id] [bigint] IDENTITY(1,1) NOT NULL,
[orderTimestamp] [datetime] NOT NULL CONSTRAINT [DF_search_history_orderTimestamp] DEFAULT (getdate()),
[user_code] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[company_id] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[county_fips] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state_fips] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[station_id] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[outputtype] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_type_flag] [tinyint] NOT NULL,
[order_name_value] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[title_officer_value] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[order_comment_value] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[keep_order_open_value] [bit] NULL,
CONSTRAINT [PK_order] PRIMARY KEY CLUSTERED
(
[search_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] GO
SET ANSI_PADDING OFF
GO
USE [parameter_DB]
GO
ALTER TABLE [dbo].[search_history] WITH NOCHECK ADD CONSTRAINT [FK_order_order_type_flag] FOREIGN KEY([order_type_flag])
REFERENCES [dbo].[search_type_flag] ([order_type_flag])
GO
ALTER TABLE [dbo].[search_history] CHECK CONSTRAINT [FK_order_order_type_flag]
GO
ALTER TABLE [dbo].[search_history] WITH CHECK ADD CONSTRAINT [FK_order_user] FOREIGN KEY([user_code])
REFERENCES [dbo].[user] ([user_code])
------------------------------------------------------------------------------------

We have a similar situation as Kalash. We would like to use Sharepoint lists as database tables. This has been going well for us but we have run into a situation where we would like to implement a conditional lookup. For example in a list if a person selects a value from a lookup field (drop down), we would like to display related values from the list the lookup field is refering to. Would this be possible in Sharepoint? Any help would be appreciated.

Thanks for a great post! You can achieve more join-like nature by passing the parent to the child rows. In this example it would allow us to show Customer fields on the Orders row. <xsl:template name="dvt_2.body">
<xsl:param name="Rows" />
<xsl:param name="dvt_ParentRow" /> <xsl:for-each select="$Rows">
<xsl:call-template name="dvt_2.rowview">
<xsl:with-param name="dvt_ParentRow" select="$dvt_ParentRow" />
</xsl:call-template>
</xsl:for-each>
</xsl:template> <xsl:template name="dvt_2.rowview">
<xsl:param name="dvt_ParentRow" />
<tr>
<td>
<xsl:value-of select="@Customer" />
<xsl:value-of select="$dvt_ParentRow/@Title" />
</td>
</tr>
</xsl:template>

Hi, I had the same approach as akash and still trying to show some picture with the ImageThumbnailDisplay Column. it only give a 0 as result. do you know how to make it work?

Hi Nitin,
You said "Showing data in Parent/Child Mode is possible if you use ListViewWebParts and then link them." Can you elaborate on this? I have the exact same need as Sebastian Soanca.