Returning values which don't match values in table

#1 (**permalink**) 02-28-2008, 09:08 AM

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

#2 (**permalink**) 02-28-2008, 09:08 AM

christoph lauterbach wrote:
> 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

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

#3 (**permalink**) 02-28-2008, 09:08 AM

> 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

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

Regards
Christoph

#4 (**permalink**) 02-28-2008, 09:08 AM

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

#5 (**permalink**) 02-28-2008, 09:08 AM

christoph lauterbach wrote:
> 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

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?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode