Discuss "Query help" in the SQL Development forum.

mehere · #1 June 6th, 2008, 01:59 PM

i have a table that has:

pol_num, trn_code, new_pol_num

now what i need to extract is any pol_num that has more than one 'Z' trn_code, but different new_pol_num ... something like this

table data
123 -- Z -- 987
123 -- Z -- 876
456 -- Z -- 654
456 -- Z -- 654

in the above instance ... it would only return pol_num 123 since that has 2 different new_pol_num(s).

any idea.

Rebelle · #2 June 6th, 2008, 02:13 PM

something like this?

SELECT distinct new_pol_num, pol_num, trn_code
FROM Table1
WHERE (((trn_code)="z"))

wait, sorry i think this will give you the opposite...it will give you one 456 because the new_pol is the same.

mehere · #3 June 6th, 2008, 02:18 PM

that's correct rebelle ... not to mention that it get's slightly more complicated as i only want to deal with pol_num that have more than 1 'Z' trn_code.

Wolffy · #4 June 6th, 2008, 02:33 PM

Haven't tested this yet, but you sound desparate

Code:

select pol_num, new_pol_num
 from myTable
where trn_code = 'Z'
 group by pol_num, new_pol_num
 having count(1) > 1

Rebelle · #5 June 6th, 2008, 02:38 PM

yeah...i think this is about the same or similar to Wolffy's.

Code:

SELECT pol_num, trn_code, Count(new_pol_num) AS Countnew_pol_num
FROM mytable
GROUP BY pol_num, new_pol_num
HAVING (((trn_code)="z") AND ((Count(new_pol_num))>1))

Wolffy · #6 June 6th, 2008, 02:43 PM

Close, but it doesn't work. Got it to work in Oralce, but that doesn't help.

mehere · #7 June 6th, 2008, 02:47 PM

how did you get it to work in Oracle? it's driving me nuts

Wolffy · #8 June 6th, 2008, 02:57 PM

Code:

select pol_num from
(select distinct pol_num, new_pol_num from myTable
where trn_code = 'Z') as A
group by pol_num
having count(pol_num) > 1

It was bloody syntax. Oracle didn't require the table alias.

sbenj69 · #9 June 6th, 2008, 03:09 PM

SELECT test.pol_num, test.trn_code, Count(test.new_pol_num) AS CountOfnew_pol_num, test.new_pol_num
FROM test
GROUP BY test.pol_num, test.trn_code, test.new_pol_num
HAVING (((test.trn_code)="z") AND ((Count(test.new_pol_num))=1));

mehere · #10 June 6th, 2008, 03:15 PM

you are my own personal hero ... thanks muchly wolffy.

here's what i ended up doing:

Code:

select full_pol, trn_dt, q_full_pol
from dbo.HO_CONV_TRN 
where full_pol in 
	(select full_pol 
	from
		(select distinct Full_Pol, q_full_pol
		from dbo.HO_CONV_TRN 
		where trn_code = 'z') as A
	group by Full_Pol 
	having count(Full_Pol) > 1)
and trn_code = 'Z'
order by full_pol, q_full_pol

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Query Counts	Jaykappy	Microsoft Access	13	May 14th, 2008 01:39 PM
SQL VBA Query	Jaykappy	Microsoft Access	12	May 8th, 2008 09:30 AM

The Following User Says Thank You to Wolffy For This Useful Post:
mehere (June 6th, 2008)

Discuss "Query help" in the SQL Development forum.

SQL Development - Structured Query Language (SQL) is the talk of databases. Discuss topics such as joins, triggers and other advanced SQL topics.