[AccessD] SQL help

Hi Gustav,

	Genius, that works perfect!

	Thanks to everyone for their help, wasn't actually sure if it was
gonna be possible to do using a query.

Pete

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: 14 December 2008 09:05
To: accessd at databaseadvisors.com
Subject: Re: [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


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com No virus found in this incoming
message.
Checked by AVG - http://www.avg.com
Version: 8.0.176 / Virus Database: 270.9.17/1846 - Release Date: 12/12/2008
18:59