Query for multiple tables

Hi,

I have a query that returns values from three seperate data tables into a dataset:

SELECT [MediaFileData].[FileIdentifier], [AudioPCData].[RecorderID],
[AudioPCData].[Channel], [MediaFileData].[SubmittingUser], [MediaFileData].[DateTimeAsString],
[MetaData].[FieldText]

FROM [MediaFileData] INNER JOIN [AudioPCData] ON MediaFileData.FileIdentifier = AudioPCData.FileIdentifier
INNER JOIN [MetaData] ON MediaFileData.FileIdentifier = MetaData.FileIdentifier
INNER JOIN [SSRData] ON MediaFileData.FileIdentifier = SSRData.FileIdentifier

WHERE ([MediaFileData].[SubmittingUser] = '{0}') AND ([AudioPCData].[RecorderID] = '{0}')
AND ([MediaFileData].[MediaDescription] = '{0}') AND ([SSRData].[ModelUsed] = '{0}')

Each table has only got one 'FileIdentifier' apart from MetaData. This table has three columns 'FileIdentifer', 'FiledName' and 'FieldText'. One file can have more than one field and therefore It will have the same 'FileIdentifier' e.g.

FileIdentifier FieldName FieldText

1 Field 1 Hello

1 Field 2 Goodbye

The first problem is I only want to display the first and second field 'FiledText' in my results grid but still load all the other fields into my dataset.

The second problem is that it creates a new row for every field, whereas I want the fields with the same 'FileIdentifier' to be in the same row!

At the moment mt results gridlooks like this:

FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2

1 MyPC 1 Me 03/05/07 14:24 Hello Hello

1 MyPC 1 Me 03/05/07 14:24 GoodBye Goodbye

I need it to look like this:

FileIdentifier RecorderID Channel SubmittingUser DateTimeAsString Field 1 Field 2

1 MyPC 1 Me 03/05/07 14:24 Hello GoodBye

Thanks,

Guy

Depends on which version of SQL Server you are using. Using SQL 2k5 you should take a look on Cross apply.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Guy,

Could you be more clear as to what your goal is, and what the purpose of these fields might be, their scope, the number of overlapping entries, and generally how you view this thing to be structured? On first blush, this seems like a situation where a relational database setup could save you some storage and access time in the long run if you can identify the shared fields and refactor your tables. The problem with setting Field1 and Field2 appropriately is also interesting in that you want Field1 to have "hello" in it and field2 to have "goodbye" in it whereas other than that string nothing differs between the records. This behaviour seems highly specific to your problem, and so knowing more about what your goals are for this database might help us to identify how best to solve your immediate problems in such a manner as to have long-term benefits.

Some natural things to weigh might be:

How do these components logically relate, and what are valid values for things in the database? (e.g. identical entries with field1 and field2 values, should this happen?, should field1 and field2 be mirrored?, etc.).

Does this solution need to last, or is it a one-time only operation?

How important is it to retain the database structure?

Is there a rule to how you want this join to work in the long run that requires domain specific knowledge?

How important is the performance of your database in the long run?

How often do you intend to perform this operation, and how much development work is "worth it"?

I think that as we understand the constraints of your task and what you intend to accomplish, we can find a solution that best fits your needs.

Thanks,

John (MSFT)