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, essentially creating a many-to-many relationship
between tables 1 and 3.

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 (MAX(PrimaryKey)) record for each record
in Table 1.

I want the view to look something like this:

Table 1          Table1        Table2       Table3
PrimaryKey     Textfield1    Textfield    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?

This seems to work...

CREATE TABLE Table1
(
PKT1 int primary key,
T1 varchar(10) NULL,
T2 varchar(10) NULL,
T3 varchar(10) NULL
)
go
INSERT Table1 VALUES (1,'a','b','c')
go

CREATE TABLE Table3
(
PKT3 int primary key,
T1 varchar(10) NULL,
T2 varchar(10) NULL,
T3 varchar(10) NULL
)
go
INSERT Table3 VALUES (1,'a','b','c')
go

CREATE TABLE Table2
(
PKT2 int primary key,
PKT1 int FOREIGN KEY REFERENCES Table1(PKT1),
PKT3 int FOREIGN KEY REFERENCES Table3(PKT3),
T1 varchar(10) NULL
)
go
INSERT Table2 VALUES (1,1,1,'one')
INSERT Table2 VALUES (2,1,1,'two')
go

SELECT a.PKT1, b.T1, c.PKT3
FROM Table1 a
LEFT JOIN Table2 b ON a.PKT1 = b.PKT1
LEFT JOIN Table3 c ON b.PKT3 = c.PKT3
WHERE b.PKT2 = (SELECT MAX(PKT2) FROM Table2 WHERE Table2.PKT1 = a.PKT1)CREATE
TABLE Table1
(
PKT1 int primary key,
T1 varchar(10) NULL,
T2 varchar(10) NULL,
T3 varchar(10) NULL
)
go
INSERT Table1 VALUES (1,'a','b','c')
go

CREATE TABLE Table3
(
PKT3 int primary key,
T1 varchar(10) NULL,
T2 varchar(10) NULL,
T3 varchar(10) NULL
)
go
INSERT Table3 VALUES (1,'a','b','c')
go

CREATE TABLE Table2
(
PKT2 int primary key,
PKT1 int FOREIGN KEY REFERENCES Table1(PKT1),
PKT3 int FOREIGN KEY REFERENCES Table3(PKT3),
T1 varchar(10) NULL
)
go
INSERT Table2 VALUES (1,1,1,'one')
INSERT Table2 VALUES (2,1,1,'two')
go

SELECT a.PKT1, b.T1, c.PKT3
FROM Table1 a
LEFT JOIN Table2 b ON a.PKT1 = b.PKT1
LEFT JOIN Table3 c ON b.PKT3 = c.PKT3
WHERE b.PKT2 = (SELECT MAX(PKT2) FROM Table2 WHERE Table2.PKT1 = a.PKT1)


-- Results --

PKT1    T1        PKT3

------       ------     ----------

1         two        1





Show quoteHide quote

Looks like I had a cut-n-copy error in the previous reply.
This is a little clearer...


CREATE TABLE Table1
(
PKT1 int primary key,
T1 varchar(10) NULL,
T2 varchar(10) NULL,
T3 varchar(10) NULL
)
go
INSERT Table1 VALUES (1,'a','b','c')
go

CREATE TABLE Table3
(
PKT3 int primary key,
T1 varchar(10) NULL,
T2 varchar(10) NULL,
T3 varchar(10) NULL
)
go
INSERT Table3 VALUES (1,'a','b','c')
go

CREATE TABLE Table2
(
PKT2 int primary key,
PKT1 int FOREIGN KEY REFERENCES Table1(PKT1),
PKT3 int FOREIGN KEY REFERENCES Table3(PKT3),
T1 varchar(10) NULL
)
go
INSERT Table2 VALUES (1,1,1,'one')
INSERT Table2 VALUES (2,1,1,'two')
go

SELECT a.PKT1, b.T1, c.PKT3
FROM Table1 a
LEFT JOIN Table2 b ON a.PKT1 = b.PKT1
LEFT JOIN Table3 c ON b.PKT3 = c.PKT3
WHERE b.PKT2 = (SELECT MAX(PKT2) FROM Table2 WHERE Table2.PKT1 = a.PKT1)


-- Results --

PKT1    T1        PKT3
------       ------     ----------
1         two        1

asp.net sql connection problem
SqlConnection - Timeout Property is read-only?
Operation must use an updateable query... while trying to write to Excel file
Using @@Identity with access
Data in table through dataset or bindingsource