Selecting columns from multiple tables?

Question

I need to retrieve data from 3 separate tables, as I've added an additional table to the database I'm using.

My layout currently looks like this:

 _____________       _____________      ___________      ___________
| users       |     |    links    |    |users_links|    | tags      |
|-------------|     |-------------|    |-----------|    |-----------|
| id          |     | id          |    | id        |    | id        |
| name        |     | long_url    |    | link_id   |    | user_id   |
| username    |     | short_url   |    | user_id   |    | link_id   |
| email       |     | user_id     |    | privacy   |    | tag       |
| password    |     | page_title  |    | notes     |    |           |
| created_at  |     | updated_at  |    |           |    |           |
| updated_at  |     | clicks      |    |-----------|    |-----------|
---------------     | page_title  |
                    --------------

Previously I collected my data from two tables (which worked fine), but after feeling the need to allow tagging, this required an additional table.

my original query looks like this:

    public static function previouslyShortened($username)
    {
            //Gets all related info for the info for member page
            $query = DB::select("select users.id as user_id,
                    links.id,
                    links.long_url,
                    links.short_url,
                    links.updated_at,
                    links.clicks,
                    links.page_title,
                    users_links.privacy,
                    users_links.notes,
                    from users
                    right outer join users_links on users.id = users_links.user_id
                    join links on links.id = users_links.link_id
                    where users.username = '$username'
                    ". (Auth::check() && Auth::user()->username == $username ? "" : "and users_links.privacy = 0")
            );

            $result = array('username' => $username, 'links' => $query);
            return $result;
    }

I would like to also include tags.id and tags.tag from the table tags within the query and return it as part of the object being created.

I have attempted this by trying to create another right outer join but I get the error:

Syntax error or access violation - Not unique table alias.

How can I do this?

edit:

My attempt at the fix was requested so here it is:

    public static function previouslyShortened($username)
    {
            //Gets all related info for the info for member page
            $query = DB::select("select users.id as user_id,
                    links.id,
                    links.long_url,
                    links.short_url,
                    links.updated_at,
                    links.clicks,
                    links.page_title,
                    users_links.privacy,
                    users_links.notes,
                    tags.id,
                    tags.tag
                    from users
                    right outer join users_links on users.id = users_links.user_id
                    join links on links.id = users_links.link_id
                    right outer join tags.user_id on users_links.id = tags.user_id
                    join tags on users_links.user_id = tags.user_id
                    where users.username = '$username'
                    ". (Auth::check() && Auth::user()->username == $username ? "" : "and users_links.privacy = 0")
            );

            $result = array('username' => $username, 'links' => $query);
            return $result;
    }

Why don't you post your attempt at the additional right outer join? — Tim S
– Tim S, Commented Jul 22, 2014 at 22:43
tags.id and links.id would both be in your query then...give them different names than 'id' and you should be good. If not, post the attempt for us — Twelfth
– Twelfth, Commented Jul 22, 2014 at 22:45
@TimS I figured it would complicate things further if I had completely wrong code. I added the attempt in the attempt in the original post now. Although now I get the error that the tags.user_id doesn't exist in 'base table'. i.gyazo.com/ca4360986101ea0b52697a76b128188f.png — samayres1992
– samayres1992, Commented Jul 22, 2014 at 22:56
Not sure why you would right outer join...do you understand the differences between outter and inner...and left outer ve right outer? Right outer will produce one row for every row in the 'right' table in the join...do you really want one row for each tag regardless of if it has a match in your main query? — Twelfth
– Twelfth, Commented Jul 22, 2014 at 22:59

dkkumargoyal · Accepted Answer · 2014-07-23 00:13:14Z

1

to remove the error you can try the following code

public static function previouslyShortened($username)
{
        //Gets all related info for the info for member page
        $query = DB::select("select users.id as user_id,
                links.id,
                links.long_url,
                links.short_url,
                links.updated_at,
                links.clicks,
                links.page_title,
                users_links.privacy,
                users_links.notes,
                tags.id,
                tags.tag
                from users
                left join users_links on users.id = users_links.user_id
                left join links on links.id = users_links.link_id
                left join tags on users.id = tags.user_id
                where users.username = '$username'
                ". (Auth::check() && Auth::user()->username == $username ? "" : "and users_links.privacy = 0") . " group by links.id"
        );
        $result = array('username' => $username, 'links' => $query);
        return $result;
}

edited Jul 23, 2014 at 0:13

answered Jul 22, 2014 at 23:14

dkkumargoyal

5563 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

samayres1992 Over a year ago

Hi thanks for help, this query returns no errors, but it also doesn't return any data. I tried a var_dump on the return and, nothing comes back.

dkkumargoyal Over a year ago

@samayres1992 for data you can check the query without where conditions and then try to put conditions to check for data and relationships are correct between rows.

Twelfth Over a year ago

@dkkumargoyal - his joins are funny and a right outer combined with an inner and then right outer to an inner join....odds are the rows are being eliminated somewhere along that path. He needs to redo his joining starting at users

dkkumargoyal Over a year ago

@Twelfth I agree, I also have some confusions in the table structure, like user_id in links table along with a user_links relationship table, both link_id and user_id in the tags table etc.. and why joining tags table twice

samayres1992 Over a year ago

@dkkumargoyal To be perfectly honest with you I'm unsure myself, a few months back I was at uni and asked my friend to help, that's what he told me to do, apparently (clearly) it's wrong.

|

Twelfth · Accepted Answer · 2014-07-22 23:56:34Z

1

From our chat, here is the from statement:

from users 
inner users_links on users.id = users_links.user_id 
inner join links on links.id = users_links.link_id 
left join tags as t1 on users_links.id = t1.user_id 
left tags as t2 on users_links.user_id = t2.user_id

answered Jul 22, 2014 at 23:56

Twelfth

7,2083 gold badges30 silver badges35 bronze badges

Collectives™ on Stack Overflow

Selecting columns from multiple tables?

edit:

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

edit:

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related