Give me idea abt query

jehangir · #1 (**permalink**) 07-27-07, 09:13

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.

aschk · #2 (**permalink**) 07-27-07, 09:21

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

jehangir · #3 (**permalink**) 07-27-07, 09:30

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(...)
                            ........
                        )
        )

aschk · #4 (**permalink**) 07-27-07, 10:55

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech