i want to do left join that should not return same multiple rows
-
hello to all .. i m beginner to mysql and php and i want to do the LEFT JOIN of three tables with data given below CREATE TABLE IF NOT EXISTS `history` ( `history_id` int(5) NOT NULL AUTO_INCREMENT, `upload_id` int(5) NOT NULL, `user_id` int(5) NOT NULL, `datetime` date NOT NULL, `location` varchar(200) NOT NULL, PRIMARY KEY (`history_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `history` -- INSERT INTO `history` (`history_id`, `upload_id`, `user_id`, `datetime`, `location`) VALUES (1, 1, 5, '2012-01-03', 'home'), (2, 2, 5, '2012-06-20', 'Office'), (3, 3, 5, '2012-03-06', 'office'), (4, 4, 5, '2012-06-05', 'Home'); -- -------------------------------------------------------- -- -- Table structure for table `uploads` -- CREATE TABLE IF NOT EXISTS `uploads` ( `upload_id` int(5) NOT NULL AUTO_INCREMENT, `user_id` int(5) NOT NULL, `file_path` varchar(200) NOT NULL, `picture_path` varchar(200) NOT NULL, PRIMARY KEY (`upload_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `uploads` -- INSERT INTO `uploads` (`upload_id`, `user_id`, `file_path`, `picture_path`) VALUES (1, 5, 'hope', 'star'), (2, 5, 'sky', 'mountain'), (3, 5, 'smoking', 'habbits'), (4, 5, 'sleeping', 'walking'); -- -------------------------------------------------------- -- -- Table structure for table `user` -- CREATE TABLE IF NOT EXISTS `user` ( `user_id` int(5) NOT NULL AUTO_INCREMENT, `name` varchar(75) NOT NULL, `address` varchar(200) NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `user` -- INSERT INTO `user` (`user_id`, `name`, `address`) VALUES (5, 'Asif', 'Bannu'), (6, 'Safeer', 'Bannu'), (7, 'Farhad', 'Peshawar'), (8, 'Rashed', 'Peshawar'), (9, 'Yasir', 'Quetta'), (10, 'Muhib', 'Islamabad'); i m executing the query
SELECT DISTINCT US.name, US.address, UP.file_path, UP.picture_path, HS.location
FROM user US
LEFT JOIN uploads UP ON US.user_id = UP.user_id
LEFT JOIN history HS ON US.user_id = HS.user_id,
uploads UD LEFT JOIN history HSI ON UD.upload_id = HSI.upload_id
WHERE US.user_id = 5
ORDER BY US.user_idbut i m getting multiple rows with same data. plz help me out in this situation. thanks a lot
-
hello to all .. i m beginner to mysql and php and i want to do the LEFT JOIN of three tables with data given below CREATE TABLE IF NOT EXISTS `history` ( `history_id` int(5) NOT NULL AUTO_INCREMENT, `upload_id` int(5) NOT NULL, `user_id` int(5) NOT NULL, `datetime` date NOT NULL, `location` varchar(200) NOT NULL, PRIMARY KEY (`history_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `history` -- INSERT INTO `history` (`history_id`, `upload_id`, `user_id`, `datetime`, `location`) VALUES (1, 1, 5, '2012-01-03', 'home'), (2, 2, 5, '2012-06-20', 'Office'), (3, 3, 5, '2012-03-06', 'office'), (4, 4, 5, '2012-06-05', 'Home'); -- -------------------------------------------------------- -- -- Table structure for table `uploads` -- CREATE TABLE IF NOT EXISTS `uploads` ( `upload_id` int(5) NOT NULL AUTO_INCREMENT, `user_id` int(5) NOT NULL, `file_path` varchar(200) NOT NULL, `picture_path` varchar(200) NOT NULL, PRIMARY KEY (`upload_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `uploads` -- INSERT INTO `uploads` (`upload_id`, `user_id`, `file_path`, `picture_path`) VALUES (1, 5, 'hope', 'star'), (2, 5, 'sky', 'mountain'), (3, 5, 'smoking', 'habbits'), (4, 5, 'sleeping', 'walking'); -- -------------------------------------------------------- -- -- Table structure for table `user` -- CREATE TABLE IF NOT EXISTS `user` ( `user_id` int(5) NOT NULL AUTO_INCREMENT, `name` varchar(75) NOT NULL, `address` varchar(200) NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `user` -- INSERT INTO `user` (`user_id`, `name`, `address`) VALUES (5, 'Asif', 'Bannu'), (6, 'Safeer', 'Bannu'), (7, 'Farhad', 'Peshawar'), (8, 'Rashed', 'Peshawar'), (9, 'Yasir', 'Quetta'), (10, 'Muhib', 'Islamabad'); i m executing the query
SELECT DISTINCT US.name, US.address, UP.file_path, UP.picture_path, HS.location
FROM user US
LEFT JOIN uploads UP ON US.user_id = UP.user_id
LEFT JOIN history HS ON US.user_id = HS.user_id,
uploads UD LEFT JOIN history HSI ON UD.upload_id = HSI.upload_id
WHERE US.user_id = 5
ORDER BY US.user_idbut i m getting multiple rows with same data. plz help me out in this situation. thanks a lot
U can create view from the tables and then create a join query using that view. I dont know exactly how ur answer should be, so correct me if i am wrong!!!!
karthi
-
hello to all .. i m beginner to mysql and php and i want to do the LEFT JOIN of three tables with data given below CREATE TABLE IF NOT EXISTS `history` ( `history_id` int(5) NOT NULL AUTO_INCREMENT, `upload_id` int(5) NOT NULL, `user_id` int(5) NOT NULL, `datetime` date NOT NULL, `location` varchar(200) NOT NULL, PRIMARY KEY (`history_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `history` -- INSERT INTO `history` (`history_id`, `upload_id`, `user_id`, `datetime`, `location`) VALUES (1, 1, 5, '2012-01-03', 'home'), (2, 2, 5, '2012-06-20', 'Office'), (3, 3, 5, '2012-03-06', 'office'), (4, 4, 5, '2012-06-05', 'Home'); -- -------------------------------------------------------- -- -- Table structure for table `uploads` -- CREATE TABLE IF NOT EXISTS `uploads` ( `upload_id` int(5) NOT NULL AUTO_INCREMENT, `user_id` int(5) NOT NULL, `file_path` varchar(200) NOT NULL, `picture_path` varchar(200) NOT NULL, PRIMARY KEY (`upload_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `uploads` -- INSERT INTO `uploads` (`upload_id`, `user_id`, `file_path`, `picture_path`) VALUES (1, 5, 'hope', 'star'), (2, 5, 'sky', 'mountain'), (3, 5, 'smoking', 'habbits'), (4, 5, 'sleeping', 'walking'); -- -------------------------------------------------------- -- -- Table structure for table `user` -- CREATE TABLE IF NOT EXISTS `user` ( `user_id` int(5) NOT NULL AUTO_INCREMENT, `name` varchar(75) NOT NULL, `address` varchar(200) NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `user` -- INSERT INTO `user` (`user_id`, `name`, `address`) VALUES (5, 'Asif', 'Bannu'), (6, 'Safeer', 'Bannu'), (7, 'Farhad', 'Peshawar'), (8, 'Rashed', 'Peshawar'), (9, 'Yasir', 'Quetta'), (10, 'Muhib', 'Islamabad'); i m executing the query
SELECT DISTINCT US.name, US.address, UP.file_path, UP.picture_path, HS.location
FROM user US
LEFT JOIN uploads UP ON US.user_id = UP.user_id
LEFT JOIN history HS ON US.user_id = HS.user_id,
uploads UD LEFT JOIN history HSI ON UD.upload_id = HSI.upload_id
WHERE US.user_id = 5
ORDER BY US.user_idbut i m getting multiple rows with same data. plz help me out in this situation. thanks a lot
-
U can create view from the tables and then create a join query using that view. I dont know exactly how ur answer should be, so correct me if i am wrong!!!!
karthi
-
hello to all .. i m beginner to mysql and php and i want to do the LEFT JOIN of three tables with data given below CREATE TABLE IF NOT EXISTS `history` ( `history_id` int(5) NOT NULL AUTO_INCREMENT, `upload_id` int(5) NOT NULL, `user_id` int(5) NOT NULL, `datetime` date NOT NULL, `location` varchar(200) NOT NULL, PRIMARY KEY (`history_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `history` -- INSERT INTO `history` (`history_id`, `upload_id`, `user_id`, `datetime`, `location`) VALUES (1, 1, 5, '2012-01-03', 'home'), (2, 2, 5, '2012-06-20', 'Office'), (3, 3, 5, '2012-03-06', 'office'), (4, 4, 5, '2012-06-05', 'Home'); -- -------------------------------------------------------- -- -- Table structure for table `uploads` -- CREATE TABLE IF NOT EXISTS `uploads` ( `upload_id` int(5) NOT NULL AUTO_INCREMENT, `user_id` int(5) NOT NULL, `file_path` varchar(200) NOT NULL, `picture_path` varchar(200) NOT NULL, PRIMARY KEY (`upload_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `uploads` -- INSERT INTO `uploads` (`upload_id`, `user_id`, `file_path`, `picture_path`) VALUES (1, 5, 'hope', 'star'), (2, 5, 'sky', 'mountain'), (3, 5, 'smoking', 'habbits'), (4, 5, 'sleeping', 'walking'); -- -------------------------------------------------------- -- -- Table structure for table `user` -- CREATE TABLE IF NOT EXISTS `user` ( `user_id` int(5) NOT NULL AUTO_INCREMENT, `name` varchar(75) NOT NULL, `address` varchar(200) NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `user` -- INSERT INTO `user` (`user_id`, `name`, `address`) VALUES (5, 'Asif', 'Bannu'), (6, 'Safeer', 'Bannu'), (7, 'Farhad', 'Peshawar'), (8, 'Rashed', 'Peshawar'), (9, 'Yasir', 'Quetta'), (10, 'Muhib', 'Islamabad'); i m executing the query
SELECT DISTINCT US.name, US.address, UP.file_path, UP.picture_path, HS.location
FROM user US
LEFT JOIN uploads UP ON US.user_id = UP.user_id
LEFT JOIN history HS ON US.user_id = HS.user_id,
uploads UD LEFT JOIN history HSI ON UD.upload_id = HSI.upload_id
WHERE US.user_id = 5
ORDER BY US.user_idbut i m getting multiple rows with same data. plz help me out in this situation. thanks a lot
SELECT DISTINCTROW US.name, US.address, UP.file_path, UP.picture_path, HS.location
FROM user US
LEFT JOIN uploads UP ON US.user_id = UP.user_id
LEFT JOIN history HS ON US.user_id = HS.user_id,
uploads UD LEFT JOIN history HSI ON UD.upload_id = HSI.upload_id
WHERE US.user_id = 5
ORDER BY US.user_idHint: Replace DISTINCT to DISTINCTROW
-
How your output should be...? Based on your output only we can write views which can use left, inner or right join.... If u can post how your answer looks like based on your datas i might try from my side
karthi
-
hello to all .. i m beginner to mysql and php and i want to do the LEFT JOIN of three tables with data given below CREATE TABLE IF NOT EXISTS `history` ( `history_id` int(5) NOT NULL AUTO_INCREMENT, `upload_id` int(5) NOT NULL, `user_id` int(5) NOT NULL, `datetime` date NOT NULL, `location` varchar(200) NOT NULL, PRIMARY KEY (`history_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `history` -- INSERT INTO `history` (`history_id`, `upload_id`, `user_id`, `datetime`, `location`) VALUES (1, 1, 5, '2012-01-03', 'home'), (2, 2, 5, '2012-06-20', 'Office'), (3, 3, 5, '2012-03-06', 'office'), (4, 4, 5, '2012-06-05', 'Home'); -- -------------------------------------------------------- -- -- Table structure for table `uploads` -- CREATE TABLE IF NOT EXISTS `uploads` ( `upload_id` int(5) NOT NULL AUTO_INCREMENT, `user_id` int(5) NOT NULL, `file_path` varchar(200) NOT NULL, `picture_path` varchar(200) NOT NULL, PRIMARY KEY (`upload_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `uploads` -- INSERT INTO `uploads` (`upload_id`, `user_id`, `file_path`, `picture_path`) VALUES (1, 5, 'hope', 'star'), (2, 5, 'sky', 'mountain'), (3, 5, 'smoking', 'habbits'), (4, 5, 'sleeping', 'walking'); -- -------------------------------------------------------- -- -- Table structure for table `user` -- CREATE TABLE IF NOT EXISTS `user` ( `user_id` int(5) NOT NULL AUTO_INCREMENT, `name` varchar(75) NOT NULL, `address` varchar(200) NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ; -- -- Dumping data for table `user` -- INSERT INTO `user` (`user_id`, `name`, `address`) VALUES (5, 'Asif', 'Bannu'), (6, 'Safeer', 'Bannu'), (7, 'Farhad', 'Peshawar'), (8, 'Rashed', 'Peshawar'), (9, 'Yasir', 'Quetta'), (10, 'Muhib', 'Islamabad'); i m executing the query
SELECT DISTINCT US.name, US.address, UP.file_path, UP.picture_path, HS.location
FROM user US
LEFT JOIN uploads UP ON US.user_id = UP.user_id
LEFT JOIN history HS ON US.user_id = HS.user_id,
uploads UD LEFT JOIN history HSI ON UD.upload_id = HSI.upload_id
WHERE US.user_id = 5
ORDER BY US.user_idbut i m getting multiple rows with same data. plz help me out in this situation. thanks a lot
Not sure why you have the second join to the history table. If each upload has a unique user and a unique history row, then user_id in the history table is useless information for this query. The second history join is giving you the redundant rows. Try and avoid using DISTINCT and its variations. It requires the server to do more work and therefore slow down your query.
SELECT
US.name,
US.address,
UP.file_path,
UP.picture_path,
HS.location
FROM
[user] US
LEFT JOIN
uploads UP
ON (US.user_id = UP.user_id)
LEFT JOIN
history HS
ON (US.upload_id = HS.upload_id)
WHERE
US.user_id = 5
ORDER BY
US.user_id