Professional Web Applications Themes

SQL Needed to Detect Married Households - MySQL

This is greatly simplified, but I have a database that contains the following fields and sample data: First,Last,Address,Gender,Age John,Doe,123 Main,M,34 Jane,Doe,123 Main,F,32 Jacob,Doe,123 Main,M,15 Bob,Smith,234 Main,M,45 Tom,Smith,234 Main,M,43 Sara,Brown,345 Main,F,34 Nancy,Brown,345 Main,F,37 Don,Black,456 Main,M,34 Sally,Black,456 Main,F,30 I need to come up with a query that will return "married households" using the criteria that there is a man and women listed at the same address both over the age of 18 years old. The query needs to list everyone at the household if the household meets the criteria (not just the two married people) As applied to the above data it ...

  1. #1

    Default SQL Needed to Detect Married Households

    This is greatly simplified, but I have a database that contains the
    following fields and sample data:

    First,Last,Address,Gender,Age
    John,Doe,123 Main,M,34
    Jane,Doe,123 Main,F,32
    Jacob,Doe,123 Main,M,15
    Bob,Smith,234 Main,M,45
    Tom,Smith,234 Main,M,43
    Sara,Brown,345 Main,F,34
    Nancy,Brown,345 Main,F,37
    Don,Black,456 Main,M,34
    Sally,Black,456 Main,F,30

    I need to come up with a query that will return "married households"
    using the criteria that there is a man and women listed at the same
    address both over the age of 18 years old. The query needs to list
    everyone at the household if the household meets the criteria (not just
    the two married people)

    As applied to the above data it should return these records:

    John,Doe,123 Main,M,34
    Jane,Doe,123 Main,F,32
    Jacob,Doe,123 Main,M,15
    Don,Black,456 Main,M,34
    Sally,Black,456 Main,F,30

    I imagine I have to use a subquery to accomplish this but I am looking
    for some assistance getting a general method to work correctly. In my
    actual application, there are additional fields such as City, State,
    Zip and a lot of other information columns. I hope I can come up with
    SQL that is flexible enough to be expanded to the full application.
    Also, I didn't include a primary key in the data listed above so feel
    free to include an ID field in any proposed solution. Thanks in advance.

    dandiebolt@gmail.com Guest

  2. #2

    Default Re: SQL Needed to Detect Married Households


    com wrote:
     

    This seems to do what you want (note I have assumed that "over the age
    of 18 means 18 or over so that 18 years and 1 second is over 18):

    SELECT DISTINCT p3. *
    FROM people p3
    JOIN people p2 USING ( Address )
    JOIN people p1 ON p1.Address = p2.Address
    AND p1.Gender <> p2.Gender
    AND p1.Age >=18
    AND p2.Age >=18

    Captain Guest

  3. #3

    Default Re: SQL Needed to Detect Married Households

    com wrote:
     


    Wow, I hope that's a for-instance, and not an actual algorithm.
    Sanders Guest

  4. #4

    Default Re: SQL Needed to Detect Married Households

    Sanders Kaufman wrote:
     
    >
    >
    >
    > Wow, I hope that's a for-instance, and not an actual algorithm.[/ref]

    for something this simple - the OP really needs to get a good book (a good
    starter is "SQL for Dummies" or go to some training... I recommend this book
    not to imply or infer the intelligence of the OP, it is simply a good starting
    point for a newbie...

    do some searches on joins and the "where-clause".

    --
    Michael Austin.
    Database Consultant
    Michael Guest

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139