CODEWITHSUNDEEP
mysqlsql
user5199117
user5199117

Reputation:

Where is the error in my full outer join?

I'm new to SQL and am wondering what is wrong in my syntax or logic here. I have my own table clients like

id   |  name      |  postid  |  sord  |
--------------------------------------------------------
1       Paul Allen   19         1
2       McDermot     8          2

and the postid column in that table is referring to the same thing as the id column in the wp_posts table like 

id | ... | ... |           guid            | ... 
--------------------------------------------------
1   ...    ...     images/somepic.jpg        ... 
.
.
8   ...    ...     images/mypic.gif          ... 
.
.
.
19   ...   ...     images/thatpic.png        ...
.
.
.

What I want to return is a table of rows like 

id   |  name      |  postid  |  sord  |   guid 
--------------------------------------------------------
1       Paul Allen   19         1         images/mypic.gif
2       McDermot     8          2         images/thatpic.png

so that I have the corresponding image paths that I can use to build out my page. I believe the method for this scenario is a full outer join. My PHP is like

global $wpdb;
$allCients = $wpdb->get_results("
    SELECT clients.id, clients.name, clients.postid, clients.sord, wp_posts.guid 
    FROM clients 
    FULL OUTER JOIN wp_posts
    ON clients.postid=wp_posts.id
    ORDER BY clients.sord
");

$numClients = count($allCients);

but for some reason that is returning 0 results (i.e. $numClients is 0) instead of the expected 2. What am I doing wrong?

Upvotes: 0

Views: 78

Answers (3)

Diego Fu
Diego Fu

Reputation: 420

You don't even need to do a LEFT JOIN in this case.

Simply take out the FULL OUTER like other mentioned.

So your code would be:

global $wpdb;
$allCients = $wpdb->get_results("
    SELECT clients.id, clients.name, clients.postid, clients.sord, wp_posts.guid 
    FROM clients 
    JOIN wp_posts
    ON clients.postid=wp_posts.id
    ORDER BY clients.sord
");

$numClients = count($allCients);

Upvotes: 0

mattslone
mattslone

Reputation: 307

If you're using MySQL, there is no FULL OUTER JOIN. What you can do instead is a LEFT OUTER JOIN, a UNION and a RIGHT OUTER JOIN. This stack overflow answer has an example:

https://stackoverflow.com/a/10137216/4629105

select * from A as a
    left outer join B as b on a.col = b.col
union
select * from A as a
    right outer join B as b on a.col = b.col

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

MySQL does not support full outer join. But you don't need it anyway, if you have a proper foreign key relationship. For instance, if you want all clients, even those without posts:

SELECT c.id, c.name, c.postid, c.sord, p.guid 
FROM clients c LEFT JOIN
     wp_posts p
     ON c.postid = p.id
ORDER BY c.sord;

Upvotes: 1

Related Questions