You don’t care about the order of M/F? If there’s only M or F, you want NULL, ‘F’ or ‘M’, NULL returned? Or does the primary one have to be first?
I only want to find the (rider) coverages where the rider sex is different from the base sex.
Assume sex is always populated. It just might be wrong.
I think something like
SELECT A.Policy, A.Coverage, A.Sex, B.Sex
FROM Table A
INNER JOIN
(SELECT Policy, Coverage, Sex FROM Table WHERE Sex = ‘F’) AS B
ON A.Policy = B.Policy AND A.Coverage = B.Coverage
WHERE A.Sex = ‘M’
Might need some revision, my SQL is rusty.
1 Like
Doesn’t even need A.Sex and B.Sex honestly but it’ll show you for sure it’s pulling the right stuff
IFY(SQL)
Since you want the mis-matches, then use the HAVING statement which will check on the final table (rather than the input table(s)). (Note: it might need to be HAVING Sex_A <> Sex_B.)
1 Like
I have started writing SQL using Conditional Table Expressions, which lends itself to a “write in order” idea.
So, first I’d get the list of base coverages (put policy and sex into a CTE table named Covlist), then I’d compare to all the riders. (You could create a CTE table for riders only as well. Just put a comma (after the close paren) after the SELECT stmt for Bse). I am using a LEFT JOIN to find any policies with riders (cov<>‘B’) that don’t have a coverage = ‘B’.
WITH Bse (Pol,Sex) AS
(SELECT policy,sex FROM origdata WHERE coverage = ‘B’)
SELECT origdata.*, Bse.Sex FROM
origdata LEFT JOIN Bse ON origdata.policy = Bse.Pol
WHERE Bse.Sex <> origdata.sex AND origdata.coverage <> ‘B’
Expanded version
WITH Bse (Pol,Sex) AS
(SELECT policy,sex FROM origdata WHERE coverage = ‘B’),
Rid (Pol,Cov,Sex) AS
(SELECT policy,coverage,sex FROM origdata WHERE coverage <> ‘B’)
SELECT Rid.*, Bse.Sex FROM
Rid LEFT JOIN Bse ON Rid.Pol = Bse.Pol
WHERE Rid.Sex <> Bse.Sex
ETA, I have the rider and base sex out of order compared to your original specs, but that is easily fixed. It does bring back memories to pre - Obergefell days, where spouse riders with same sex as base were investigated as possible issue errors.
1 Like
Oh yeah, thanks! I haven’t actually run SQL in a few years now. I totally agree.
This is very helpful. It allows me to think more like I’m used to. :toth:
In the output for that, Rid.Sex & Bse.Sex are both labeled as Sex. How/where do I tell it to label them as, say, SexRdr and SexBas?
I would know how to do that in a simple Select statement. I’ve tried a few things here but they’re errorful.
Use a “column alias” (look at my SQL for an illustration as it actually gives an example that answers your question directly).
1 Like
Ooh…it goes in this SELECT statement…
So that it’s something like
SELECT Rid.*, Bse.Sex AS SEX_Bse FROM
Kiitos, ###
Yep . . . Rid.Sex will show up as “Sex” while Bse.Sex will show up as “SEX_Bse”.
And why is fornication in all caps?
[Insert witty double entendre here]
ISQLYP
A lot of sex talk going on in this SQL thread…
the quick and dirty answer is something like–
select A.*
from data A, data B
where A.Policy=B.Policy
and B.Coverage = ‘B’
and A.Sex<>B.Sex
There is no need to use nested queries, or having, or an outer join.
3 Likes
I am not an expert, but I believe you have an implied join in your statement. Someone who is more skilled than me can compare the execution plans and determine whether that is correct.
yeah, having “from data A, data B” instead of “from data A join B” seems pretty much the same thing
yes, it’s the same as an inner join. I just meant there was no outer join needed, although you might feel that’s easier to read.
select A.*
from data A inner join data B
on A.Policy=B.Policy
where B.Coverage = ‘B’
and A.Sex<>B.Sex
I think you’ll run into problems here as the join has to be done first before making this comparison (so need to use HAVING rather than WHERE).
