CODEWITHSUNDEEP
mysql
user1876234
user1876234

Reputation: 857

Mysql subquery not displaying rows

This is my query:

SELECT a.vardas, a.pavarde FROM `y6fdt_igym_abonementai` AS a 
INNER JOIN 
( 
    SELECT max(galiojaiki) gal, id, abonementas FROm y6fdt_igym_sutartys
) c
on c.abonementas = a.id

Why I see only a.vardas and a.pavarde columns as result. But not id, abonementas from subquery ?

Upvotes: 0

Views: 40

Answers (2)

Akim
Akim

Reputation: 136

change 

SELECT a.vardas, a.pavarde FROM `y6fdt_igym_abonementai` AS a 
 ...
SELECT max(galiojaiki) gal, id, abonementas FROm y6fdt_igym_sutartys

to

SELECT a.vardas, a.pavarde FROM `y6fdt_igym_abonementai` a 
...

SELECT max(c.galiojaiki) c.gal, c.id, c.abonementas FROm y6fdt_igym_sutartys c

Upvotes: 0

Barmar
Barmar

Reputation: 781984

If you want to see those columns, you have to add them to the main SELECT clause.

SELECT a.vardas, a.pavarde, c.gal, c.id, c.abonementas
FROM `y6fdt_igym_abonementai` AS a 
INNER JOIN 
( 
    SELECT max(galiojaiki) gal, id, abonementas FROm y6fdt_igym_sutartys
) c
on c.abonementas = a.id

Note that the id and abonementas columns in the subquery will not be from row with max(galiojaiki). It will just take them from some random row in the table. If you want those specific values, you need to use:

SELECT a.vardas, a.pavarde, c.gal, c.id, c.abonementas
FROM `y6fdt_igym_abonementai` AS a 
FROM y6fdt_igym_sutartys AS c ON c.abonementas = a.id
INNER JOIN 
( 
    SELECT max(galiojaiki) AS maxgal 
    FROm y6fdt_igym_sutartys
) d on c.galiojaiki = d.maxgal

Upvotes: 5

Related Questions