Discussion:
query help
(too old to reply)
mcnewsxp
2013-02-28 14:18:45 UTC
Permalink
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.
Erland Sommarskog
2013-02-28 14:44:01 UTC
Permalink
Post by mcnewsxp
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, SQL Server MVP, ***@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
2013-02-28 19:54:03 UTC
Permalink
Post by Erland Sommarskog
Post by mcnewsxp
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.
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.
thanks much.
mcnewsxp
2013-02-28 22:20:03 UTC
Permalink
Post by Erland Sommarskog
Post by mcnewsxp
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.
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:

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'.
Erland Sommarskog
2013-02-28 22:52:08 UTC
Permalink
Post by mcnewsxp
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:

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.
--
Erland Sommarskog, SQL Server MVP, ***@sommarskog.se

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
mcnewsxp
2013-02-28 22:24:44 UTC
Permalink
Post by Erland Sommarskog
Post by mcnewsxp
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.
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
rpresser
2013-03-01 15:13:02 UTC
Permalink
Post by Erland Sommarskog
Post by mcnewsxp
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.
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.
s***@acceleratebs.com
2013-03-11 08:12:51 UTC
Permalink
Post by mcnewsxp
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.
Loading...