MySQL Using a subquery in table join

Question

I am trying get the name of a user that created a record and another user that updated the record from another table using table Join, the record returns 2 rows, which isn't what I want, I am trying to figure out how to write a sub-query to get the name of the user that updated the record and assign an alias and have it return a single row.

The 2 tables below and my query

DROP TABLE IF EXISTS `reseller`;
CREATE TABLE IF NOT EXISTS `reseller` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `phone_number` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `user_type` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `created` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `lastlogin` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('active','suspended') COLLATE utf8_unicode_ci NOT NULL,
  `confirmcode` int(9) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `reseller` (`id`, `name`, `email`, `password`, `phone_number`, `user_type`, `created`, `lastlogin`, `status`, `confirmcode`) VALUES
(1, 'Andrie Rieu', 'reseller@ghmailc.com', 'jrxtroU0zq1XzTDBdjFpMEdCZFpmQzdPbzZ4aTlOTDhWZz098cPr55nj4NXecFhU', '35893828231', 'sales rep', '1596282651', '1619609401', 'active', 0),
(2, 'Joice Brenda', 'btcethxrpxlm@gmail.com', 'wmTvO1r7Bl18qXbrdjFpMEdCZFpmQzdPbzZ4aTlOTDhWZz09Wz3dZ6wIR9onEhPd', '+325893929491', 'book keeper', '1619684254', '1619713660', 'active', 0);
COMMIT;

DROP TABLE IF EXISTS `supplier_record`;
CREATE TABLE IF NOT EXISTS `supplier_record` (
  `supplier_id` int(5) NOT NULL AUTO_INCREMENT,
  `supplier_name` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `company_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `supplier_taxid` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `supplier_address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `supplier_phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `supplier_email` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `supplier_bankname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `supplier_bankaccountno` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `supplier_bankaccountname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `supplier_bankroutingno` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `supplier_bankswiftcode` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `supplier_other` text COLLATE utf8_unicode_ci NOT NULL,
  `created` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `updated` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `created_by` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `updated_by` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`supplier_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='All supplier records';


INSERT INTO `supplier_record` (`supplier_id`, `supplier_name`, `company_name`, `supplier_taxid`, `supplier_address`, `supplier_phone`, `supplier_email`, `supplier_bankname`, `supplier_bankaccountno`, `supplier_bankaccountname`, `supplier_bankroutingno`, `supplier_bankswiftcode`, `supplier_other`, `created`, `updated`, `created_by`, `updated_by`) VALUES
(1, 'Wineop', 'Wineop Freight Services', 'TKPLYO213', '91 Juiner Ave', '0903489924913', 'hienow539@gmail.com', 'Bank of Netherlands', '0042942044', 'Komentis Wineop', 'KLD44-4FK32X', '3LLDRKE3-EOFKD', 'Eristomse', '1619757985', '', '2', '1');
COMMIT;

My query that returns 2 rows

SELECT reseller.name,supplier_record.* FROM supplier_record,reseller WHERE (supplier_record.created_by = reseller.id) OR (supplier_record.updated_by = reseller.id) ORDER BY supplier_record.supplier_id DESC

`JOIN`. `JOIN`. `JOIN`. *Never* use commas in the `FROM` clause. — Gordon Linoff, May 01 '21 at 17:12
commas are old, confusing and outdated. see this (https://stackoverflow.com/a/20467845/8575615) — Praveen, May 01 '21 at 17:25
@praveen okay I tried your query and it worked, didn't know multiple join could work. — PeterT, May 01 '21 at 17:28

score 1 · Answer 1 · answered May 01 '21 at 17:22

pls try and see if this is the result you expect

query:

SELECT 
  res_cr.name created_name, res_upd.name update_name, supplier_record.* 
FROM 
  supplier_record 
  left join reseller res_cr on supplier_record.created_by = res_cr.id
  left join reseller res_upd on supplier_record.updated_by = res_upd.id
ORDER BY 
  supplier_record.supplier_id DESC

fiddle url: (https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ca3b7ed03b7090eef4673aa612117a78)

MySQL Using a subquery in table join

1 Answers1