SQL help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vp - 23 Mar 2006 21:06 GMT

For the table given below

PK1 spk1 STATE1 PROV1 PLAN1
pk2 spk2 state2 prov2 plan2

Tried the query for the above 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 how I could use the above logic...(let me know if
that is correct)

Reply to this Message

tuarek - 29 Mar 2006 18:20 GMT

VP,

I verified your statement. It looks fine to me..

Regards,

Mehmet

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 case
FROM table1 a,table2 b
WHERE (a.PK1=b.PK2 and a.spk2=b.spk2)

Reply to this Message

Tonkuma - 30 Mar 2006 10:05 GMT

PK1, spk1, ..., pk2, spk2, ....
What are these?
Column names? Data?
Are there one table which have columns PK1, spk1, ..., pk2, spk2, ....,
plan2?

>From most part of your SQL statement, it looks these are column names.

But, "FROM PROVIDER a, PROVIDER b" confused me. a and b are same table.
So, for example:
This phrase "a.PK1=b.PK2 and a.spk2=b.spk2" shows the table PROVIDER
have columns PK1 and PK2.

Reply to this Message