Question 169005 in Mysql Question posted in
The official MySQL documentation can be found here.

SELECT rows starting with (multiple values) FROM MySQL?

I an using c# and mysql server and i have two tables like this :

table_1

+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| AUT001 | William Norton       | UK        | Cambridge      |
| AUT002 | William Maugham      | Canada    | Toronto        |
| AUT003 | William Anthony      | UK        | Leeds          |
| AUT004 | S.B.Swaminathan      | India     | Bangalore      |
| AUT005 | Thomas Morgan        | Germany   | Arnsberg       |
| AUT006 | Thomas Merton        | USA       | New York       |
| AUT007 | Piers Gibson         | UK        | London         |
| AUT008 | Nikolai Dewey        | USA       | Atlanta        |
| AUT009 | Marquis de Ellis     | Brazil    | Rio De Janerio |
| AUT010 | Joseph Milton        | USA       | Houston        |
| AUT011 | John Betjeman Hunter | Australia | Sydney         |
| AUT012 | Evan Hayek           | Canada    | Vancouver      |
| AUT013 | E. Howard            | Australia | Adelaide       |
| AUT014 | C. J. Wilde          | UK        | London         |
| AUT015 | Butler Andre         | USA       | Florida        |
+--------+----------------------+-----------+----------------+

table_2

+--------+-----+
| ID | PreFix  |
+--------+-----+
| 1  | Wi      |
| 2  | Th      |
| 3  | E       |
+--------+-----+

I want to select any author that his name starts with any prefix from the table_2, the results should be like :

+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| AUT001 | William Norton       | UK        | Cambridge      |
| AUT002 | William Maugham      | Canada    | Toronto        |
| AUT003 | William Anthony      | UK        | Leeds          |
| AUT005 | Thomas Morgan        | Germany   | Arnsberg       |
| AUT006 | Thomas Merton        | USA       | New York       |
| AUT012 | Evan Hayek           | Canada    | Vancouver      |
| AUT013 | E. Howard            | Australia | Adelaide       |
+--------+----------------------+-----------+----------------+

i tried this solution :

SELECT * FROM tabel_1 where aut_name like concat((SELECT PreFix FROM table_2) , ‘%’);

but i get this error message :

Error Code: 1242. Subquery returns more than 1 row

Tags:

2

Answers


  1. 0

    Try using JOIN instead of subquerying:

    SELECT t1.aut_id, t1.aut_name, t1.country, t1.home_city
FROM table_1 AS t1
JOIN table_2 AS t2 ON t1.aut_name LIKE CONCAT(t2.PreFix, '%');

    The SQL CONCAT function is designed to concatenate multiple strings into a single string, taking individual string values as arguments and combining them together. CONCAT expects individual scalar values as arguments, not a result-set of multiple rows which is what your subquery could potentially return.

    To solve a problem like the one you laid out here, you need to leverage the power of JOIN, a statement allowing you to combine rows from different tables based on a related column between them, which you will usually call a foreign key. This way, you ensure that each row from table_1 is compared with all the rows from table_2 that have matching PreFix values, a condition you can specify after the ON clause.

    As a side note, it’d be very much appreciated if you provided the SQL’s DDL and DML statements to build the minimal reproducible example needed to test your case and play around. I took the liberty of creating these statements so I’ll be posting them here to help any people to come by later to understand, play around with, test and improve our work:

    CREATE TABLE table_1 (
  aut_id VARCHAR(10),
  aut_name VARCHAR(50),
  country VARCHAR(50),
  home_city VARCHAR(50)
);

CREATE TABLE table_2 (
  ID INT,
  PreFix VARCHAR(2)
);

INSERT INTO table_1 (aut_id, aut_name, country, home_city)
VALUES
  ('AUT001', 'William Norton', 'UK', 'Cambridge'),
  ('AUT002', 'William Maugham', 'Canada', 'Toronto'),
  ('AUT003', 'William Anthony', 'UK', 'Leeds'),
  ('AUT004', 'S.B.Swaminathan', 'India', 'Bangalore'),
  ('AUT005', 'Thomas Morgan', 'Germany', 'Arnsberg'),
  ('AUT006', 'Thomas Merton', 'USA', 'New York'),
  ('AUT007', 'Piers Gibson', 'UK', 'London'),
  ('AUT008', 'Nikolai Dewey', 'USA', 'Atlanta'),
  ('AUT009', 'Marquis de Ellis', 'Brazil', 'Rio De Janerio'),
  ('AUT010', 'Joseph Milton', 'USA', 'Houston'),
  ('AUT011', 'John Betjeman Hunter', 'Australia', 'Sydney'),
  ('AUT012', 'Evan Hayek', 'Canada', 'Vancouver'),
  ('AUT013', 'E. Howard', 'Australia', 'Adelaide'),
  ('AUT014', 'C. J. Wilde', 'UK', 'London'),
  ('AUT015', 'Butler Andre', 'USA', 'Florida');

INSERT INTO table_2 (ID, PreFix)
VALUES
  (1, 'Wi'),
  (2, 'Th'),
  (3, 'E');
    Login or Signup to reply.
  2. 0

    The error message you received "Subquery returns more than 1 row" indicates that the subquery (SELECT PreFix FROM table_2) is returning multiple rows, and therefore cannot be used as a parameter in the CONCAT() function.

    To fix this, you can use the IN operator instead of LIKE to compare the prefixes from table_2 with the beginning of aut_name from table_1. Here is the corrected code (SQL):

    SELECT *
FROM table_1
WHERE aut_name LIKE CONCAT((SELECT PreFix FROM table_2 WHERE ID = 1), '%')
   OR aut_name LIKE CONCAT((SELECT PreFix FROM table_2 WHERE ID = 2), '%')
   OR aut_name LIKE CONCAT((SELECT PreFix FROM table_2 WHERE ID = 3), '%');

    Alternatively, you can use a JOIN statement to achieve the same result. Here is the code using JOIN:

    SELECT t1.*
FROM table_1 t1
JOIN table_2 t2 ON t1.aut_name LIKE CONCAT(t2.PreFix, '%');

    This code selects all the rows from table_1 where aut_name starts with any of the prefixes from table_2.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search