[AccessD] SQL help

Hi Pete

This can be achieved with a Cartesian join and a single subquery to pick the comments like this:

PARAMETERS CustomerID Long;
SELECT 
  tblTable1.*, 
  tblTable3.CusID, 
  tblTable3.CusName, 
    (Select 
      Comment 
    From 
      tblTable2 As T 
    Where 
      T.CusID=tblTable3.CusID 
      And 
      T.ConID=tblTable1.ConID) AS 
  Comment
FROM 
  tblTable1, 
  tblTable3
WHERE 
  tblTable3.CusID=[CustomerID] 
  AND 
  tblTable1.Req=True;

ConID	Desc	Req	CusID	CusName	Comment
1	A1	Yes	1	Fred	
2	A2	Yes	1	Fred	Yes
5	A5	Yes	1	Fred	

1	A1	Yes	2	Daphne	Me
2	A2	Yes	2	Daphne	Word
5	A5	Yes	2	Daphne	

1	A1	Yes	3	Scooby	
2	A2	Yes	3	Scooby	
5	A5	Yes	3	Scooby	

/gustav


>>> pete at vftt.co.uk 13-12-2008 20:50 >>>
Hi Everyone,

	I am trying to get some data from the following tables into a list.
The basics of the tables are shown below:

TABLE 1
ConID	Desc	Req
1	A1	Yes
2	A2	Yes
3	A3	No
4	A3	No
5	A5	Yes

TABLE 2
CusID	ConID	Comment
1	2	Yes
1	3	Yep
2	1	Me
2	2	Word

TABLE 3
CusID	Name
1	Fred
2	Daphne
3	Scooby

	What I want is to select a customer from table 3 and get a list of
all items from table 1 where Req is Yes, along with any matching comment
from table 2 for that customer (if there are any), like the following
examples:

ConID	Desc	Req	CusID	Comment
1	A1	Yes	1	
2	A2	Yes	1	Yes
5	A5	Yes	1	

ConID	Desc	Req	CusID	Comment
1	A1	Yes	2	Me
2	A2	Yes	2	Word
5	A5	Yes	2	

ConID	Desc	Req	CusID	Comment
1	A1	Yes	3	
2	A2	Yes	3	
5	A5	Yes	3	

	What I can't do is get a SQL join that will bring up the results
like that. I've tried the following:

SELECT Table1.ConID, Table1.Desc, Table1.Req, Table2.CusID, Table2.Comment
FROM Table2 RIGHT JOIN Table1 ON Table2.ConID = Table1.ConID
WHERE (((Table1.Req)<>0) AND ((Table2.CusID)=1))
ORDER BY Table1.ConID, Table2.CusID;

	But that just produces (for customer 1) the following:

ConID	Desc	Req	CusID	Comment
2	A2	Yes	1	Yes

	How can I construct the SQL to produce the result as required?

Pete