MySQL Query Show additional query information-CodePudding

I have two tables in a database:

CREATE TABLE PAR (
    A           varchar(10) not null,
    T               varchar(25) not null,
    U               varchar(25) not null,
    V               varchar(30) not null,
    primary key (A),  
);

INSERT INTO PAR (A, T, U, V)
VALUES
('A5065','Choco','Belt','Texas'),
('B6060','Koro','Tan','Montana'),
('C2036','Beta','Blast','Washington'),
('D2000','Tao','Bet','Kentucky');



CREATE TABLE AT (
    A       varchar(10) not null,
    B               char(1),
    C       integer,
    D       varchar(20),
    primary key (A),
    foreign key (A) references PAR(A)
);

INSERT INTO AT (A, B, C, D)
VALUES
('A5065','Z','1660','Apple'),
('B6060','D','1598','Banana');

I want to write a query that finds the highest and lowest C values in table AT

The desired output would look something like this:

Choco Belt 1660
Koro Tan 1598

I wrote a query but unfortunately it only returns: (does not have a second column for 1660 and 1598 values)

Choco Belt
Koro Tan

My query is:

SELECT CONCAT(T,' ', U) AS 'Names' from PAR
WHERE A IN ( SELECT A from AT where (C IN (SELECT MIN(C) from AT) || C IN (SELECT MAX(C) from AT)));

CodePudding user response：

You need to specify a second column in the select-list, and that means you must have a table to reference in the outer query. So use JOIN instead of IN(subquery):

SELECT CONCAT(T,' ', U) AS 'Names', AT.C
FROM PAR
JOIN AT USING (A)
WHERE AT.C IN (SELECT MIN(C) from AT) OR AT.C IN (SELECT MAX(C) from AT)

Output:

 ------------ ------ 
| Names      | C    |
 ------------ ------ 
| Choco Belt | 1660 |
| Koro Tan   | 1598 |
 ------------ ------

Tip: Use OR instead of || for logical OR, because || can mean string concatenation depending on your SQL mode.