query of information

Question

Post reply

query of information

icampbell

Right there with Babe

Points: 770
More actions
May 19, 2009 at 6:01 am

#130400

If you have two tables A & B
A.ref_no= b.ref_no
how do i retrieve the detail of table A which do not appear in table B. The rows which only appear in table A.
Any advice is greatly appreciated.
Thanks.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 1

A number of ways, simplest to read is probably

SELECT * FROM A WHERE A.ref_no NOT IN (SELECT b.ref_no FROM b)

These types of query are typically not that efficient when optimised though where the tables are large.

.

icampbell Right there with Babe Points: 770 More actions · Answer 2

select distinct *

from srs_esd,srs_cap

where srs_esd.esd_code not in(select srs_cap.cap_stuc from srs_cap)

gives multiple copies of the same line. How do i get a distinct value.

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 3

If I understood your query it should be

select distinct *

from srs_esd

where srs_esd.esd_code not in(select srs_cap.cap_stuc from srs_cap)

not

select distinct *

from srs_esd,srs_cap

where srs_esd.esd_code not in(select srs_cap.cap_stuc from srs_cap)

.

arjun.tewari Ten Centuries Points: 1013 More actions · Answer 4

icampbell (5/19/2009)
If you have two tables A & B
A.ref_no= b.ref_no
how do i retrieve the detail of table A which do not appear in table B. The rows which only appear in table A.
Any advice is greatly appreciated.
Thanks.

Well the answer is in your question only. It will simply be

A.ref_no b.ref_no

The query can be:

SELECT A.* FROM A JOIN B ON A.ref_no B.ref_no

Please comply to the FORUM standards before posting.

icampbell Right there with Babe Points: 770 More actions · Answer 5

tried the following code

SELECT distinct * FROM srs_esd JOIN srs_cap ON srs_esd.esd_code srs_cap.cap_stuc

however the distinct doesn't seem to work

tried this also

select distinct *

from srs_esd

where srs_esd.esd_code not in(select srs_cap.cap_stuc from srs_cap)

however the wrong value is displayed.

please help?

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 6

arjun.tewari (5/19/2009)
icampbell (5/19/2009)
If you have two tables A & B
A.ref_no= b.ref_no
how do i retrieve the detail of table A which do not appear in table B. The rows which only appear in table A.
Any advice is greatly appreciated.
Thanks.
Well the answer is in your question only. It will simply be
A.ref_no b.ref_no
The query can be:
SELECT A.* FROM A JOIN B ON A.ref_no B.ref_no
Please comply to the FORUM standards before posting.

I don't believe this will work. Have you tried it?

Which forum standard are you referring to?

Tim

.

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 7

icampbell (5/19/2009)
tried the following code
SELECT distinct * FROM srs_esd JOIN srs_cap ON srs_esd.esd_code srs_cap.cap_stuc
however the distinct doesn't seem to work
tried this also
select distinct *
from srs_esd
where srs_esd.esd_code not in(select srs_cap.cap_stuc from srs_cap)
however the wrong value is displayed.
please help?

You'll have to give me more on what is wrong with the second query. You should see all rows on srs_esd that don't have a match in srs_cap on srs_esd.esd_code = srs_cap.cap_stuc.

Can't help much more than that with the details you have provided.

Tim

.

icampbell Right there with Babe Points: 770 More actions · Answer 8

Hi Tim,

Table A has 2213 rows which match the criteria for

esd_ayrc = '09/10'

and esd_cald >'01/01/2009'

However on comparing to table B with the query returns 2137 results.

This is far too high.

Thanks,

Iain.

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 9

Hang on .. you seem to have introduced a conditional clause now and switched back to talking about tables A and B.

I think arjun.tewari was probably referring to the fact that it's best to post the real query and schema you are using.

Please post both table structures and the exact query and I'll take a look.

Thanks

Tim

.

GSquared SSC Guru Points: 260824 More actions · Answer 10

Try this:

select distinct srs_esd.*

from srs_esd

left outer join srs_cap

on srs_esd.esd_code = srs_cap.cap_stuc

where srs_cap is null;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kupy SSCommitted Points: 1652 More actions · Answer 11

select a.*

from a left outer join on A.ref_no= b.ref_no

where b.ref_no is null

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 12

GSquared (5/19/2009)
Try this:
select distinct srs_esd.*
from srs_esd
left outer join srs_cap
on srs_esd.esd_code = srs_cap.cap_stuc
where srs_cap is null;

kupy (5/19/2009)
select a.* from a left outer join on A.ref_no= b.ref_no where b.ref_no is null

Both are alternative constructs for the same thing I posted. Different folks write it different ways.

I suspect OP has some sort of cartesian product, which is why I have asked for the full code.

Tim

.

icampbell Right there with Babe Points: 770 More actions · Answer 13

Hi Guys,

Thanks for the advise. It turns out that there was a data issue and that was causing the wrong number of rows to be returned. A variation on TIM's solution worked.

Thanks,

Iain.:-P

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 14

Tim Walker.

SSCertifiable

Points: 5173

May 19, 2009 at 9:19 am

#997093

Thanks for the feedback.

Glad it's sorted.

Tim

.