Query/View Question

I am trying to create a view that returns data from three tables and can't seem to get it to return the data that I want. I am no SQL expert, so hopefully someone can give me some insight into what I need to do.

The tables are basically set up like this:

TABLE 1

PrimaryKey

Textfield1

Textfield2

Textfield3

TABLE 2

PrimaryKey

Table1ForeignKey

Table3ForeignKey

Textfield1

TABLE 3

PrimaryKey

Textfield1

Textfield2

Textfield3

Table 1 and Table 3 are each joined to Table 2 on their respective Primary/Foreign Key fields.

I want the view to return all of the records from Table 1, even if there are no matching records in Table 2.

From Table 2 I only want the latest record for each record in Table 1.

I want the view to look something like this:

Table 1

PrimaryKey

Table1

Textfield1

Table2

Textfield

Table3

Textfield

In other words, I want to return one record in the view for each record in table 1, and I want the data from table 2 in each of those records to represent the last record added to table 2.

Can anyone enlighten me on the query necessary to get this view?

Hi,

some more questions:

how do you define "the latest" in table2 ?

HTH, Jens Suessmeyer,

http://www.sqlserver2005.de

|||Since the Primary Key field autoincrements, the 'latest' record from Table 2 will always be the max(table2.primarykey).|||

Perhaps my question will make more sense explained like this:

I will use an analogy of checking out books from the library.

Table 1 is a table of books, with a primary key of bookid.

Table 2 is a detail record of who withdrew the book, when, when it was returned, etc. with a primary key of DetailID and has a foreign key to Table 1 to identify the book as well as a foreign key to table 3 to identify who withdrew it.

Table 3 is a table of library card holders contact info with a primary key of CardholderID.

All of the primary keys are auto-incrementing.

I want the view to basically give me a snapshot of ALL books, and if it a particular book is currently withdrawn, I want to see who has it and when they checked it out.

I hope that makes more sense.

|||

OK, keeping your analogy in mind, the query should be like:

Select
T1.PrimaryKey,T1.TextField,
T2.PrimaryKey,T2.TextField,
T3.TextField
FROM Table1 T1
LEFT JOIN
(
SELECT Table1FK, Table3FK,Textfield
FROM Table2
INNER JOIN
(
SELECT MAX(PrimaryKey) as PK, Table1PK
FROM TABLE2
GROUP BY Table1PK
) SubQuery
ON Subquery.PK = Table2.PK
AND SubQuery.Table1PK = Table2.Table1PK
) T2
ON
T1.PrimaryKey = T2.Table1FK
INNER JOIN Table3 T3
ON T3.PrimaryKey = T2.Table1FK

untested....

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de