MySQL Forums
Forum List  »  Newbie

JOIN, WHERE and calculated column (based on JOIN and WHERE)
Posted by: Tomasz gagola
Date: June 29, 2018 05:14AM

Looking for the help. I try to run a code in this order JOIN, WHERE and and GRP number (on the results of my query).

I have two tables test and text:

create table test (
code varchar(10) primary key,
num int(10) not null,
name varchar(10) not null,
surname varchar(10) not null);

insert into test values (1,09,'Tom', 'Smith');
insert into test values (2,09,'Adam','Blake');
insert into test values (3,15,'John','Smith');
insert into test values (4,15,'Adam','XYZ');
insert into test values (5,43,'John','Abc');
insert into test values (6,99,'Adam','Abc');
insert into test values (7,99,'John','Abc');
insert into test values (8,15,'Adam','XYZ');
insert into test values (9,43,'John','Abc');
insert into test values (10,99,'Adam','Abc');
insert into test values (11,99,'John','Abc');
insert into test values (12,1,'A','A');
insert into test values (13,1,'J','A');

and table text:

create table text (
code varchar(10) primary key,
codeFK varchar(10) not null,
state varchar(10));

insert into text values (1,2,'O');
insert into text values (2,2,'O');
insert into text values (3,2,'O');
insert into text values (4,2,'C');
insert into text values (5,3,'O');
insert into text values (6,3,'O');
insert into text values (7,3,'O');
insert into text values (8,2,'O');
insert into text values (9,2,'C');
insert into text values (10,2,'O');
insert into text values (11,2,'C');
insert into text values (12,1,'C');
insert into text values (13,12,'C');
insert into text values (14,13,'C');
I join them and try to add coulm group as per below code:

JOIN - it works

SELECT num FROM test
LEFT JOIN text ON test.code = text.codeFK
WHERE state = 'O'

ORDER BY test.num DESC;
GRP calculation on its on

select t.*,
(select count(distinct t2.num)
from test t2
where t2.num <= t.num
) as grp
from test t ORDER BY GRP;
But my question is how to get GRP column with above join query? So, the grp column will be calculated only on the results based on WHERE statement and not the whole table test.

I tried below but it gives me an error:

SELECT num FROM test
LEFT JOIN text ON test.code = text.codeFK
WHERE state = 'O'

AND test.num IN (select t.num,
(select count(distinct t2.num)
from test t2
where t2.num <= t.num
) as grp from test t)

ORDER BY test.num DESC;

Options: ReplyQuote


Subject
Written By
Posted
JOIN, WHERE and calculated column (based on JOIN and WHERE)
June 29, 2018 05:14AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.