MySQL query pulling from two tables, display in correct fields

Question

I'm trying to select all fields in two separate tables as long as they're sharing a common ID.

//mysql query
$result = mysql_query("SELECT * FROM project, links 
WHERE project.id = links.id and project.id = $clientID")

//displaying the link 
if ($row['url'] != null){
    echo "<div class='clientsection' id='links'>Links</div>";
    echo "<a class='clientlink' id='link1' href='" . $row['url'] . "'>" . $row['name'] . "</a>";
}
else {
echo "<a class='clientlink' id='link1' href='" . $row['url'] . "' style='display:none;'>" . $row['name'] . "</a>";
        };

As you can see, my tables are "projects", and "links" Each is sharing a common field "id" for reference. It looks as though where both links.id and project.id are equal, it outputs anything, but when there is no links.id associated with a given $clientID the container relative to the $clientID doesn't display at all.

Essentially I'm using this to add links dynamically to a specific client in this CMS and if there are no links, I want the container to show up anyway.

Hopefully I've expressed myself clearly, any pointers in the right direction are appreciated. Thanks!

Marcus Adams · Accepted Answer · 2010-04-27 22:31:07Z

5

Using the SQL ANSI explicit join syntax, you can specify a LEFT OUTER JOIN, as opposed to the INNER JOIN that you are doing in your implicit join.

The LEFT OUTER JOIN returns results in the first table even if there are no matching rows in the joining table:

SELECT * FROM project
LEFT OUTER JOIN links 
ON links.id = project.id
WHERE project.id = $clientID

For rows where there is no matching id in the links table, links.id will be NULL.

Edit

If you want just the first link, where the autoincrement primary key links.linkid specifies the order, you can do a self join like this:

SELECT * FROM project
LEFT OUTER JOIN links
ON links.id = project.id
LEFT OUTER JOIN links l2
ON l2.id = project.id AND l2.linkid < links.linkid
WHERE project.id = $clientID AND l2.id IS NULL

What this does is join in any links table rows where the linkid is smaller, and then exludes those rows in the WHERE clause. You end up with just the rows where there is no smaller linkid, hence the link with the smallest linkid for each project.

edited Apr 27, 2010 at 22:31

answered Apr 27, 2010 at 21:58

Marcus Adams

54k9 gold badges98 silver badges146 bronze badges

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

7 Comments

Matt Nathanson Over a year ago

Thanks Marcus for your answer, although this has left me with something else to fix. Now, it is opening the correct containers, but on individual client IDs where there is more than one link, it displays multiple containers, with one showing the first link, and then another one created with each subsequent link

Marcus Adams Over a year ago

@Matt, is there a column in the links table that specifies the order of the links, so that we can find the "first" link?

Matt Nathanson Over a year ago

I have a primary key, linkid that is obviously ordered, would that work?

Marcus Adams Over a year ago

@Matt, I've updated my answer for your second question. This can also be done with a sub query.

Matt Nathanson Over a year ago

Hey Marcus, I really appreciate your time. I'm actually looking to display all the links, if there were 100 links for this $clientID i'd want to display all of them.. the edited query you posted actually causes zero links to display..

|

Germ · Accepted Answer · 2010-04-27 22:09:35Z

1

You're query is essentially doing an "INNER JOIN" so it will only return records where there is matching id's in both tables. If you do an "LEFT OUTER JOIN" as in Marcus's example it will return the results you are looking for.

edited Apr 27, 2010 at 22:09

answered Apr 27, 2010 at 22:04

Germ

6,5704 gold badges28 silver badges26 bronze badges

Collectives™ on Stack Overflow

MySQL query pulling from two tables, display in correct fields

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related