HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

HELP

Author

Topic

Tolk
Starting Member

4 Posts

Posted - 2005-02-15 : 14:44:40

sorry for abrupt and generalized topic heading. I need help with some SQL ive written (for an access database intergrated into a delphi - pascal (i know its shit but i have no choice, its for college) program)).
Heres what ive done so far:

SELECT screenings.APID FROM screenings
WHERE screenings.APID NOT IN (SELECT trap.APID FROM trap)
AND screenings.Heartrate BETWEEN (SELECT trcr.heartrateMIN FROM trcr) AND (SELECT trcr.heartrateMAX FROM trcr)

lines 1 and 2 work, the last two dont work; i think because i don't know how to reference the specific field value so its trying to compare the 'screenings.heartrate' field value against all the records in 'trcr.heartrateMIN/MAX' rather than just the one the file pointer is looking at.

anyone help?

Kristen
Test

22859 Posts

Posted - 2005-02-15 : 14:55:26

use something like

SELECT trcr.heartrateMIN FROM trcr WHERE trcr.SomeColumn = trap.SomeOtherColumn

to narrow the matching?

You might also need to enforce the sub-select only returning one row (even if, in practice, there is only one row SQL might think there *could* be multiple rows), e.g. :

SELECT MIN(trcr.heartrateMIN) FROM trcr WHERE trcr.SomeColumn = trap.SomeOtherColumn

Kristen

Tolk
Starting Member

4 Posts

Posted - 2005-02-15 : 15:01:59

SELECT screenings.APID FROM screenings
WHERE screenings.APID NOT IN (SELECT trap.APID FROM trap)
AND screenings.Heartrate BETWEEN (SELECT trcr.heartrateMIN FROM trcr WHERE trcr.TRID = trap.TRID) AND (SELECT trcr.heartrateMAX FROM trcr WHERE trcr.TRID = trap.TRID)

ok heres the new SQL trcr.TRID and trap.TRID both definately exist but when i try to run the SQL it says trap.TRID has no default value?
Think its because in the second line (which i need) its only selecting values that are not in 'trap' so it has nothing to compare to (if you see what i mean).
Thanks for the help btw!

Kristen
Test

22859 Posts

Posted - 2005-02-15 : 15:06:06

Sorry, I was typing out of the wrong end!

The only table you can connect to is "screenings" - "trap" is NOT in scope.

SELECT trcr.heartrateMIN FROM trcr WHERE trcr.SomeColumn = screenings.SomeOtherColumn

If you need "trap" to be in scope you will have to JOIN "screenings" to "trap"

Kristen

Tolk
Starting Member

4 Posts

Posted - 2005-02-15 : 15:13:37

problem is i have no equal fields in trcr and screenings. Herers an ER so you can see better whats going on (i wasnt being very helpful just giving you some random sql).
[url]http://www.mchugh.btinternet.co.uk/image1.JPG[/url]

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-02-15 : 15:27:35

Does the Trcr table hold a set of ranges that the Screenings table is supposed to fall between or is there one set of min/max values that are to be used? If the latter, how does one pick the one range to use?

HTH

=================================================================
Egotism is the anesthetic that dulls the pain of stupidity. -Frank William Leahy, football coach (1908-1973)

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-15 : 15:28:01

Would something like this work


SELECT s.APID 
FROM applicant a LEFT JOIN screenings s ON a.APID = s.APID
		 LEFT JOIN trap t ON a.APID = t.APID
		 RIGHT JOIN trcr r ON t.TRID = r.TRID
WHERE t.APID IS NULL AND (s.Heartrate BETWEEN r.heartrateMIN AND r.heartrateMAX)

Andy

Beauty is in the eyes of the beerholder

Tolk
Starting Member

4 Posts

Posted - 2005-02-15 : 15:35:32

@Bustaz

In the table screenings there are a group of different fields, 'heartrate' obviously is one of these. In the table trcr there are the min and max values for each field (there prb better way of this but im new to this stuff). For this query i want to select all the records in the table screenings which do not have equivilants in the table trap, and whos values fall within the min and max values in the table trcr.
(sorry im lame at explainging stuff hopefully the above will help you help me though :)

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-15 : 15:57:55

quote:

Originally posted by AndyB13

SELECT s.APID
FROM applicant a LEFT JOIN screenings s ON a.APID = s.APID
LEFT JOIN trap t ON a.APID = t.APID
RIGHT JOIN trcr r ON t.TRID = r.TRID
WHERE t.APID IS NULL AND (s.Heartrate BETWEEN r.heartrateMIN AND r.heartrateMAX)


SELECT DISTINCT s.APID 
FROM trials t1 INNER JOIN trcr t2 ON t1.TRID = t2.TRID
	       INNER JOIN screenings s ON s.SFID = t1.SFID
WHERE s.APID NOT IN (SELECT trap.APID FROM trap) 
	AND (s.Heartrate BETWEEN t2.heartrateMIN AND t2.heartrateMAX)

Andy

Beauty is in the eyes of the beerholder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources