p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

sql_language thread: help with sql subquery?


Message #1 by "Diana Rekos" <diana_rekos@p...> on Mon, 11 Mar 2002 14:35:28
You can do it like this :

SELECT Accounts.[Account Number], Accounts.SSN, Accounts.[Account Type],
Accounts.Company FROM Accounts AS T1 WHERE ((Accounts.Company <>
"RJFS") AND Accounts.[Account Number] EXISTS
SELECT Accounts.[Account Number] FROM Accounts WHERE
((Accounts.SSN = T1.SSN) AND (Accounts.[Account Type] = T1.[Account Type])
AND (Accounts.Company = "RJFS))
))

I think this is what you was looking for.

Oleg.

-----Original Message-----
From: Diana Rekos [mailto:diana_rekos@p...]
Sent: March 11, 2002 2:35 PM
To: sql language
Subject: [sql_language] help with sql subquery?


I have a table called "Accounts" with these fields:  Account Number,
Account Type, Company, SSN (and other fields don't really apply to this)

I want to write a query that will tell me within each SSN, which accounts
held outside of our company (Company <> "RJFS") do not have an account of
the same Account Type and SSN at the company.  I can't figure out how/if
the query is able to compare the current record it is looking at to other
records.  Is there some sort of Current(Account Type) function, or
This(Account Type), or something similar?  I remember being able to do this
sort of thing in other programming languages...

So the query should print a list of outside accounts that do not have a
related (same ssn & type) company account.

This is what I was thinking:
SELECT Accounts.[Account Number], Accounts.SSN, Accounts.[Account Type],
Accounts.Company FROM Accounts WHERE ((Accounts.Company <>
"RJFS") AND (Exists??
SELECT Accounts.[Account Number] FROM Accounts WHERE
((Accounts.SSN = same as this one??) AND (Accounts.[Account Type] = same as
this one??) AND (Accounts.Company = "RJFS))
))

$subst('Email.Unsub').


  Return to Index