table :First
..............................................................................
id | ros | table | column | stool | date | places|
..............................................................................
1 12 5 6 a 2017-07-17 goa |
2 12 5 6 b 2017-07-17 delhi|
..............................................................................
table :second
..........................................................................
id | ros | name | email | phone | stos|address |date |
....................................................................................................
5 12 and and@her.com 394924673464 6 fddsfds 2017-07-03 |
6 12 her her@and.com 84838593894 6 fdafdfd 2017-07-04 |
....................................................................................................
Query like I am Using:
SELECT `p`.* FROM `first` `p` LEFT JOIN `second` as `st` ON `st`.`ros`=`p`.`ros` ORDER BY `id` DESC LIMIT 10;
My question is why it returns 4 rows instead of just 2? its resturning 4 value without having in the table should i need to use distinct or group by for that ??
Try this query :
SELECT p.* FROM first as p LEFT JOIN second as st ON st.site_id=p.site_id group by p.id ORDER BY p.id DESC LIMIT 10;
It returning 4 columns Because your tables has duplicate site_id.
Change your query with this.This will return you only 2 rows.
SELECT `p`.* FROM `first` `p` LEFT JOIN `second` as `st` ON `st`.`site_id`=`p`.`site_id` GROUP BY p.id ORDER BY `id` DESC LIMIT 10;
It return 4 rows because of all of the site_id in both table is 11. to make it two columns simply just add Group By in your sql statements.
Just Like :
SELECT `p`.* FROM `first` `p` LEFT JOIN `second` as `st` ON
`st`.`site_id`=`p`.`site_id` GROUP BY `p`.`id` ORDER BY `id` DESC LIMIT 10;
The answer to your question "Why" is : This is normal as you are joining on site_id and every row in your first table has 2 matching site_id's rows in the second table so 2 * 2 = 4
#first table
+-----+---------+
| id | site_id |
+-----+---------+
| 31 | 11 |
+-----+---------+
| 32 | 11 |
+-----+---------+
# second table
+-----+---------+
| id | site_id |
+-----+---------+
| 5 | 11 |
+-----+---------+
| 6 | 11 |
+-----+---------+
When you join 31 from the first table to the second table on site_id you will get 2 rows (31 with 5, and 31 with 6)
And when you join 32 from the first table to the second table on site_id you will get 2 rows (32 with 5, and 32with 6)
you need to group the result on the person as @Sonu Bamniya said