I'm guessing the (DATE)checkInDateTime is an attempt at typecasting, and you're getting the sql error because that's not the right syntax... try CAST(checkInDateTime AS DATE) instead.
MySQL INNER JOIN以日期为条件
I'm trying to use inner join to get the data from two tables using date as one of the condition.
But since the second table contain dateTime column type, I want to use only the date to check for condition.
When i run the code using Postman, it says I have error in SQL syntax at line (DATE)checkInDateTime = $rideDate .
I also have test the SQL without taking the date into condition and the SQL works .
Is there any ways to use the date as condition in InnerJoin method? Please help me .
Thanks.
P/s : my dateTime column store values such as 2015-01-08 11:18:02
//get all rides from table rides
$result = mysql_query("SELECT first.ID, first.fullname,
second.checkInDateTime FROM first INNER JOIN second ON first.ID =
second.riderID WHERE second.ridesID = $rideID AND second.
CAST(checkInDateTime AS DATE) = $rideDate") or die(mysql_error());
//check for empty result
if(mysql_num_rows($result) > 0) {
//loop all result and put into array riders
$response["riders"] = array();
while ($row = mysql_fetch_array($result)) {
//temp array
$rider = array();
$rider["riderID"] = $row["ID"];
$rider["riderName"] = $row["fullname"];
$rider["timeCheckedIn"] = $row["checkInDateTime"];
//push single ride into final response array
array_push($response["riders"], $rider);
}
//success
$response["success"] = 1;
//print JSON response
echo json_encode($response);
} else {
//no rides found
$response["success"] = 0;
$response["message"] = "No riders found";
//print JSON response
echo json_encode($response);
}
- 点赞
- 写回答
- 关注问题
- 收藏
- 复制链接分享
- 邀请回答
1条回答
-
采纳
点赞 评论 复制链接分享
提交
相关推荐
- 回答 1 已采纳 I'm trying to use inner join to get the data from two tables using date as one of the condition. But since the second table contain dateTime column type, I want to use only the date to check for condition. When i run the code using Postman, it says I have error in SQL syntax at line (DATE)checkInDateTime = $rideDate . I also have test the SQL without taking the date into condition and the SQL works . Is there any ways to use the date as condition in InnerJoin method? Please help me . Thanks. P/s : my dateTime column store values such as 2015-01-08 11:18:02 //get all rides from table rides $result = mysql_query("SELECT first.ID, first.fullname, second.checkInDateTime FROM first INNER JOIN second ON first.ID = second.riderID WHERE second.ridesID = $rideID AND second. CAST(checkInDateTime AS DATE) = $rideDate") or die(mysql_error()); //check for empty result if(mysql_num_rows($result) > 0) { //loop all result and put into array riders $response["riders"] = array(); while ($row = mysql_fetch_array($result)) { //temp array $rider = array(); $rider["riderID"] = $row["ID"]; $rider["riderName"] = $row["fullname"]; $rider["timeCheckedIn"] = $row["checkInDateTime"]; //push single ride into final response array array_push($response["riders"], $rider); } //success $response["success"] = 1; //print JSON response echo json_encode($response); } else { //no rides found $response["success"] = 0; $response["message"] = "No riders found"; //print JSON response echo json_encode($response); }
- 回答 4 已采纳 通过下面sql查询商品库存数量，因为库存表里面的库存是很多仓库的库存数据，在left join的时候又要做一个表连接，这样查询的时候效率非常低，如果left join一个表就很快，但是无法满足需求，请大神门帮忙优化下sql，谢谢 select g.*,gsd.number from goods g left join (select gs.goods_id,sum(gs.number) number from goods_stock gs,warehouses w where gs.warehouse_id=w.id group by gs.goods_id and w.id='123456789') gsw on(g.id=gsd.goods_id)
- 回答 3 已采纳 I have planned to use three tables for my sites tagging system, they looks like: |-----------------------| |==> photo | | -> id | | -> custom_id | | -> title | | -> slug | | -> date | | -> image_url | |-----------------------| |==> tags | | -> id | | -> slug | | -> title | |-----------------------| |==> tags_relation | | -> tid | <-- this is the tags.id | -> pid | <-- this is the photo.custom_id |-----------------------| So, to fetch the recent posts of a specific tag i am using INNER JOIN by the following query: SELECT p.id, p.custom_id, p.title, p.slug, p.date, p.image_url, t.id, t.slug, tr.* FROM tags t INNER JOIN tags_relation tr ON t.id = tr.tid INNER JOIN photo p ON p.custom_id = tr.pid WHERE t.slug = 'people' ORDER BY p.date DESC LIMIT 20 Everything works fine except the fact that the 'slug, id, title' column of the photo table is being replaced by the 'slug, id, title' column of the tags table! I figured out a solution that is changing the tags columns name, but is there any best practices to solve this problem? Thanks
- 回答 2 已采纳 Sorry if this has been asked before, I have searched and tried several similar solutions, though none quite the same and none worked for me. I am trying to update a field 'contactable' in TableA (customers), which should be either 0 or 1 depending on whether a customer has opted out of receiving our emails. All values in this field are currently '1'. If they have opted out, their email address will be present in the 'address' field of TableB (unsubscribes). So I am trying to use INNER JOIN to join TableA and TableB (both of which have an 'address' field) and if any the addresses in TableB match those in TableA, set 'contactable' in TableA from '1' to '0'. Any help would be much appreciated. I gather it should be quite a simple operation, though I am new to SQL.
- 回答 3 已采纳 I have tables "customers" and "plans" and I want to list all customers regardless they have a plan or not. I'm using query to do it SELECT customer.name, plan.goal FROM customer LEFT JOIN plan ON plan.customerid=customer.customerid ORDER BY customer.name I also want to see the goal (plan.goal) with the customer name. This works as long as customer does not have a plan or has a single plan. If customer has two or more plans then I get as many rows of customer name as there are plans. What I want is customer.name and plan.goal from the latest plan. We can assume the higher value in plan.planid is the latest plan. I guess I should use sub queries and INNER JOINS some how but I just don't get it right now...
- 回答 6 已采纳 最近和同学一起做毕业项目，遇到一个多表join的问题 先上下原本的sql  这个速度是0.5秒  这个是0.1秒 这个是为什么？不是说在join查询的时候在结尾使用where会影响速度么，怎么反而更快？
- 回答 3 已采纳 I can't seem to figure out how to order by in this MySQL select. I hope you can help me out. Tabels: categories catId, catParentId, catDisplay 1 0 1 2 0 1 3 0 1 4 1 1 5 1 1 categories_translation transId, catId, catName, catDesc, langId 1 1 Title1 Desc1 1 2 2 Title2 Desc2 1 3 3 Title3 Desc3 1 4 4 Title4 Desc4 1 5 5 Title5 Desc5 1 language langId, langName, langCode 1 Danish da 2 English en My query: SELECT `categories`.`catId`, `categories`.`catParentId`, `categories`.`catDisplay`, `categories_translation`.`catName`, `categories_translation`.`catDesc`, `language`.`langCode` FROM `categories` INNER JOIN `categories_translation` ON `categories_translation`.`catId` = `categories`.`catId` INNER JOIN `language` ON `language`.`langId` = `categories_translation`.`langId` WHERE `language`.`langCode` = 'da' Now, I get returned what I want, but is there a way to order the child categories to their parents, so the result looks like this: Desired result: catId | catParentId | catDisplay | catName | catDesc | langCode 1 0 1 Title1 Desc1 da 4 1 1 Title4 Desc4 da 5 1 1 Title5 Desc5 da 2 0 1 Title2 Desc2 da 3 0 1 Title3 Desc3 da I've tried order by, but can seem to get the results like I want.
- 回答 1 已采纳 For a few days now, I can't find a solution for this one issue regarding sorting the data from a joined table. Say I'm making restaurant itineraries for users. Each user has a few restaurants in his itinerary. I have two tables, one that contains the information regarding a user's itinerary (when will he be visiting restaurants, which restaurants), and one containing the information about all the restaurants I know. guides ...................and.............. restaurants userid ---> id restaurant1_id -->--| name date1 | style restaurant2_id -->--| address date2 The "restaurant1_id" or "restaurant2_id" corresponds with the "id" in restaurants table. So I'm inner joining these tables like this: SELECT restaurants.name AS name, restaurants.style AS style, restaurants.address AS address, guides.date1 AS date1, guides.date2 AS date2 FROM `guides` INNER JOIN `restaurants` ON guides.restaurant1_id = restaurants.id or guides.restaurant2_id = restaurants.id WHERE guides.userid = 2 Say I'd like to see all of the info of all of the restaurants a user with ID "2" will be visiting. With the following query, I'm getting the desired result: $result = $conn->query($sql); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { echo $row['name'].'<br>' .$row['style'].'<br>' .$row['address'].'<hr>' ; } } else { echo 'No results.'; } I created a fiddle here. Where I'm having trouble, is sorting the results. I'd like the restaurant that is referred to in the "guides" table as "restaurant1_id" to show up before "restaurant2_id". Another issue is the date. I can't figure out how to show 'date1' next to the name of 'restaurant1_id'. Should I store all the data in variables or is there a better way to approach this? I appreciate any suggestions!
- 回答 2 已采纳 I have a little problem with INNER JOIN in mysql query. I have two tables the first is 'kontrola' and 2nd is 'naruszenia' In 'kontrola' table I have rows: id podmiot miasto wszczeto zakonczono naruszenie_id (Foreign KEY on 'naruszenia' table) In my naruszenia table I have: id naruszenia Now I want to display using INNER JOIN the naruszenie from 'naruszenia' table. I've create somethin linke this: $listakontroli = $connecting->query("SELECT * FROM kontrola INNER JOIN naruszenia ON kontrola.naruszenie_id=naruszenia.id"); But the result is that when I want to display records in table I have changed the ID of first table(kontrola) and the naruszenia_id still showing id from naruszenia table. How to change it to display properly the word not id.
- 回答 2 已采纳 I am new in Mysql. I have multiple tables and i want to join all of them. I use INNER JOIN for this. "SELECT * FROM table INNER JOIN table2 ON table.client_id = table2.id WHERE table2.id= 113 ORDER BY table.id DESC LIMIT 1 "; Here i face a problem that i have a column name title in each table. I want to use AS in my command. Like in outlook table there is name of column is TITLE i want to use it as outlook_title. How it is possible? I want to use echo to print data like this echo '<h1>' .$row["client"]. '</h1>' ; echo '<h1>' .$row["name"]. '</h1>' ; echo '<h1>' .$row["name"]. '</h1>' ; echo '<h1>' .$row["ation_title"]. '</h1>' ; echo '<h1>' .$row["look_title"]. '</h1>' ; Please help me
- 袭冷的博客 一、单表查询 SELECT tab01.col01, tab01.col11 FROM Table_01 tab01 ORDER BY tab01.col01 二、两表查询 SELECT tab01.col01, tab01.col11, tab02.c22 FROM ... INNER JOIN Table_0...
- o_w_l_5_1_6的博客 INNER JOIN关键字表示在表中存在至少一个匹配时，INNER JOIN 关键字返回行。 1、连接两个数据表的用法： FROM Member INNER JOIN MemberSort ON Member.MemberSort=MemberSort.MemberSort 相当于：FROM Member,...
- Saintyyu的博客 今天在Mysql官网（参考博客1）看到一段话： At the parser stage, queries with right outer join operations are converted to equivalent queries containing only left join operations. In the general case, ...
- 铁柱同学的博客 select * from a left join b on a.x = b.x left join c on c.y = b.y left join d on d.z=c.z &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;这样的多个left join组合，一方面是心里有点不舒服，...
- 3年前qq_27449993的博客 字段n FROM 表1 别名1 INNER JOIN 表2 别名2 ON 条件 WHERE 条件 SELECT p.p_NAME,p.p_LINKMAN,b.`B_NAME`,b.`B_PRICE`,b.`B_AUTHOR` FROM `publisher` p INNER JOIN book b ON b.`P_ID`=p.P_ID #---------...
- qq_35317984的博客 INNER JOIN 关联两张数据表的写法： SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号INNER JOIN 关联三张数据表的写法： SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ...
- 马少芳的博客 题目： 图书（图书号，图书名，作者编号，出版社，出版日期） 作者（作者姓名，作者编号，...inner join 两个表 列名输出答案：select 作者.姓名，图书.图书名，图书.出版社 from 作者 inner join 图书 on 作者.
- ThinkWon的博客 文章目录数据库基础知识为什么要使用数据库什么是SQL？什么是MySQL?数据库三大范式是什么mysql有关权限的表都有哪几个MySQL的binlog有有几种录入格式？分别有什么区别？数据类型mysql有哪些数据类型引擎MySQL存储...
- keke_俩个科的博客 2.left join，左链接,以左表为参照,显示所有数据,右表中没有则以null显示 3.right join，右链接,以右表为参照显示数据，,左表中没有则以null显示 例子 如图两个表 右连接 SELECT * FROM ketest as b RIGHT JOIN ...
- 乔志勇笔记的博客 在使用查询语句时，inner join,left join,right join等这些联接语句，想必大家并不陌生，而且经常会用到，联接达到了我们想要的结果，可是当数据越大越大的时候，会发现查询很慢，往往只是加索引进行优化，可是有...