Query Question

[Guest]

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?

 

[Garth Wells]

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

 

[Garth Wells]

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