ItGo.me Focus on IT Recommend

Home > php - Why would Inner Join in MySQL return unrelated information?

php - Why would Inner Join in MySQL return unrelated information?

2020腾讯云8月秒杀活动,优惠非常大!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1040

2020阿里云最低价产品入口,含代金券(新老用户有优惠),
入口地址https://www.aliyun.com/minisite/goods

I have two tables on my database, one of it is order and the other is user.

I want to pull the information for an order along with the associated user that made the order, to do this, I used an INNER JOIN to join the two tables on the user's name.

However, the issue is that, it is bringing up the correct order information, but the user is not the related user and is instead the first user record in the database.

Why in the world is this happening?

Here is my query:

    SELECT orders.oid, 
           orders.ordernumber, 
           orders.date_requested, 
           orders.date_approved, 
           orders.status, 
           orders.region, 
           orders.users_name, 
           orders.comments, 
           orders.customShippingAddress, 
           orders.approval_comments, 
           orders.approved_by_id, 
           users.firstname, 
           users.lastname, 
           users.address, 
           users.address2, 
           users.city, 
           users.`state`, 
           users.zip, 
           users.phonenum, 
           users.cellnum, 
           users.officenum, 
           users.region, 
           users.shipping_address 
      FROM orders 
INNER JOIN users 
        ON orders.users_name=users.firstname + ' ' + users.lastname
     WHERE orders.oid='$id'";

I don't understand why this isn't working. Can anyone provide me with some direction please?

Recommend:mysql - INNER JOIN condition in WHERE clause or ON clause

n this query: SELECT e.id FROM employees e JOIN users u ON u.email=e.email WHERE u.id='139840' but I accidentally ran this query SELECT e.id FROM employees e JOIN users u ON u.email=e.email AND u.id='139840' (note the AND instead of WHERE

php mysql inner-join where-clause concat
|
  this question
edited Apr 3 '16 at 21:50 Prix 14.6k 11 43 104 asked Apr 3 '16 at 21:45 SherwoodPro 67 7 1   You are joining on something that could very well not be unique, you should have something more like inner join... order.user_id = user.id –  Anees Saban Apr 3 '16 at 21:48      If the user makes multiple orders, then wouldn't I have the same problem for uniqueness? –  SherwoodPro Apr 3 '16 at 21:50 1   No, a user can have multiple orders. One user per order though. But what I meant was the fact that if you have 2 users with the same name, they will both be possible matches for this order. It will simply choose the first match found though. –  Anees Saban Apr 3 '16 at 21:51 1   orders.users_name what happens when you have multiple users with the same name and lastname? :) I would suggestion to save the user_id only in the order and LEFT JOIN on the user_id –  Prix Apr 3 '16 at 21:51 1   @SherwoodPro well in MySQL + is only addition not concatenation like in php and some other languages, which is why there is a explicit CONCAT. Nevertheless you should 1) use prepared statement and stop injecting variable directly to your query unless no options(and if no options ensure they are safe to use, using other means) to be safe against SQL Injection and 2) use the user id on your orders rather than something ambiguous like first and last name so if at a later stage you have multiple users that have similar names, that won't produce an issue. –  Prix Apr 3 '16 at 22:19  |  show more comments

1 Answers
1

---Accepted---Accepted---Accepted---

I`m guessing you want

ON orders.users_name=CONCAT(users.firstname ,' ',users.lastname)

Your version

ON orders.users_name=users.firstname + ' ' + users.lastname

will probably make some conversions under the hood trying to add things up,+ concats things in sql server,not mysql


|
  this answer
answered Apr 3 '16 at 21:52 Mihai 16.9k 5 29 48      That works great. If you don't mind me asking, when would I use the + instead of CONCAT if it can mix things up like that? –  SherwoodPro Apr 3 '16 at 21:59 1   @SherwoodPro only in sql server can you use + to concat things,it `s just that mysql is VERY permissive allowing you to add up strings,leading to unpredictable results –  Mihai Apr 3 '16 at 22:00

 | 

Recommend:mysql - where clause in inner join query

o; $this->query = "SELECT task.employee_id , task.user_id , task.service_id, service.name AS servicename , service.description AS servicedescription, employee.name AS employeename, employee.pic_path AS employeepicture, e

oriUlr:http://stackoverflow.com/questions/36391593/why-would-inner-join-in-mysql-return-unrelated-information/36391666

------splitte line----------------------------