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
