Professional Web Applications Themes

Need help with query - MySQL

I'm stuck on a query. Can someone help? It looks like this: table countries ( ctryID int, ... country attributes ..., primary key (ctryID) ); table visited ( ctryID int, travelerID int ); A traveler has visited a country if the visited table has both the ctryID and the travelerID. There will be no duplicates of ctryID/travelerID tuples. I'm not interested in counting how many times the traveler visits each country ... only that they have or haven't visited. Assuming a current traveler with travelerID=2, find all country IDs (ctryID) that the traveler hasn't visited. Thanks. This one has me ...

  1. #1

    Default Need help with query

    I'm stuck on a query. Can someone help? It looks like this:

    table countries ( ctryID int, ... country attributes ..., primary key
    (ctryID) );

    table visited ( ctryID int, travelerID int );

    A traveler has visited a country if the visited table has both the ctryID
    and the travelerID. There will be no duplicates of
    ctryID/travelerID tuples. I'm not interested in counting how many times the
    traveler visits each country ... only that they have or haven't visited.

    Assuming a current traveler with travelerID=2, find all country IDs (ctryID)
    that the traveler hasn't visited.

    Thanks. This one has me stumped! I've tried subqueries ... everything. I
    must be doing something stupid.


    weheh Guest

  2. #2

    Default Re: Need help with query

    == Quote from weheh (net)'s article 

    i think you need an outer join between the 2 table.
    something like this:
    select countryID from country left outer join traveler on country.countryid =
    traveler.countryid
    --
    POST BY: lark with PHP News Reader
    lark Guest

  3. #3

    Default Re: Need help with query

    lark wrote: 
    >
    > i think you need an outer join between the 2 table.
    > something like this:
    > select countryID from country left outer join traveler on
    > country.countryid = traveler.countryid[/ref]

    He wants a list of all countries that travelerID (why not travellerID?) has
    not visited and you offer a query that lists every country multiple times.
    Wow that's helpful Lark!


    Paul Guest

  4. #4

    Default Re: Need help with query

    weheh wrote: 

    SELECT
    `c`.`ctryID`
    FROM `countries` `c`
    LEFT JOIN `visited` `v` ON `c`.`ctryID` = `v`.`ctryID` AND `v`.`travelerID`
    = 2
    WHERE `v`.`ctryID` IS NULL


    Paul Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Replies: 2
    Last Post: August 6th, 07:08 PM
  3. Replies: 1
    Last Post: July 2nd, 09:09 AM

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