| Author |
 Topic  |
|
|
fuzzyjonclay
Starting Member
United Kingdom
3 Posts |
 Posted - 04/25/2012 : 06:17:10
|
Hi there,
I am new to SQL so please forgive me if this is a silly question :)
I need to retrieve records from one table where a certain condition exists in another table as follow:-
select address_number, address, town, county, postcode
from addresses
where exists (select * from organisations where status in ('FULL','BRAN'))
order by address_number
So, I want to retrieve all company records in the addresses table where the status of the company in the organisations table is equal to FULL or BRAN.
The above SQL is not working properly as 7082 records are retrieved whether I used the "where exists" or not.
Could anyone help me please! If it is useful, both the addresses and the organisations table share a key of address_number.
Many thanks,
Jon
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/25/2012 : 06:22:33
|
Where is the relation between the outer select statement and the statement in the exists clause?
N 56°04'39.26"
E 12°55'05.63" |
 |
|
|
fuzzyjonclay
Starting Member
United Kingdom
3 Posts |
Posted - 04/25/2012 : 06:23:40
|
Hi there,
Hmm, I'm not too sure exactly what you mean. I'm VERY new to SQL :)
Do I need to join the two table together somehow?
Cheers
Jon |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/25/2012 : 06:24:07
|
select a.address_number, a.address, a.town, a.county, a.postcode
from dbo.addresses AS a
where exists (select * from dbo.organisations AS x where x.status in ('FULL','BRAN') and x.somecol = a.somecol)
order by a.address_number
N 56°04'39.26"
E 12°55'05.63" |
|
|
|
fuzzyjonclay
Starting Member
United Kingdom
3 Posts |
Posted - 04/25/2012 : 06:27:19
|
Genius.... thank you so much :)
I can now see what I did wrong too.
Best wishes
Jon |
|
|
| |
Topic |
|
Problem retrieving records from two tables
