asked on

joining tables

I am trying to show all data in both tables, I am only seeing HR_tbl tables not cust_tbl?

select	 "CUST_TBL"."COMPANYNAME" as "COMPANYNAME",
	 "CUST_TBL"."ADDRESS_1" as "ADDRESS_1",
	 "CUST_TBL"."PHONE_OFFICES" as "PHONE_OFFICES",
	 "CUST_TBL"."PHONE_CELL" as "PHONE_CELL",
	 "HR_TBL"."LNAME" as "LNAME",
	 "HR_TBL"."FNAME" as "FNAME",
	 "HR_TBL"."PHONE_HOME" as "PHONE_HOME" 
 from	 "HR_TBL" "HR_TBL",
	 "CUST_TBL" "CUST_TBL" 
 where   "HR_TBL"."CUST_ID"="CUST_TBL"."COMPANYNAME"(+)

Open in new window

Terry Woods

The (+) indicates an outer join for Oracle SQL's, I believe - is that what you're using?

That would mean you can get results from one table and not the other when the join criteria "HR_TBL"."CUST_ID"="CUST_TBL"."COMPANYNAME" doesn't get met.

Normally a "cust_id" column would be numeric, so is useful to compare it with an alphabetic "companyname"?

jamesccnacmp

ASKER

OK what must I do

Terry Woods

How are the tables linked together? Is there a column value common to each table? If so, you probably want to join on that.

You need to explain the relationship between the tables. Is there a record (or many?) in HR_TBL for every record in CUST_TBL? Is there a record (or many?) in CUST_TBL for every record in HR_TBL?

Perhaps providing some example rows from each table and an example of the result you'd like to see would be a good way to go.

jamesccnacmp

ASKER

this is my table

COMPANYNAME      VARCHAR2(25)      Yes      -       -
ADDRESS_1      VARCHAR2(25)      Yes      -       -
PHONE_OFFICES      NUMBER(10,0)      No      -       -
PHONE_CELL      NUMBER(10,0)      Yes      -       -
FAXPHONE      NUMBER(10,0)      Yes      -       -
CITY      VARCHAR2(25)      Yes      -       -
STATE      VARCHAR2(3)      Yes      -       -
ZIPCODE      NUMBER(7,0)      No      -       -
EMAIL      VARCHAR2(35)      No      -       -
NOTES      CHAR(400)      No      -       -
CUST_ID      NUMBER(10,0)      Yes      -       -
HR_ID      NUMBER(10,0)      Yes      -       -

cust-tbl.xls
HR-tbl.xls

jamesccnacmp

ASKER

I have sent you HR_tbl and Cust_tbl tables

ASKER CERTIFIED SOLUTION

Terry Woods

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

jamesccnacmp

ASKER