Query Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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
 
Back
Top