Professional Web Applications Themes

Help with 0 / N query (users/users_photos) - MySQL

Hi I have the next problem: I Have a table users: I Have another table users_photos. The table user_photos has a user_id. A user can have from 0 to N photos so perhaps the user_id of some users are not in users_photos table. I need a query that returns: - user_id, name, address.... from table users. - photo_name from users_photos. with the next conditions: - It must return users without photos. - It must return only the first ocurrence in users_photos.This is where Im having problems. I get a query that returns all the data I need, but users with ...

  1. #1

    Default Help with 0 / N query (users/users_photos)

    Hi
    I have the next problem:
    I Have a table users:
    I Have another table users_photos.

    The table user_photos has a user_id.
    A user can have from 0 to N photos so perhaps the user_id of some users
    are not in users_photos table.
    I need a query that returns:

    - user_id, name, address.... from table users.
    - photo_name from users_photos.

    with the next conditions:

    - It must return users without photos.
    - It must return only the first ocurrence in users_photos.This is where
    Im having problems. I get a query that returns all the data I need,
    but users with more than 1 photo are repeated.

    Is it possible?.
    Thanks.

    fReDiNi Guest

  2. #2

    Default Re: Help with 0 / N query (users/users_photos)

    SELECT DISTINCT

    http://dev.mysql.com/doc/refman/5.0/en/select.html

    Karsten Guest

  3. #3

    Default Re: Help with 0 / N query (users/users_photos)


    fReDiNi wrote: 

    We I'm sure you worked out that DISTINCT is not what you need (sorry
    Karsten).

    However what you want is possible. There is a bit of key (no pun
    intended) information missing from your description of the problem
    though. We'll come to this in a minute.

    Now what you want to do is almost exactly the same as the answer that I
    posted just yesterday on the (short) thread
    http://groups.google.co.uk/group/comp.databases.mysql/tree/browse_frm/thread/c494258c0901ecfd/36c4f38bd933a98d?rnum=1&hl=en&_done=%2Fgroup%2Fcom p.databases.mysql%2Fbrowse_frm%2Fthread%2Fc494258c 0901ecfd%2F%3Fhl%3Den%26#doc_ee9721b4cd5d66fd

    The part that you haven't told us about, is how you identify what is
    the FIRST occurrence in the users_photos table. It could be that there
    is an autoincrement id column or maybe there is a timestamp column that
    shows when the row was added?
    I am going to assume that you are using the former, but the query is of
    the same format if you are using a timestamp, just change the field
    names as required.

    So, my 2 tables look as follows:
    CREATE TABLE `users` (
    `user_id` int(11) NOT NULL,
    `name` varchar(20) NOT NULL,
    `address` varchar(30) NOT NULL
    )
    CREATE TABLE `users_photos` (
    `user_id` int(11) NOT NULL,
    `photo_name` varchar(30) NOT NULL,
    `photo_id` int(11) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`photo_id`)
    )

    Using these tables, the query toi achieve what you want is:
    SELECT
    u1.*,
    p1.*
    FROM `users` u1
    LEFT JOIN `users_photos` p1 USING(user_id)
    LEFT JOIN `users_photos` p2 ON p1.user_id = p2.user_id AND p1.photo_id
    < p2.photo_id
    WHERE p2.photo_id IS NULL

    Captain Guest

Similar Threads

  1. License.Limit.Exceeded with 4x150 users licence andonly 300 real users
    By mm_patrick in forum Macromedia Flash Flashcom
    Replies: 6
    Last Post: November 18th, 01:30 AM
  2. License.Limit.Exceeded with 4x150 users licence and only300 real users
    By danielalamaison in forum Macromedia Flash Flashcom
    Replies: 8
    Last Post: November 14th, 08:04 PM
  3. Query the users workstation for username
    By scott@M&I in forum Coldfusion - Advanced Techniques
    Replies: 4
    Last Post: February 28th, 05:07 PM
  4. Replies: 2
    Last Post: July 2nd, 01:48 PM
  5. Sql Query for new users in the system.....Help Needed
    By Jacco Schalkwijk in forum Microsoft SQL / MS SQL Server
    Replies: 8
    Last Post: July 2nd, 02:37 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