Professional Web Applications Themes

Returning values which don't match values in table - MySQL

Hi, I hope that the subject isn't too vague... I have a table with country codes and names and want to query against it with a list of codes. The query should return the values, which are not in the table. Example: The table contains: 'de', 'fr', 'it', 'at', 'ch', 'es' The queried values are: 'de', 'it', 'es', 'pl', 'cz', 'us' The query should return: 'pl', 'cz', 'us' I know that there is a way with a subselect, but i don't know how to fill it with the query values. Thanks a lot for your help Christoph...

  1. #1

    Default Returning values which don't match values in table

    Hi,

    I hope that the subject isn't too vague...

    I have a table with country codes and names and want to query against
    it with a list of codes. The query should return the values, which are
    not in the table.

    Example:
    The table contains: 'de', 'fr', 'it', 'at', 'ch', 'es'
    The queried values are: 'de', 'it', 'es', 'pl', 'cz', 'us'
    The query should return: 'pl', 'cz', 'us'

    I know that there is a way with a subselect, but i don't know how to
    fill it with the query values.

    Thanks a lot for your help
    Christoph

    christoph Guest

  2. #2

    Default Re: Returning values which don't match values in table

    christoph lauterbach wrote: 

    You haven't told us a great deal about the tables so in my query I have made
    the following assumptions:
    1) The table is called country_code_table
    2) The field within it holding the country codes is called country_code

    If your table/field names differ then you can change them accordingly.
    Here is the query:
    SELECT `q`.`country_code`
    FROM (
    SELECT 'de' `country_code`
    UNION
    SELECT 'it'
    UNION
    SELECT 'es'
    UNION
    SELECT 'pl'
    UNION
    SELECT 'cz'
    UNION
    SELECT 'us'
    ) AS `q`
    LEFT JOIN `country_code_table` `c` USING(`country_code`)
    WHERE `c`.`country_code` is NULL


    Paul Guest

  3. #3

    Default Re: Returning values which don't match values in table

    > You haven't told us a great deal about the tables so in my query I have made 

    Thanks a lot Paul, this is great! I guess performance must be very
    good with this...

    Regards
    Christoph

    christoph Guest

  4. #4

    Default Re: Returning values which don't match values in table

    Okay, this evening I mastered to implement Pauls solution. The correct
    query reads as follows:
    SELECT `q`.`country_code`
    FROM (
    SELECT 'de' AS `country_code`
    UNION
    SELECT 'it' AS `country_code`
    UNION
    SELECT 'es' AS `country_code`
    UNION
    SELECT 'pl' AS `country_code`
    UNION
    SELECT 'cz' AS `country_code`
    UNION
    SELECT 'us' AS `country_code`
    ) AS `q`
    LEFT JOIN `country_code_table` USING(`country_code`)
    WHERE `country_code_table`.`country_code` is NULL

    Regards
    Christoph

    christoph Guest

  5. #5

    Default Re: Returning values which don't match values in table

    christoph lauterbach wrote: 

    You only need the column name on the first sub-select ('de' in this case)
    And using table aliases tend to make for easier reading (and also are
    necessary for self joins).
    I did test the query that I posted, so what were the changes that youfound
    necessary?


    Paul Guest

Similar Threads

  1. returning (char **) values
    By John in forum UNIX Programming
    Replies: 6
    Last Post: September 26th, 06:56 PM
  2. Returning Values from a SP to asp
    By Harag in forum ASP Database
    Replies: 1
    Last Post: August 23rd, 10:26 AM
  3. Stored Procedure not returning values.
    By Bob Barrows in forum ASP
    Replies: 2
    Last Post: August 14th, 12:31 PM
  4. Returning multiple values for unique key
    By noon in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 3rd, 03:35 PM
  5. [PHP] Returning values from functions
    By David Nicholson in forum PHP Development
    Replies: 0
    Last Post: July 9th, 10:44 PM

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