SQL Help for ASP Report

[ralphtrent]

Hello.
I am trying to gather data for a sql that will get family info and student info. Here is my sql:
SELECT
*
FROM
tSTATS st,
tMEMBERS mem,
tStudents s
WHERE
mem.FAMILY_ID = st.FAMILY_ID
and PAID_STS='Y' and LIST_STS='Y'
and s.FAMILY_ID = mem.FAMILY_ID
Sometimes the tSTUDENTS table might not have data that matches the mem.FAMILY_ID. Is there SQL that I can do that will still get the record. Right now I get 0 records if the member has no students. I am using an Access Database.

Thanks
Ralph

 

[DreXor]

i might suggest researching how to use a "SQL JOIN" , it will probably releave you of alot of confusion in your statements, and make it more responsive.

other than that i dont see much wrong with your SQL statement, other than in your DB is there any records where PAID_STS='Y' and LIST_STS='Y'?

hope the info helps

 

[Digimon02]

try this..

SELECT *
FROM tSTATS st
inner join tMEMBERS mem on st.FAMILY_ID = mem.FAMILY_ID
left join tStudents s on mem.FAMILY_ID = s.FAMILY_ID
WHERE PAID_STS='Y' and LIST_STS='Y'

 

[ralphtrent]

I am getting an error saying:

Syntax error (missing Operator_ in query expressing 'st.FAMILY_ID = mem.FAMILY_ID left joing tSTUDENTS on mem. FAMILY_ID = s.FAMILY_ID'

What does that mean. Where is it failing?

Thanks

 

[Webwench]

If you used copy paste for your last error statement, I see typo. 'joing'
also tSTUDENTS needs an 's' after it or 's.FAMILY_ID' is not good.

 

[ralphtrent]

the typo's where my fault. I copied the sql that Digimon02 wrote and I am getting the error on that.

 

[Veep]

Is this a MSAccess or SQLServer database?

 

[ralphtrent]

Access

 

[Veep]

MS Access gets confused when you mix your join types. You're going to need to replace the inner join with another Left join. I'd also qualify the criteria so the query engine is sure which table: st.PAID_STS='Y' and st.LIST_STS='Y'

Another way to go about it is to create a query in Access and where you're happy with it's results, just save it. Then you can just Select * from myQuery.

 

[ralphtrent]

doing this:
SELECT *
FROM tSTATS st
inner join tMEMBERS mem on st.FAMILY_ID = mem.FAMILY_ID
inner join tStudents s on mem.FAMILY_ID = s.FAMILY_ID
WHERE PAID_STS='Y' and LIST_STS='Y'

Still fails. any other ideas?

 

[Veep]

I create 3 tables in Access with some dummy data and this works:
Select sSTATS * From (tSTATS Inner join tMembers on tStats.Family_ID = tMembers.Family_ID) LEFT JOIN tStudents ON tMembers.Family_ID = tStudents.Family_ID WHERE tSTATS.PAID_STS='Y' and tSTATS.LIST_STS='Y'

What do you mean by "fails". What is the error?

 

[ralphtrent]

that seems to fix the sql but the report will still not show what I expect it to show. I must be a in my if then else codes now, but the sql is bring back the records I wanted it to