[AccessD] SQL help

Hi Bobby,

	<<In your expected output below for cusID=1, you will not get the
conID=5 from table 1 because there are no items in table 2 linking cusid=1
and conid=5.>>
	The problem is that I need all three rows from table 1 where Req =
Yes even if there are no matching entries in table 2, so that you can see
which lines have a comment and which lines don't.
	Table 3 isn't actually needed as the query gets the customer ID from
a text box on the main form (which displays their details) - I only included
it to show that there can be customers in table 3 and no related records in
table 2.

Pete

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bobby Heid
Sent: 13 December 2008 22:05
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] SQL help

Pete,

SELECT Table3.CusID, Table3.CustName, Table1.Desc, Table1.Req,
Table2.Comment FROM (Table3 INNER JOIN Table2 ON Table3.CusID =
Table2.CusID) INNER JOIN
Table1 ON Table2.ConID = Table1.ConID
WHERE (((Table3.CusID)=1) AND ((Table1.Req)=True));

Based upon what you say you want, I think you would ONLY get the following
records:

Customer ID = 1
ConID	Desc	Req	CusID	Comment
2	A2	Yes	1	Yes

Customer ID = 2
ConID	Desc	Req	CusID	Comment
1	A1	Yes	2	Me
2	A2	Yes	2	Word

Customer ID = 3
ConID	Desc	Req	CusID	Comment

The reason is:
For ID = 1, there are 2 matches in table 2 (con id =2 and 3)
	For con id=2 (in table 1), you have one match in table 1 where req =
true
	For con id=3 (in table 1), there are 0 records in table 1 where req
= true

For ID = 2, there are 2 matches in table 2 (con id =1 and 2)
	For con id=1, you have one match in table 1 where req = true
	For con id=2, you have one match in table 1 where req = true

For ID = 3, there are 0 matches in table 2

To get to table 3 from table one, you have to go through table 2

Here's the query for cusID = 1:

	SELECT Table3.CusID, Table3.CustName, Table1.Desc, Table1.Req,
Table2.Comment
		FROM (Table3 
			INNER JOIN Table2 ON Table3.CusID = Table2.CusID) 
			INNER JOIN Table1 ON Table2.ConID = Table1.ConID
		WHERE Table3.CusID=1 AND Table1.Req=True

In your expected output below for cusID=1, you will not get the conID=5 from
table 1 because there are no items in table 2 linking cusid=1 and conid=5.

Hope this helps,
Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Pete Phillipps
Sent: Saturday, December 13, 2008 2:51 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] SQL help

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