SEO

#1 (**permalink**) 02-27-2008, 06:28 AM

Hi All

I have a table with this layout (example 2 rows with 4 columns)

PK1 STATE1 PROV1 PLAN1
pk2 state2 prov2 plan2

If STATE1 and PROV1 equals state2 and prov2 than I have to plug in pk2
to a field.

How can I do it in SQL?? Please help need

thanks,

#2 (**permalink**) 02-27-2008, 06:28 AM

I am not sure i understand it all, but perhaps this is what you want:

SELECT col1, col2, MAX(col3) FROM table GROUP BY col1, col2 HAVING
COUNT(*) > 1

B.

#3 (**permalink**) 02-27-2008, 06:28 AM

PK1 STATE1 PROV1 PLAN1
pk2 state2 prov2 plan2

If (STATE1=state2 and PROV1=prov2 ) than take pk2 to plug in to
another field
Should I do a self join on the table to check this condition??? or
please advice

thanks,

#4 (**permalink**) 02-27-2008, 06:28 AM

vp wrote:
> PK1 STATE1 PROV1 PLAN1
> pk2 state2 prov2 plan2
>
>
> If (STATE1=state2 and PROV1=prov2 ) than take pk2 to plug in to
> another field
> Should I do a self join on the table to check this condition??? or
> please advice
>
> thanks,
>
As Joe Celko often says, some DDL would help, but here's a guess based
on my best guess based on what little data I have.

Perhaps you want something like:

UPDATE some_other_table SET some_column=(
SELECT pk FROM first_table
WHERE state='state2'
AND province='prov2')

#5 (**permalink**) 02-27-2008, 06:29 AM

PK1 spk1 STATE1 PROV1 PLAN1
pk2 spk2 state2 prov2 plan2

Tried the query for the able table( using a self join)

SELECT DISTINCT
case when a.PROV1 IS NOT NULL and
(a.PROV1=b.plan2 and a.STATE1=b.state2 )
then b.pk2
else a.prov1 end,
FROM PROVIDER a, PROVIDER b
WHERE (a.PK1=b.PK2 and a.spk2=b.spk2)

Please let me know if this is correct or suggest some thing...

thanks

#6 (**permalink**) 02-27-2008, 06:29 AM

PK1 spk1 STATE1 PROV1 PLAN1
pk2 spk2 state2 prov2 plan2

Tried the query for the able table( using a self join)

SELECT DISTINCT
case when a.PROV1 IS NOT NULL and
(a.PROV1=b.plan2 and a.STATE1=b.state2 )
then b.pk2
else a.prov1 end,
FROM PROVIDER a, PROVIDER b
WHERE (a.PK1=b.PK2 and a.spk2=b.spk2)

Please let me know if this is correct or suggest some thing...

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

SEO

Sql help