CODEWITHSUNDEEP
sqldatabaseoracle11g
Brian Wilson
Brian Wilson

Reputation: 135

SQL find the address?

Im trying to find mortals who have a certain address.

I have what I believe to be the right code but no matter what I do to the 'WHERE' clause, I get no results returned.

Create a unique list of first and last names for anyone living at 4389 Jaffa Terrace S.

I'VE TRIED

SELECT MORTAL.FIRST_NAME||' '||MORTAL.LAST_NAME "4389_Jaffa_Terrace_S"
FROM MORTAL 
JOIN MORTAL_ADDRESS ON MORTAL.MORTAL_ID = MORTAL_ADDRESS.MORTAL_ID
JOIN ADDRESS ON MORTAL_ADDRESS.ADDRESS_ID = ADDRESS.ADDRESS_ID
WHERE ADDRESS.ADDRESS_LINE1 LIKE  '%4389%';


SELECT FIRST_NAME||' '||LAST_NAME "4389_Jaffa_Terrace_S"
FROM MORTAL
JOIN MORTAL_ADDRESS ON MORTAL.MORTAL_ID = MORTAL_ADDRESS.MORTAL_ID
JOIN ADDRESS ON MORTAL_ADDRESS.ADDRESS_ID = ADDRESS.ADDRESS_ID:
WHERE ADDRESS.ADDRESS_LINE1 LIKE  '%4389%';

SQL> SELECT FIRST_NAME||' '||LAST_NAME "4389_Jaffa_Terrace_S"
  2  FROM MORTAL
  3  JOIN MORTAL_ADDRESS ON MORTAL.MORTAL_ID = MORTAL_ADDRESS.MORTAL_ID
  4  JOIN ADDRESS ON MORTAL_ADDRESS.ADDRESS_ID = ADDRESS.ADDRESS_ID
  5  WHERE UPPER(ADDRESS.ADDRESS_LINE1) LIKE  '%4389%';

no rows selected

SQL> SELECT FIRST_NAME||' '||LAST_NAME "4389_Jaffa_Terrace_S"
  2  FROM MORTAL
  3  JOIN MORTAL_ADDRESS ON MORTAL.MORTAL_ID = MORTAL_ADDRESS.MORTAL_ID
  4  JOIN ADDRESS ON MORTAL_ADDRESS.ADDRESS_ID = ADDRESS.ADDRESS_ID
  5  WHERE UPPER(ADDRESS.ADDRESS_LINE1) LIKE  '%JAFFA%';

SQL> SELECT FIRST_NAME||' '||LAST_NAME "4389_Jaffa_Terrace_S"
  2  FROM MORTAL
  3  JOIN MORTAL_ADDRESS ON MORTAL.MORTAL_ID = MORTAL_ADDRESS.MORTAL_ID
  4  JOIN ADDRESS ON MORTAL_ADDRESS.ADDRESS_ID = ADDRESS.ADDRESS_ID
  5  WHERE SUBSTR(ADDRESS.ADDRESS_LINE1,0,4) LIKE  '4389';

no rows selected

EDITED TO ADD:

SQL> desc mortal
 Name
 --------------------------
 MORTAL_ID
 SEX_TYPE_CODE
 FIRST_NAME
 LAST_NAME
 DOB
 MARITAL_STATUS_CODE
 SSN
 MIDDLE_NAME
 WORK_PHONE

SQL> desc Mortal_address
 Name
 --------------------------
 ADDRESS_ID
 MORTAL_ID

SQL> desc address
 Name
 --------------------------
 ADDRESS_ID
 ADDRESS_TYPE_CODE
 ZIP_CODE
 STATE_CODE
 ADDRESS_LINE1
 CITY
 ADDRESS_LINE2
 ADDRESS_LINE3
 COUNTRY

Upvotes: 1

Views: 649

Answers (1)

BWS
BWS

Reputation: 3846

try debugging step-by-step.

first:

select * from address where address_line1 like '%4389%'

then, for any address returned,

select * from Mortal_address where address_id = <the address_id that was returned from above>

then, 

select * from mortal where mortal_id = <the mortal_id that was returned from above>

maybe this will show where your joins are not joining correctly ??

Upvotes: 3

Related Questions