CODEWITHSUNDEEP
mysql
lalexa
lalexa

Reputation: 453

mysql select from one to many relation

I have two tables - Users and Products. User may have a few products. I need to select all products and show which user they belong.

How to solve it using mysql joins.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(16) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8

 CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8

I've tried like this:

SELECT users.login FROM users LEFT JOIN products ON users.id=products.user_id;

But in this case it shows me only user logins, but i need to get product name, price and description as well.

Upvotes: 0

Views: 24

Answers (1)

juergen d
juergen d

Reputation: 204746

SELECT p.name, p.price, p.description, group_concat(u.login) as users
FROM products p
LEFT JOIN users u ON u.id = p.user_id
GROUP BY p.name, p.price, p.description

Upvotes: 1

Related Questions