Results 1 to 10 of 10

Thread: get address?

  1. #1
    Join Date
    Sep 2013
    Posts
    57

    Unanswered: get 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.

    Code:
    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
    I've Tried:

    Code:
    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post results from SQL below:

    select count(*) from address where address_line1 like '%4389%'
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    post results from SQL below:

    select count(*) from address where address_line1 like '%4389%'
    COUNT(*)
    ---------
    2

    Even better, here's how the 2 appear in the table:

    ADDRESS_LINE1
    ------------------------
    4389 JAFFA Terrace S.
    4389 Jaffa Terrace S.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select count(*) from address_mortal
    where address_id in (select address_id from address where address_line1 like '%4389%');
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2013
    Posts
    57
    That's not it, It's supposed to be the 2 names.....
    Or is that a sub select?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >That's not it, It's supposed to be the 2 names....
    since you choose to NOT provide requested details, I choose to stop wasting my time on this thread.

    You're On Your Own (YOYO)!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    >That's not it, It's supposed to be the 2 names....
    since you choose to NOT provide requested details, I choose to stop wasting my time on this thread.

    You're On Your Own (YOYO)!
    Scroll up.

    I posted the reply to your question.

    If I missed something, tell me.

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by bwilson95 View Post
    Scroll up.

    I posted the reply to your question.

    If I missed something, tell me.
    No you did not!
    What anacedent is trying to do is figure out if there is any inconsistency in your data and requested you execute a different query.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Anacedent is teaching you to work your way through the joins to see which condition is filtering out all of your (valid?) records.

  10. #10
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by LKBrwn_DBA View Post
    No you did not!
    What anacedent is trying to do is figure out if there is any inconsistency in your data and requested you execute a different query.
    Ok, I see that now.
    (never said i wasnt slow on the uptake lol)
    deosnt matter how you word the query, the instructor wants to see 'no rows selected', as the ID for the address in tbl address doesnt exist elsewhere.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •