CODEWITHSUNDEEP
sql
user1624843
user1624843

Reputation: 23

Selecting data from 3 tables using SQL in Oracle

I am hoping there is someone out there that can assist. I am very new to all of this and I am stuck. I have 3 tables that I am trying to pull data from:

Customer (custid, first, last, pointsclubno, survey no, order date, order tot)

Employee (empid, firstname, lastname, salary, sale, salesdate, custid, locationid)

Location (locationid, address, city, state)

I am trying to select customers who were served by employees and the location that it happened. I tried this query first:

SELECT EMPLOYEE.EMPID, FIRST, LAST, LOCATION.LOCATIONID
FROM   EMPLOYEE, CUSTOMER, LOCATION  
WHERE  EMPID = '111111'
   AND EMPLOYEE.LOCATIONID = LOCATION.LOCATIONID;

No rows were selected.

Then this query:

SELECT EMPLOYEE.EMPID, FIRST, LAST, LOCATION.LOCATIONID
FROM   EMPLOYEE, CUSTOMER,LOCATION
WHERE  EMPID = '111111'
   AND EMPLOYEE.LOCATIONID = LOCATION.LOCATIONID;

 EMPID FIRST     LAST     LOCATIONID

111111 BOB       JONES      1267
111111 JIM       DAVIS      1267
111111 SCOTT     CONKLIN        1267
111111 DAN       ORTIZ      1267
111111 BRIAN     JOHNSON        1267
111111 TOM       MANN       1267
111111 LIZ       HENSLEY        1267
111111 BARB      BUTLER     1267
111111 MARIE     ANTON      1267
111111 JOE       SMITH      1267

10 rows selected.

Based off how the tables are set up this should only pull 1 row. And as you can see, the first query pull 0 and the second pulled 10. Can anyone see what I am doing wrong?

Upvotes: 2

Views: 3552

Answers (2)

RedhopIT
RedhopIT

Reputation: 619

I already think that you have wrong logic in your database schema ? you should add empid in customer table and remove custid from employees table ? because each employee can serve many customers ?

then use :

SELECT * 
FROM   CUSTOMER C, LOCATION L, EMPLOYEE E 
WHERE  C.EMPID = '11111' 
   AND E.EMPID = '11111' 
   AND E.locationid = l.locationid

Upvotes: 1

rs.
rs.

Reputation: 27427

Try this: [You didn't join customer and employee table]

SELECT 
EMPLOYEE.EMPID, 
FIRST, LAST, 
LOCATION.LOCATIONID 
FROM CUSTOMER, EMPLOYEE, LOCATION
WHERE 
CUSTOMER.CUSTID = EMPLOYEE.CUSTID AND
EMPLOYEE.LOCATIONID = LOCATION.LOCATIONID AND
AND EMPLOYEE.EMPID = '111111';

Upvotes: 1

Related Questions