Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2007
    Posts
    2

    Angry Unanswered: Give me idea abt query

    Hello All,

    I have a Data model in which i have the following tables structure


    posts(table)-------->columns(id, ...........)
    lists_posts(table)--->columns(post_id, list_id)
    lists(table)--------->columns(id, user_id, .....)
    users(table)-------->coulmns(id,.......)

    I want to get lists from lists table, users from users table and posts from posts table where
    lists.user_id = users.id
    lists_posts.list_id = lists.id
    and lists_posts.id = posts.id

    I write the query using Joins and getting for multiple posts duplicated lists and users but i want to get posts in such a way that for each user+list comination there shld be multiple posts in the result.


  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What sort of output are you expecting? Are you looking for list posts for a particular user, or lists for a particular user.
    Write out an expected output... i.e. what it will look like.

    For each join you do you are rolling out the tree to expand the previous result set (i.e. multiply it) by the number of adjoining rows in the additional table.

    i.e.
    Code:
    SELECT id FROM users;
    =================
    id
    =================
    1
    2
    3
    4
    5
    
    SELECT u.id,l.id FROM users u JOIN lists l ON l.user_id = u.id
    =================
    user_id, list_id
    =================
    1 => expanded
    1           1
    1           2
    1           3
    2 => expanded
    2           4
    2           5
    2           6
    
    
    SELECT u.id,l.id,lp.id FROM users u 
    JOIN lists l ON l.user_id = u.id
    JOIN list_posts lp ON lp.list_id = l.id
    =================
    user_id, list_id, post_id
    =================
    1 => expanded
    1           1  => expanded
    1           1            1
    1           1            2
    1           1            3
    1           2  => expanded
    1           2            4
    1           2            5
    1           2            6
    1           3  => expanded
    1           3            7
    1           3            8
    AND SO ON......
    2 => expanded
    2           4
    2           5
    2           6
    Last edited by aschk; 07-27-07 at 10:28.

  3. #3
    Join Date
    Jul 2007
    Posts
    2
    Quote Originally Posted by aschk
    What sort of output are you expecting? Are you looking for list posts for a particular user, or lists for a particular user.

    For each join you do you are rolling out the tree to expand the previous result set (i.e. multiply it) by the number of adjoining rows in the additional table.

    i.e.
    Code:
    SELECT id FROM users;
    =================
    id
    =================
    1
    2
    3
    4
    5
    
    SELECT u.id,l.id FROM users u JOIN lists l ON l.user_id = u.id
    =================
    user_id, list_id
    =================
    1 => expanded
    1           1
    1           2
    1           3
    2 => expanded
    2           3
    2           4
    2           5
    The output i am expecting shld be: all lists for each user and all posts for each list. like
    Code:
    [lists] => Array
            (
                [0] => Array
                        (
                            [Listing] => Array
                            (
                                [id] => 1015
                                ........
                            )
    
                           [User] => Array
                           (
                                [id] => 85
                                ........
                            )
                            [post] => Array
                           (
                                [0] => array(...)
                                [1] => array(...)
                                [2] => array(...)
                                ........
                            )
               [1] => Array
                        (
                            [Listing] => Array
                            (
                                [id] => 1023
                                ........
                            )
    
                           [User] => Array
                           (
                                [id] => 89
                                ........
                            )
                            [post] => Array
                           (
                                [0] => array(...)
                                [1] => array(...)
                                [2] => array(...)
                                ........
                            )
            )

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    If you need that sort of output you're going to have to parse the MySQL dataset using your favourite scripting language (which in this case looks like PHP).
    What you're asking for is a 3D like structure, arrays of arrays of arrays, unfortunately MySQL has no way of providing this sort of output.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •