selecting from database when rows are shared on another table?

Question

Hi,

I'm having an issue trying to extract data from a table that is kinda shared. Here are the tables I've created:

CREATE TABLE CUSTOMER
(SSN CHAR(9) NOT NULL,
FNAME VARCHAR(15) NOT NULL,
LNAME VARCHAR(15) NOT NULL,
PHONE CHAR(10),
ADDRESS VARCHAR(30) ,
PRIMARY KEY (SSN));
CREATE TABLE ACCOUNT
(ACC_NO CHAR(10) NOT NULL,
BALANCE DECIMAL(10,2) NOT NULL,
OPEN_DATE DATE,
TYPE CHAR(10) ,
PRIMARY KEY (ACC_NO));
CREATE TABLE OWNS
(SSN CHAR(9) NOT NULL,
ACC_NO CHAR(10) NOT NULL,
OWN_DATE DATE,
TYPE CHAR(10) ,
PRIMARY KEY (SSN,ACC_NO),
FOREIGN KEY(SSN) REFERENCES CUSTOMER(SSN),
FOREIGN KEY(ACC_NO) REFERENCES ACCOUNT(ACC_NO));

Here is what is inserted into the database:
INSERT INTO CUSTOMER
VALUES ('123456789','John','Smith','7077777070','6000 J St Sacramento, CA 95815');
INSERT INTO CUSTOMER
VALUES ('234567890','Jane','Doe','7776541238','666 J St Sacramento, CA 95815');
INSERT INTO ACCOUNT
VALUES ('002','7500.99',TO_DATE('1999-11-18', 'YYYY-MM-DD'),'Checking');
INSERT INTO OWNS
VALUES ('123456789','002',TO_DATE('1999-11-15', 'YYYY-MM-DD'),'Checking');
INSERT INTO ACCOUNT
VALUES ('001','500.99',TO_DATE('1999-11-15', 'YYYY-MM-DD'),'Savings');
INSERT INTO OWNS
VALUES ('234567890','001',TO_DATE('1999-11-15', 'YYYY-MM-DD'),'Savings');
INSERT INTO OWNS
VALUES ('234567890','002',TO_DATE('1999-11-18', 'YYYY-MM-DD'),'Checking');

Basically. I am trying to retrieve information regarding customer Jane Doe. I am trying to do:
SELECT O.SSN,COUNT(A.ACC_NO),SUM(A.BALANCE)
FROM ACCOUNT A, OWNS O
WHERE O.ACC_NO=A.ACC_NO AND O.SSN=(SELECT O2.SSN
FROM OWNS O2
WHERE COUNT(O2.ACC_NO) >=2) ;

However I know that this runs into a grouping issue. I don't know how to resolve it. I want to use a query that doesn't require a specific customer's data as the database will be updated in the future. I tried using natural joins but I'm not sure where to go from there. I tried:
SELECT SSN,COUNT(ACC_NO),SUM(BALANCE)
FROM ((CUSTOMER NATURAL JOIN OWNS) NATURAL JOIN ACCOUNT)
WHERE COUNT(ACC_NO)>=2;

But I realized that this runs into a similar issue. Can someone help me out?

Answer 1

Below Query might solve the problem

select c.FNAME,c.LNAME,c.ADDRESS , a.BALANCE ,a.OPEN_DATE,a.TYPE ,o.OWN_DATE,o.TYPE

from (CUSTOMER c , ACCOUNT a and OWNS o)

where c.SSN=o.SSN and a.ACC_NO=o.ACC_NO

Answer 2

Finding customer specific requirements has long been a challenge. In fact, a survey of current automotive suppliers found that a significant number did not know where to go for the latest applicable customer specific requirements. For registrars and end users, this represents a serious problem. How can rules be followed and enforced if they are not readily available?







It is with these challenges in mind customerspecifics.com was created. Here you will find a community to access, share, and discuss customer specific requirements.







Please understand that the content of this site will take some time to develop as we work hand in hand with each of you to build a comprehensive database of the thousands of available customer specific requirements. We ask that you join us in our cause as we attempt to build something great for the common benefit of the quality community.

Answer 3

a minimum of the headers are a similar. get waiting for a lot of eyeball artwork. replica all the records from sheet1 beginning from the authentic left nook to the backside precise. circulate to sheet 2. circulate to the backside. Paste. you have a merged workbook. there are probable some column which could be sorted, variety using that. Then circulate by way of line by using line. Delete the duplicates.

Answer 4

If you use an aggregate function (like count or sum) you have to group by all non-aggregate fields. there's no way to get around it. Either don't use aggregate functions or use GROUP BY.