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

Union all with two rows where first row is empty – Mysql

there is a table where some data is populated and have to get single data for every key value

Fk| field1 | field2
====================
 1|r       |lo
2 |abcd    |mem
3 |efghi   |mem
4 |abcd    |mem
5 |efghi   |mem

Trying to get single values for every field one which i require

I tried the query:

(Select * from table where field1='abd' limit 1)
union all
(Select * from table where field1='abcd' limit 1)

I know field1 don’t contain ‘abd’ is not present but it should give me one result right? but it is not giving me.
Expected results

Fk| field1 | field2
====================
2 |abcd    |mem

If not can you please suggest any better query which can solve my problem.

Edit:- can anyone please share any other query which can give me same result.thanks for helping everyone

Tags:

2

Answers


  1. 0

    In MySQL 8 you get the wanted results, but your query must be rewritten

    CREATE TABLE tex
    (`Fk` int, `field1` varchar(5), `field2` varchar(3))
;
    
INSERT INTO tex
    (`Fk`, `field1`, `field2`)
VALUES
    (1, 'r', 'lo'),
    (2, 'abcd', 'mem'),
    (3, 'efghi', 'mem'),
    (4, 'abcd', 'mem'),
    (5, 'efghi', 'mem')
;


    Records: 5  Duplicates: 0  Warnings: 0

    (Select * from tex where field1='abd' limit 1 )
union all
(Select * from tex where field1='abcd' limit 1)
    Fk field1 field2
    2 abcd mem

    fiddle

    Login or Signup to reply.
  2. 0

    When you use LIMIT in a combinable subquery for UNION ALL all of them except for the last one need to be enclosed in parenthesis.

    In this case you want each subquery to limits its results separately, so all of them need to be enclosed in parenthesis.

    For example:

    (Select * from t where field1 = 'abd' limit 1) 
union all
(Select * from t where field1 = 'abcd' limit 1)

    Result:

    Fk  field1  field2
--  ------  ------
2   abcd    mem

    See running example at db<>fiddle.

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

Back To Top
Search