Not sure how to get what I want with SQL code 1

[Rascelon]

OK. I have 2 tables, tblAnalyte and tblWQStandard. There is a one to many relationship between the 2 tables. Unique analytes are stored in tblAnalyte and water quality standards for an analyte are stored in tblWQStandard. There can be more than one standard for any analyte but only one analyte per standard stored.

I have a query with the 2 tables using a left inner join so that I keep all the records from tblAnalyte (all the analytes) but only includes those WQ standards that match with analytes in tblAnalyte (all analytes in WQ standards do match with tblAnalyte because it is an enforced relationship).

There are 500 records in tblAnalyte and 100 in tblWQStandard. Running the query above I get more than 500 because there are duplicate analytes in WQStandard. I'm only interested in showing a certain standard type (MCL) but I want to show all the analytes in tblAnalyte.
If I specify WHERE WQStandard Type = MCL then I get only 50 records because those are only the records with MCL standards. But I thought the left inner join would allow me to get what I want.

Not sure what I'm doing wrong. Any suggestions?
Vince

 

[r937]

what you are doing wrong is using the WHERE clause for your condition -- move it to the ON clause instead

change this --

from tblAnalyte
left outer
join tblWQStandard
on tblWQStandard.foo = tblAnalyte.bar
where tblWQStandard.type = 'MCL'

to this --

from tblAnalyte
left outer
join tblWQStandard
on tblWQStandard.foo = tblAnalyte.bar
and tblWQStandard.type = 'MCL'

p.s. it's a left outer join, not a left inner join ;-)

r937.com | rudy.ca

 

[PHV]

Sorry Rudy, but JetSQL chokes on such join conditions ...
SELECT A.*, W.*
FROM tblAnalyte AS A LEFT JOIN (
SELECT * FROM tblWQStandard WHERE Type = 'MCL'
) AS W ON A.foo = W.bar

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Rascelon]

Thanks PH. That worked exquisitely. You were also right about Rudy's syntax.
Thanks too Rudy for keeping me straight on the terms. I can't tell you how long I've been calling them left-right inner joins.
Vince

 

[r937]

thanks PHV, what a bummer it is always trying to remember which forum i'm in and which version of sql i can use

that's the nice thing about sql standards, eh -- there's so many to choose from!



r937.com | rudy.ca

 

[lespaul]

Rascelon - check out Understanding SQL Joins

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[r937]

lespaul, thanks, but an article written for mysql is possibly -- just possibly -- not going to help him avoid the type of trap that i fell into here

i mean, that article even says "Due to differences in capabilities, the techniques outlined in this article may not work on other RDBMS; you should refer to the documentation that comes with each system for accurate syntax."

in particular, it doesn't come close to touching on the issue in this particular situation (although it does use the right inner/outer terminology)

not to be too negative, just trying to keep all eyes open



r937.com | rudy.ca