Why does my SQL LEFT JOIN query not work?

It is returning 0 rows when there should be some results.

This is my first attempt at using JOIN but from what I've read this looks pretty much right, right?

"SELECT pending.paymentid, pending.date, pending.ip, pending.payer, pending.type, pending.amount, pending.method, pending.institution, payment.number, _uploads_log.log_filename
FROM pending
LEFT JOIN _uploads_log
ON pending.paymentid='".$_GET['vnum']."'
AND _uploads_log.linkid = pending.paymentid"

I need to return the specified values from each table where both pending.paymentid and _uploads_log.log_filename are equal to $_GET['vnum]

What is the correct way to do this? Why am I not getting any results?

If someone more experienced than me could point me in the right direction I would be much obliged.

EDIT

For pending the primary key is paymentid, for _uploads_log the primary is a col called log_id and log_filename is listed as index.

---

Try this

 SELECT  pending.paymentid,
         pending.date,
         pending.ip,
         pending.payer,
         pending.type,
         pending.amount,
         pending.method,
         pending.institution,
         payment.number,
         _uploads_log.log_filename
FROM     pending
         LEFT JOIN _uploads_log
              ON _uploads_log.linkid = pending.paymentid
WHERE   _uploads_log.log_filename = '" . $_GET['vnum']  . "'

Your current query is vulnerable with SQL Injection. Please take time to read the article below.

Mysql left join does not work

I want to get total transfered items from table inv_site_item where 'item_id' in inv_sie_item = 'item_code' in inv_items, I am getting packing also from packing table which works fine in this query only inv_site_item is giving problem.

error is: Unknown column 'inv_site_item.site_id' in 'field list'

$where .= " AND inv_items.item_code = $item_code";

$query = "SELECT inv_items.*,packing.name_en `packing_name`,"
    . " COUNT(inv_site_item.site_id) `transfer_out`, COUNT(inv_site_item.location_site_id) `transfer_in`  FROM inv_items"
            . " left join "
            . "inv_packing as packing on packing.id=inv_items.packing"
            . " left join "
            . "inv_site_item as transfer on transfer.item_id=inv_items.item_code"
    . " WHERE item_code !='' " . $where . "";

---

you must use your table alias transfer instead, So:

change from

inv_site_item.site_id

to

transfer.site_id

also with inv_site_item.location_site_id to be transfer.location_site_id

MySQL LEFT JOIN does not work correctly

I'm having trouble getting the result out of my query. I want to fetch the sum and total count of an unit sale in transactions, where the transaction is in a specific zip.

Here are my tables:

TABLE unit_type(
    id (Primary key)
    unit_name (varchar)
    department_id (Foreign key)
)

TABLE transaction(
    id (PK)
    commission_fix_out (int)
    transaction_end_week (int)
    property_id (FK)
    unit_type_id (FK)
    ...
)

TABLE property(
    id (PK)
    property_zip_id (FK)
    ...
 )

My unit_types table has the following records:

+-----+----------------------+----------------+
| id  | unit_name            | department_id  |
+-----+----------------------+----------------+
| 1   | WV construction      | 1              |
| 2   | WV resale            | 1              |
| 3   | WV rent              | 1              |
| 4   | BV industrial sale   | 2              |
| 5   | BV industrial rent   | 2              |
| ... | ...                  | ...            |
+-----+----------------------+----------------+

Here's how my query looks like:

SELECT SUM(commission_fix_out), COUNT(commission_fix_out), unit_name, ut.id
FROM unit_type as ut
LEFT JOIN transaction as t
ON ut.id = t.unit_type_id
RIGHT JOIN property as p
ON (p.id = t.property_id AND p.property_zip_id = 1459)
WHERE ut.department_id = 1
GROUP BY unit_name
ORDER BY ut.id

which results in:

+------------+-------------+-------------+---------+
| SUM(...)   | COUNT(..)   | unit_name   | ut.id   |
+------------+-------------+-------------+---------+
| 40014      | 11          | WV resale   | 2       |
| NULL       | 0           | WV rent     | 3       |
+------------+-------------+-------------+---------+

I was expecting another row with WV construction, but it doesn't show up. Anyone who knows where i am wrong with this one?

---

I managed to fix my problem. I'd like to share my result with you:

SELECT SUM(commission_fix_out), COUNT(commission_fix_out), unit_name
FROM unit_type ut
LEFT JOIN transaction t
ON (ut.id = t.unit_type_id AND t.property_id IN (SELECT id FROM property p WHERE
property_zip_id = 1459))
WHERE department_id = 1
GROUP BY unit_name
ORDER BY ut.id

Instead of using an extra JOIN, i'd tried using a subquery in my ON-clause which gives my next results:

+-----------+-----------+-------------------+------+
| SUM(..)   | COUNT()   | unit_name         | id   |
+-----------+-----------+-------------------+------+
| NULL      | 0         | WV construction   | 1    |
| 40014     | 11        | WV resale         | 2    |
| NULL      | 0         | WV rent           | 3    |
+-----------+-----------+-------------------+------+




Mysql LEFT JOIN does not work for 2 tables

Basically I have 2 tables

• Topics
• Users

I am trying to use a left join so that I can link the "posted_by" in "topics" with "user_id" in "users", so that I can output both the users.username for display, as well as users.profile(avatar picture).

Here is my current code, which is giving me boolean errors.

        <?php
    include 'core/init.php';
    include 'includes/overall/header.php';

    $sql = " SELECT *, users.id, users.username, users.profile
        FROM `topics`
        LEFT JOIN
        users ON topics.posted_by = " . mysql_real_escape_string($_GET['topic_id']) . " users.user_id ORDER BY `posted` DESC";

    $result = mysql_query($sql);

    // Start looping table row
    while($rows = mysql_fetch_array($result)){
    ?>
    <table>
      <tr>
        <td rowspan="4"> Avatar code to go here<br>
           <? echo $rows['username']; ?></td>
        <td><? echo $rows['category']; ?> > <? echo $rows['sub_category']; ?> </td>
      </tr>
      <tr>
        <td><? echo $rows['posted']; ?></td>
      </tr>
      <tr>
        <td><? echo $rows['topic_data']; ?></td>
      </tr>
      <tr>
        <td><a href="view_topic.php?id=<? echo $rows['topic_id']; ?>">Reply</a> (<? echo $rows['reply']; ?>) Replies</td>
      </tr>
    </table>

    <?php
    // Exit looping and close connection
    }
    mysql_close();
    ?>

---

I believe you are using something like

$sql = "SELECT users.user_id, users.username, users.profile, topics.topic_id,     topics.category, topics.sub_category,
topics.topic_data, topics.posted_by, topics.posted, topics.view, topics.reply
FROM users WHERE topics.posted_by = users.user_id ORDER BY topics.posted DESC";

Try adding

$sql = "SELECT users.user_id, users.username, users.profile, topics.topic_id, topics.category, topics.sub_category, topics.topic_data, topics.posted_by, topics.posted, topics.view, topics.reply FROM users, topics WHERE topics.posted_by = users.user_id ORDER BY topics.posted DESC";

MySQL Left Outer Join with Count of the attached table, Show all records

I am trying to create a query where there is a count of related records from another table. I'd like the "parent" records whether there are related records (a count) or not.

SELECT r.region_name, r.region_id, r.abbreviation, r.map_order,
    (IFNULL( COUNT( p.property_id ) , 0 )) AS propertyCount
FROM  `rmp_region` r
LEFT OUTER JOIN  `rmp_property` p
    ON p.path LIKE CONCAT(  '%/', r.region_id,  '/%' )
WHERE p.active =1
AND r.parent_region_id =1
GROUP BY r.region_name, r.region_id, r.abbreviation, r.map_order
ORDER BY r.map_order ASC

I've tried different variations of this.. but i cannot get the parent records to display if the count is zero/no related records.

thanks for any help!

---

You need to move "p.active = 1" from the WHERE clause into the OUTER JOIN criteria.

Here's your example with the change:

SELECT r.region_name, r.region_id, r.abbreviation, r.map_order,
    (IFNULL( COUNT( p.property_id ) , 0 )) AS propertyCount
FROM  `rmp_region` r
LEFT OUTER JOIN  `rmp_property` p
    ON p.path LIKE CONCAT(  '%/', r.region_id,  '/%' ) and p.active =1
WHERE r.parent_region_id =1
GROUP BY r.region_name, r.region_id, r.abbreviation, r.map_order
ORDER BY r.map_order ASC




Mysql - LEFT JOIN with COUNT

I have 2 database tables

1. directory
2. directory_category

They are linked by the category id (as cat_id & id) in both tables respectively

---

I want to list all the categories in directory_category and at the same time count how many records are found in directory for this category (using a single sql query)

I have tried

SELECT
      directory_category.id
      directory_category.category_name
      directory.cat_id
      count(directory) as total_records
      FROM directory_category
      LEFT JOIN directory
      ON directory_category.id = directory.cat_id

This query only produces one record and the total_records seems to be the sum of the entire directory table

---

SELECT
    directory_category.id,
    directory_category.category_name,
    directory.cat_id,
    COUNT(directory.id) AS total_records
FROM directory_category
LEFT JOIN directory ON directory_category.id = directory.cat_id
GROUP BY directory_category.id

MySQL - LEFT JOIN and COUNT ()

I have 3 tables:

1. forums

id, name, description

1. threads

thread_id, forum_id, user_id, title, content, views

1. posts

post_id, thread_id, author_id, content, date

What I want to do is to get all the threads in a forum, and get the post count of every thread. So I get every thread (WHERE forum_id = whatever) and then I LEFT JOIN with the table posts so in order to count the results. But something is not working. Here is my query:

SELECT t.*, u.nick, COUNT(p.post_id) AS postcount
  FROM
    threads t
  LEFT JOIN
    users u
    ON
       u.id = t.user_id
  LEFT JOIN
     posts p
     ON
       p.thread_id = t.thread_id
  WHERE
     t.forum_id = $this->forumID

This query will only show (I think) the threads that have any post on it. I also tried using the GROUP BY statement but it makes MySQL error...

How can I solve this?

----------- EDIT: I tried adding GROUP BY t.thread_id, however, as I said before, MySQL errors:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE t.forum_id = 2' at line 15

Full query:

SELECT t.*, u.nick, COUNT(p.post_id) AS postcount
  FROM
    threads t
  LEFT JOIN
    users u
    ON
       u.id = t.user_id
  LEFT JOIN
     posts p
     ON
       p.thread_id = t.thread_id
  GROUP BY
     t.thread_id
  WHERE
     t.forum_id = $this->forumID

EDIT 2:

My bad, I put the GROUP BY statement where it wasn't meant to be. It is now solved.

---

GROUP BY was the right way to go, so just add: GROUP BY t.thread_id

MYSQL LEFT JOIN and COUNT and GROUP BY

I found several threads concerning that task, but all don't really help me, or I'm not experienced enough to understand it.

I am using mysql and have two tables user and user_rh. I want a list (ordered by names) of usernames with the correspond number of entries in user_rh.

      `user`                `user_rh`
------------------     ------------------
| `uid` | `name` |     | `uid` | `zaid` |
------------------     ------------------
|   1   | Bob    |     |   2   |    4   |
|   2   | John   |     |   2   |    7   |
|   3   | Fred   |     |   3   |    2   |
|   4   | Peter  |     ------------------
------------------

So the result should look like:

--------------------
| `name` | `count` |
--------------------
| Bob    |    0    |
| Fred   |    1    |
| John   |    2    |
| Peter  |    0    |
--------------------

I tried this query:

SELECT
    `user`.`name`,
    `user_rh`.`uid`
FROM
    `user`
LEFT JOIN
    `user_rh`
ON (`user_rh`.`uid`=`user`.`uid`)
ORDER BY
    `user`.`name`

It is going in the right way, but this will not return count values:

------------------
| `name` | `uid` |
------------------
| Bob    |  NULL |
| John   |   2   |
| John   |   2   |
| Fred   |   1   |
| Peter  |  NULL |
------------------

I thought I just add the COUNT() and GROUP BY commands:

SELECT
    `user`.`name`,
    COUNT(`user_rh`.`uid`) AS `count`
FROM
    `user`
LEFT JOIN
    `user_rh`
    ON (`user_rh`.`uid`=`user`.`uid`)
GROUP BY
    `user_rh`.`uid`
ORDER BY
    `user`.`name`

But it shows me something like this. First it delivers a sorted list of names that have count!=0 and the last entry of the list is the first entry of table user which has count=0.

------------------
| `name` | `uid` |
------------------
| John   |   2   |
| Fred   |   1   |
| Bob    |   0   |
------------------

I think, I'm just combining the commands in a wrong way.

---

Your 2nd query is fine. Just group by the user from the first table. Otherwise you would turn your left join into an inner join

SELECT
    `user`.`name`,
    COUNT(`user_rh`.`uid`) AS `count`
FROM
    `user`
LEFT JOIN
    `user_rh`
    ON (`user_rh`.`uid`=`user`.`uid`)
GROUP BY
    `user`.uid, `user`.`name`
ORDER BY
    `user`.`name`




Mysql Left Join with conditional on the right table

I'm having trouble figuring out the sql for the following problem of mine. I have two tables like this:

+----------+------------+------------+
| event_id | event_name | event_date |
+----------+------------+------------+

+---------------+----------+---------+--------+
| attendance_id | event_id | user_id | status |
+---------------+----------+---------+--------+

What I am trying to do is to get a table like this:

+----------+--------+
| event_id | status |
+----------+--------+

Where the conditional for the second attendance table is the user_id. I'm trying to get a list of all the events as well as the status of a user for each one of those events, even if there is no record inside attendance (NULL is ok for now). And again, the status data from the attendance table needs to be chosen by the user_id.

From my initial research, I thought this would work:

SELECT event_id, status FROM events LEFT JOIN attendance WHERE attendance.user_id='someoutsideinput' ORDER BY event_date ASC

But that is not working for me as expected..how should I go about this?

Thanks!

---

all you need to do is to move the condition in the WHERE clause into ON clause.

SELECT events.event_id, COALESCE(attendance.status, 0) status
FROM events LEFT JOIN attendance
     ON events.event_id = attendance.event_id AND
        attendance.user_id='someoutsideinput'
ORDER BY events.event_date ASC




Mysql LEFT JOIN with COUNT returns an unexpected value

I've two tables:

post:

id | body   | author | type | date
1  | hi!    | Igor   | 2    | 04-10
2  | hello! | Igor   | 1    | 04-10
3  | lol    | Igor   | 1    | 04-10
4  | good!  | Igor   | 3    | 04-10
5  | nice!  | Igor   | 2    | 04-10
6  | count  | Igor   | 3    | 04-10
7  | left   | Igor   | 3    | 04-10
8  | join   | Igor   | 4    | 04-10

likes:

id | author | post_id
1  | Igor   | 2
2  | Igor   | 5
3  | Igor   | 6
4  | Igor   | 8

And I want to do a query that returns the number of posts made by Igor with type 2, 3 or 4 and number of likes made by Igor too, so, I did:

SELECT COUNT(DISTINCT p.type = 2 OR p.type = 3 OR p.type = 4) AS numberPhotos, COUNT(DISTINCT l.id) AS numberLikes
FROM post p
LEFT JOIN likes l
ON p.author = l.author
WHERE p.author = 'Igor'

And the expected output is:

array(1) {
  [0]=>
  array(2) {
    ["numberPhotos"]=>
    string(1) "6"
    ["numberLikes"]=>
    string(2) "4"
  }
}

But the output is:

array(1) {
  [0]=>
  array(2) {
    ["numberPhotos"]=>
    string(1) "2"
    ["numberLikes"]=>
    string(2) "4" (numberLikes output is right)
  }
}

So, how to do this?

---

The problem is that p.type = 2 OR p.type = 3 OR p.type = 4 evaluates to either 1 or 0, so there are only 2 possible distinct counts.

To fix the issue, you can use a case statement:

COUNT(DISTINCT case when p.type in (2,3,4) then p.id end)




Left Outer Join does not get any results

I want to join the tables which would have all values from the table Portal_Order alongwith other matching values from other tables. LEft outer Join returns no values

Query :

With Max_Date As (Select Max(Change_Date) Change_Date, Ocoe_Job_Id
From Ocoe_Job_Status Where Ocoe_Job_Id In
(Select Ocoe_Job_Id From Portal_Order Where To_Char(Created_Date,'YYYY-MM-DD')
Between ( Select To_Char(Sysdate-1000,'YYYY-MM-DD') From Dual)
And ( Select To_Char(Sysdate,'YYYY-MM-DD') From Dual)  )
Group By Ocoe_Job_Id) 

Select Order_Id, Order_Name, Order_Desc, B.Description As Order_Status, C.Ocoe_Job_Id, C.Comments
As Communication_Id , Communication_Name, Created_By, Count(*) Over () As Total_Record_Count, Row_Number()
Over ( Order By Order_Id ) Row_Number
From  Max_Date D,
Ocoeowner.Portal_Order A
Left Outer Join Ocoeowner.Ocoe_Job_Status C On C.Ocoe_Job_Id =A.Ocoe_Job_Id
LEFT OUTER JOIN Ocoeowner.Portal_Order_Status_Code_Lk B on A.Order_Status = B.Status_Code
 Where To_Char(Created_Date,'YYYY-MM-DD')
Between ( Select To_Char(Sysdate-1000,'YYYY-MM-DD') From Dual) And ( Select To_Char(Sysdate,'YYYY-MM-DD') From Dual)
And C.Ocoe_Job_Id = D.Ocoe_Job_Id And D.Change_Date = C.Change_Date AND communication_name='ptuletters';

---

The predicate condition needs to be in the On clause for the join, not in the where clause. The way Outer joins work, is after the join conditions are analyzed, all the rows from the "outer side" that do not match the inner side are added back in.... But this all happens before the where clause is processed. So if the where clause predicate filters on an attribute from the outer side of an outer join, all those rows will be removed again... (They are all null). Put the predicate in the join condition instead

And C.Ocoe_Job_Id = D.Ocoe_Job_Id And D.Change_Date = C.Change_Date

has to be moved, and may be too AND communication_name='ptuletters', depending of the source table

LEFT JOIN does not work for 2 tables

Basically I have 2 tables

• Topics
• Users

I am trying to use a left join so that I can link the "posted_by" in "topics" with "user_id" in "users", so that I can output both the users.username for display, as well as users.profile(avatar picture).

Here is my current code, which is giving me boolean errors.

        <?php
    include 'core/init.php';
    include 'includes/overall/header.php';

    $sql = " SELECT *, users.id, users.username, users.profile
        FROM `topics`
        LEFT JOIN
        users ON topics.posted_by = " . mysql_real_escape_string($_GET['topic_id']) . " users.user_id ORDER BY `posted` DESC";

    $result = mysql_query($sql);

    // Start looping table row
    while($rows = mysql_fetch_array($result)){
    ?>
    <table>
      <tr>
        <td rowspan="4"> Avatar code to go here<br>
           <? echo $rows['username']; ?></td>
        <td><? echo $rows['category']; ?> > <? echo $rows['sub_category']; ?> </td>
      </tr>
      <tr>
        <td><? echo $rows['posted']; ?></td>
      </tr>
      <tr>
        <td><? echo $rows['topic_data']; ?></td>
      </tr>
      <tr>
        <td><a href="view_topic.php?id=<? echo $rows['topic_id']; ?>">Reply</a> (<? echo $rows['reply']; ?>) Replies</td>
      </tr>
    </table>

    <?php
    // Exit looping and close connection
    }
    mysql_close();
    ?>

---

I believe you are using something like

$sql = "SELECT users.user_id, users.username, users.profile, topics.topic_id,     topics.category, topics.sub_category,
topics.topic_data, topics.posted_by, topics.posted, topics.view, topics.reply
FROM users WHERE topics.posted_by = users.user_id ORDER BY topics.posted DESC";

Try adding

$sql = "SELECT users.user_id, users.username, users.profile, topics.topic_id, topics.category, topics.sub_category, topics.topic_data, topics.posted_by, topics.posted, topics.view, topics.reply FROM users, topics WHERE topics.posted_by = users.user_id ORDER BY topics.posted DESC";

Mysql Access LEFT JOIN does not return results

I have three tables: Comments, Users and CommentsHelpfulness.

Users can submit several comments, which are stored in the Comments table.

CommentsHelpfulness has three columns: UserID, CommentID and a "Helpful" Boolean. Every user can indicate for every comment if they find it useful, which will create an entry in the CommentsHelpfulness table.

I want a query that gives me all Comment IDs, with the name of the user that submitted it and shows whether the currently logged in user found it helpful, did not find it helpful or did not say anything about it. So the ID of a comment the current user did not express his opinion about should still be output, just without the helpful Boolean.

To me that sounds like it should be done like this using a left join:

SELECT Comments.ID, Users.Nom, CommentsHelpfulness.Helpful FROM (Comments INNER JOIN Users ON Comments.UserID = Users.ID) LEFT JOIN CommentsHelpfulness ON (CommentsHelpfulness.CommentID = Comments.ID AND (CommentsHelpfulness.UserID = ?))

Unfortunately this does not output Comment IDs without an entry in the CommentsHelpfulness table. Why does this not work? Is it because of Access?

---

I think the issue is the inner join, not the left join.

Try removing that table:

SELECT c.ID, ch.Helpful
FROM Comments as c LEFT JOIN
     CommentsHelpfulness as ch
     ON ch.CommentID = c.ID AND
        ch.UserID = ?

Mysql LEFT JOIN does not work as expected when retrieving data from 2 tables

I have 2 tables, named subscriptions and tags. Once in a month I will have to create tags for each order.

I have to list subscriptions without a tag for the given month (2011-10-01 or 2011-09-01 etc). This query returns 0 records:

   SELECT s.id, s.active, s.status
     FROM asw_subscriptions as s
LEFT JOIN asw_tags as t ON s.id = t.subscription_id
    WHERE t.subscription_id IS NULL
      AND t.deliveryDate = '2011-10-01'
      AND s.status = '2'
      AND s.active = '1'
 ORDER BY s.id DESC
    LIMIT 0, 25

Table Structure

---

subscriptions = id (int / auto), active (enum : 0,1), status (enum : 0,1)

tags = id (int / auto), deliveryDate (date), tagNumber

---

The problem is in clausule

t.deliveryDate = '2011-10-01' AND

You have not record on the left because condition 'IS NULL' eliminates LEFT JOIN'ed records. So, above condition will eliminate all join products, because will never be true (there will be always null in t.deliveryDate.

Try something like this:

SELECT s.id, s.active, s.status
     FROM asw_subscriptions as s
WHERE s.status = '2'
      AND s.active = '1'
      AND NOT EXISTS (
         SELECT 1
         FROM asw_tags as t
         WHERE s.id = t.subscription_id
         AND t.deliveryDate = '2011-10-01'
      )
 ORDER BY s.id DESC
    LIMIT 0, 25




Mysql Left join does not work as expected

I am using a simple Left Join in my query.But the problem query is not working as expected.

Query Is

select m.* from Menu m
left join MenuRole mr on m.Id=mr.MenuID
where mr.DesignationID=1

Menu Table

MenuRole Table

Output of query

The problem is the Employee data is missing from the result and the parentID columnwith Id=0 is also missing.

---

This is caused by the where clause, when threre is no match DesignationId is null and therefore DesignationId = 1 is not true.

Try this:

select m.* from Menu m
left join MenuRole mr on m.Id=mr.MenuID and mr.DesignationID=1

Mysql LEFT JOIN does not work with account

I have a problem with my query:

SELECT table1.Name,
       COUNT(*)
FROM table1
LEFT JOIN table2 ON table2.Name_all = table1.Name
GROUP BY table1.Name

It shows and counts a names from table1. I want to join all names from table2 which do not exist in table1.

Maybe someone knows what I am doing wrong?

Thanks in advance

---

From your description you seem to mean this, which is a list of name_all that does not match table1 name.

SELECT table2.Name_all
FROM table2
LEFT JOIN table1 ON table2.Name_all = table1.Name
WHERE table1.Name Is Null

If you need a count as well, you can say:

SELECT table2.Name_all, Count(table2.Name_all) AS CountOf
FROM table2
LEFT JOIN table1 ON table2.Name_all= table1.Name
WHERE table1.Name Is Null
GROUP BY table2.Name_all;