0

Updates : Please see below

i have table: data

+-----------------------+--------------+-----------+
| State                 | d_country    | d_postcode|
+-----------------------+--------------+-----------+
| State1                | Country1     |      1111 | 
| State2                | Country2     |      2222 | 
| State3                | Country3     |      3333 | 
| State4                | Country4     |      4444 | 
+-----------------------+--------------+-----------+

And another table: user

+-----------------------+--------------+-----------+
| Name                  | u_country    | u_postcode|
+-----------------------+--------------+-----------+
| Name1                 | Country3     |      3333 | 
| Name2                 | Country5     |      5555 | 
| Name3                 |              |      6666 | 
| Name4                 | Country6     |      6666 | 
| Name5                 | Country6     |      6666 | 
+-----------------------+--------------+-----------+

What SQL should i use to:

Determine the number (count) of country that are not listed on table data. For example u_postcode is not listed in d_postcode is 5555 and 6666. It will return 2.

List down name and what country not available in table data yet.

Updates

  1. I want to use grouping to filter postcode and make Name3 and Name4 as different rows.

For example:

    +-----------------------+--------------+-----------+
    | Name                  | u_country    | u_postcode|
    +-----------------------+--------------+-----------+ 
    | Name2                 | Country5     |      5555 | 
    | Name3                 |              |      6666 |
    | Name4                 | Country6     |      6666 | 
    +-----------------------+--------------+-----------+

Any possible idea?

Remarks: Corrected myself

SELECT DISTINCT Name, u_country, u_postcode FROM user 
WHERE u_postcode NOT IN (SELECT d_postcode FROM data)
apis17
  • 2,825
  • 2
  • 23
  • 23
  • Is this homework? Looks like a homework question – Cetra May 19 '10 at 04:05
  • no this is not homework. but anyone want to make this as homework also can. :). i know the hard way but want to use simplest query like 2-10 lines to achieve this. like JOIN, IN inner JOIN but do not know how to. still research. and not found :( – apis17 May 19 '10 at 04:20
  • i have this: http://stackoverflow.com/questions/150610/selecting-unique-rows-in-a-set-of-two-possibilities and this: http://stackoverflow.com/questions/190702/mysql-select-n-rows-but-with-only-unique-values-in-one-column related to my problem. it's near but i have no idea yet. – apis17 May 19 '10 at 04:22
  • updates: i want Name3 and Name4 as unique value. – apis17 May 19 '10 at 06:09

1 Answers1

5

Try this.

SELECT Name, u_country, u_postcode FROM user 
WHERE u_postcode NOT IN (SELECT d_postcode FROM data)

Hope it helps

FlyingCat
  • 14,036
  • 36
  • 119
  • 198
  • @jerry: can i use another subquery or AND NOT EXISTS ? i'm affraid IN will be problem in performance. any idea? – apis17 May 19 '10 at 05:18
  • if i put GROUP BY u_postcode, the u_country for Name 3 will be blank. how to display Name3 and Name4 as unique? – apis17 May 19 '10 at 06:02
  • ok nvm, i solved uniqe problem using SELECT DISTINCT command. thank you all. – apis17 May 19 '10 at 13:19