Problem regarding query

[Mohini Dhanaskar]

hello All,

i had created two tables in a same oracle database.

first one is RegisterComplaint and second is ConsumerRegistration.

There fields are as follows:

RegisterComplaint:

CID VARCHAR2(5)
COMPLAINT_TYPE VARCHAR2(40)
NOCOMP NUMBER(2,0)
NOPRBM NUMBER(4,0)
TIME NUMBER(6,2)
CONSUMER-ID NUMBER(10,0)

ConsumerRegistration:


CONSUMER-ID NUMBER(10,0)
FNAME VARCHAR2(4000)
LNAME VARCHAR2(4000)
CADDR VARCHAR2(4000)
AREA VARCHAR2(4000)
PHN-NO NUMBER(10,0)
WARDNO VARCHAR2(4000)
ZONE VARCHAR2(4000)


here,i want to write one query which fetch d data from both the columns where RegisterComplaint Consumer-ID id same
as ConsumerRegistration Consumer-ID.

Please help me out in d same.

[Wendy L Gibbons]

you need to use the where clause

the syntax is
select <columns>
from <comma seperated table list with aliases>
where <colunm1 = column2 etc.>

a table alias is required here for both tables as you have a column called consumer-id on both tables

[Ravikanth thota]

select *
from RegisterComplaint R, ConsumerRegistration C
where R.CONSUMER-ID=C.CONSUMER-ID;

[chris webster]

rk thota wrote:select *
from RegisterComplaint R, ConsumerRegistration C
where R.CONSUMER-ID=C.CONSUMER-ID;

Don't use "SELECT *..." here because you will get two Consumer ID columns (the PK and the FK). Specify the individual columns you need instead.

In general, you should be specific about which columns you need to fetch in your query, especially in a join query as here, so you know exactly which columns are being retrieved and in what order (e.g. for mapping into Java), you don't get any nasty surprises if somebody changes the underlying table structure (e.g. to add a column that you are not handling in Java), and you don't waste resources fetching data that is never used.

Also, check your column names. If the Consumer ID column is really "CONSUMER-ID" (not CONSUMER_ID) then you will need to wrap it in double quotes i.e. SELECT "CONSUMER-ID" FROM..., or Oracle will think you're trying to subtract the value in a column called ID from the value in a column called CONSUMER.

It's better to avoid making column or table names case sensitive or using characters like "-" in names, as it just creates more scope for errors. If possible, use underscores in names e.g. CONSUMER_ID, not CONSUMER-ID, then you can simply write SELECT consumer_id FROM... and not worry about case-sensitivity or quotes.

Incidentally, the way you explained your requirement is basically the way you would write the SQL - "fetch X where ...", so why not just go ahead and learn some SQL?

[Wendy L Gibbons]

to follow on from Chris
column lists

[Mohini Dhanaskar]

i tried the query,

Query:
select CID,complaint_type,fname,lname
from RegisterComplaint1 R, ConsumerRegistration C
where R.CONSUMER-ID=C.CONSUMER-ID;


getting error:

ORA-00904: "ID": invalid identifier

[Wendy L Gibbons]

Mohini Dhanaskar wrote: i tried the query,

Query:
select CID,complaint_type,fname,lname
from RegisterComplaint1 R, ConsumerRegistration C
where R.CONSUMER-ID=C.CONSUMER-ID;


getting error:

ORA-00904: "ID": invalid identifier

Please read chris's reply, he explains about this:

Also, check your column names. If the Consumer ID column is really "CONSUMER-ID" (not CONSUMER_ID) then you will need to wrap it in double quotes i.e. SELECT "CONSUMER-ID" FROM..., or Oracle will think you're trying to subtract the value in a column called ID from the value in a column called CONSUMER.

[Eshwin Sukhdeve]

mohini this error comes (ORA-00904:) when you are not using valid identifier.remove(-) from the column name.put (_) .then try it should work.