Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
query help
20 views
Skip to first unread message
mcnewsxp
Feb 28, 2013, 9:18:45 AM2/28/13
to
I have a student table and need to filter it based on existing records on two other one to many tables. the two other tables will contain a student_id and a signdate. so I only need the most recent signdate so that I only get one record.
actually I only need to know if a matching student_id exists in each of these two tables.
I can't seem to work it out. I can get the syntax for one sub query but not two.
need help.
tia,
mcnewsw8.
actually I only need to know if a matching student_id exists in each of these two tables.
I can't seem to work it out. I can get the syntax for one sub query but not two.
need help.
tia,
mcnewsw8.
Erland Sommarskog
Feb 28, 2013, 9:44:01 AM2/28/13
to
If you only want to find students that exists on both tables:
SELECT ...
FROM students s
WHERE (SELECT *
FROM tbl1 t1
WHERE t1.student_id = s.student_id)
AND (SELECT *
FROM tbl1 t2
WHERE t2.student_id = s.student_id)
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
mcnewsxp
Feb 28, 2013, 2:54:03 PM2/28/13
to
On Thursday, February 28, 2013 9:44:01 AM UTC-5, Erland Sommarskog wrote:
> mcnewsxp (mcou...@mindspring.com) writes:
>
> > I have a student table and need to filter it based on existing records
>
> > on two other one to many tables. the two other tables will contain a
>
> > student_id and a signdate. so I only need the most recent signdate so
>
> > that I only get one record.
>
> > actually I only need to know if a matching student_id exists in each of
>
> > these two tables.
>
> > I can't seem to work it out. I can get the syntax for one sub query but
>
> > not two.
>
>
>
> I showed you just the other day the technique to get the most recent row.
>
>
>
> If you only want to find students that exists on both tables:
>
>
>
> SELECT ...
>
> FROM students s
>
> WHERE (SELECT *
>
> FROM tbl1 t1
>
> WHERE t1.student_id = s.student_id)
>
> AND (SELECT *
>
> FROM tbl1 t2
>
> WHERE t2.student_id = s.student_id)
>
>
>
>
>
that looks good.
> mcnewsxp (mcou...@mindspring.com) writes:
>
> > I have a student table and need to filter it based on existing records
>
> > on two other one to many tables. the two other tables will contain a
>
> > student_id and a signdate. so I only need the most recent signdate so
>
> > that I only get one record.
>
> > actually I only need to know if a matching student_id exists in each of
>
> > these two tables.
>
> > I can't seem to work it out. I can get the syntax for one sub query but
>
> > not two.
>
>
>
> I showed you just the other day the technique to get the most recent row.
>
>
>
> If you only want to find students that exists on both tables:
>
>
>
> SELECT ...
>
> FROM students s
>
> WHERE (SELECT *
>
> FROM tbl1 t1
>
> WHERE t1.student_id = s.student_id)
>
> AND (SELECT *
>
> FROM tbl1 t2
>
> WHERE t2.student_id = s.student_id)
>
>
>
>
>
thanks much.
mcnewsxp
Feb 28, 2013, 5:20:03 PM2/28/13
to
On Thursday, February 28, 2013 9:44:01 AM UTC-5, Erland Sommarskog wrote:
> mcnewsxp (mcou...@mindspring.com) writes:
>
> > I have a student table and need to filter it based on existing records
>
> > on two other one to many tables. the two other tables will contain a
>
> > student_id and a signdate. so I only need the most recent signdate so
>
> > that I only get one record.
>
> > actually I only need to know if a matching student_id exists in each of
>
> > these two tables.
>
> > I can't seem to work it out. I can get the syntax for one sub query but
>
> > not two.
>
>
>
> I showed you just the other day the technique to get the most recent row.
>
>
>
> If you only want to find students that exists on both tables:
>
>
>
> SELECT ...
>
> FROM students s
>
> WHERE (SELECT *
>
> FROM tbl1 t1
>
> WHERE t1.student_id = s.student_id)
>
> AND (SELECT *
>
> FROM tbl1 t2
>
> WHERE t2.student_id = s.student_id)
>
>
>
that solution gives this error:
>
> > I have a student table and need to filter it based on existing records
>
> > on two other one to many tables. the two other tables will contain a
>
> > student_id and a signdate. so I only need the most recent signdate so
>
> > that I only get one record.
>
> > actually I only need to know if a matching student_id exists in each of
>
> > these two tables.
>
> > I can't seem to work it out. I can get the syntax for one sub query but
>
> > not two.
>
>
>
> I showed you just the other day the technique to get the most recent row.
>
>
>
> If you only want to find students that exists on both tables:
>
>
>
> SELECT ...
>
> FROM students s
>
> WHERE (SELECT *
>
> FROM tbl1 t1
>
> WHERE t1.student_id = s.student_id)
>
> AND (SELECT *
>
> FROM tbl1 t2
>
> WHERE t2.student_id = s.student_id)
>
>
>
Msg 4145, Level 15, State 1, Line 6
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
mcnewsxp
Feb 28, 2013, 5:24:44 PM2/28/13
to
On Thursday, February 28, 2013 9:44:01 AM UTC-5, Erland Sommarskog wrote:
> mcnewsxp (mcou...@mindspring.com) writes:
>
> > I have a student table and need to filter it based on existing records
>
> > on two other one to many tables. the two other tables will contain a
>
> > student_id and a signdate. so I only need the most recent signdate so
>
> > that I only get one record.
>
> > actually I only need to know if a matching student_id exists in each of
>
> > these two tables.
>
> > I can't seem to work it out. I can get the syntax for one sub query but
>
> > not two.
>
>
>
> I showed you just the other day the technique to get the most recent row.
>
>
>
> If you only want to find students that exists on both tables:
>
>
>
> SELECT ...
>
> FROM students s
>
> WHERE (SELECT *
>
> FROM tbl1 t1
>
> WHERE t1.student_id = s.student_id)
>
> AND (SELECT *
>
> FROM tbl1 t2
>
> WHERE t2.student_id = s.student_id)
>
>
>
>
where EXISTS
>
> > I have a student table and need to filter it based on existing records
>
> > on two other one to many tables. the two other tables will contain a
>
> > student_id and a signdate. so I only need the most recent signdate so
>
> > that I only get one record.
>
> > actually I only need to know if a matching student_id exists in each of
>
> > these two tables.
>
> > I can't seem to work it out. I can get the syntax for one sub query but
>
> > not two.
>
>
>
> I showed you just the other day the technique to get the most recent row.
>
>
>
> If you only want to find students that exists on both tables:
>
>
>
> SELECT ...
>
> FROM students s
>
> WHERE (SELECT *
>
> FROM tbl1 t1
>
> WHERE t1.student_id = s.student_id)
>
> AND (SELECT *
>
> FROM tbl1 t2
>
> WHERE t2.student_id = s.student_id)
>
>
>
>
Erland Sommarskog
Feb 28, 2013, 5:52:08 PM2/28/13
to
mcnewsxp (mcou...@mindspring.com) writes:
> that solution gives this error:
>
> Msg 4145, Level 15, State 1, Line 6
> An expression of non-boolean type specified in a context where a
> condition is expected, near 'AND'.
If you want answers with tested queries, please include:
> that solution gives this error:
>
> Msg 4145, Level 15, State 1, Line 6
> An expression of non-boolean type specified in a context where a
> condition is expected, near 'AND'.
1) CREATE TABLE statements for your tables.
2) INSERT statements with sample data.
3) The desired result given the sample.
4) A brief description of the business rules.
5) Which version of SQL Server you are using.
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
rpresser
Mar 1, 2013, 10:13:02 AM3/1/13
to
On Thursday, February 28, 2013 9:44:01 AM UTC-5, Erland Sommarskog wrote:
> mcnewsxp (mcou...@mindspring.com) writes:
>
> > I have a student table and need to filter it based on existing
> > records on two other one to many tables. the two other tables
> > will contain a student_id and a signdate. so I only need the
> > most recent signdate so that I only get one record.
>
> > actually I only need to know if a matching student_id exists in
> > each of these two tables.
>
> > I can't seem to work it out. I can get the syntax for one sub
> > query but not two.
>
> I showed you just the other day the technique to get the most
> recent row.
>
> If you only want to find students that exists on both tables:
>
> SELECT ...
> FROM students s
> WHERE (SELECT *
> FROM tbl1 t1
> WHERE t1.student_id = s.student_id)
> AND (SELECT *
> FROM tbl1 t2
> WHERE t2.student_id = s.student_id)
Missing "EXISTS" before each (SELECT subquery.
>
> > I have a student table and need to filter it based on existing
> > records on two other one to many tables. the two other tables
> > will contain a student_id and a signdate. so I only need the
> > most recent signdate so that I only get one record.
>
> > actually I only need to know if a matching student_id exists in
> > each of these two tables.
>
> > I can't seem to work it out. I can get the syntax for one sub
> > query but not two.
>
> I showed you just the other day the technique to get the most
> recent row.
>
> If you only want to find students that exists on both tables:
>
> SELECT ...
> FROM students s
> WHERE (SELECT *
> FROM tbl1 t1
> WHERE t1.student_id = s.student_id)
> AND (SELECT *
> FROM tbl1 t2
> WHERE t2.student_id = s.student_id)
shiv...@acceleratebs.com
Mar 11, 2013, 4:12:51 AM3/11/13
to
0 new messages