The field exists only in dotable_a, not in dotable_b

Databases
1

Hi.

This is my query in db mysql:

select substring_index(line,'-',1) as r,
sum(if(dove=1,1,0)) as line_a,
sum(if(dove=2,1,0)) as line_b,
count(substring_index(line,'-',1)) as q from (
select line, 1 as dove from dotable_b
union all
select line_mt, 2 as dove from dotable_a
) as tab
group by r
having q > 3
order by q desc

and this is the output:

r		line_a	line_b	q
DO6022115 	154	1	155
DO6038875 	151	1	152
DO6038860 	147	2	149
DO6082030 	141	0	141
DO6038840 	122	4	126
DO6073020 	106	0	106
DO6048371 	99	1	100

For each r is associated in the dotable_a to a field called Zn, I need now this output:

zn	r		line_a	line_b	q
???	DO6022115 	154	1	155
???	DO6038875 	151	1	152
???	DO6038860 	147	2	149
???	DO6082030 	141	0	141
???	DO6038840 	122	4	126
???	DO6073020 	106	0	106
???	DO6048371 	99	1	100

But the field zn exists only in dotable_a, not in dotable_b…

Can you help me?
thanks

2

In dotable_b, SELECT NULL AS zn, in your union.

3

Many thanks x your answer, the new query:

select substring_index(line,'-',1) as r,
sum(if(dove=1,1,0)) as line_a,
sum(if(dove=2,1,0)) as line_b,
count(substring_index(line,'-',1)) as q from (
select NULL AS zn, ine, 1 as dove from dotable_b
union all
select zn, line_mt, 2 as dove from dotable_a
) as tab
group by r
having q > 3
order by q desc

And output not change, why?:

r		line_a	line_b	q
DO6022115 	154	1	155
DO6038875 	151	1	152
DO6038860 	147	2	149
DO6082030 	141	0	141
DO6038840 	122	4	126
DO6073020 	106	0	106
DO6048371 	99	1	100
4

because you did not ask for it to change

5

where mistake?

6

in your outer query

7

Ok:

select NULL AS zn, substring_index(line,'-',1) as r,
sum(if(dove=1,1,0)) as line_a,
sum(if(dove=2,1,0)) as line_b,
count(substring_index(line,'-',1)) as q from (
select NULL AS zn, line, 1 as dove from dotable_b
union all
select zn, line_mt, 2 as dove from dotable_a
) as tab
group by r
having q > 3
order by q desc

Output:


zn	r		line_a	line_b	q
	DO6022115 	154	1	155
	DO6038875 	151	1	152
	DO6038860 	147	2	149
	DO6082030 	141	0	141
	DO6038840 	122	4	126
	DO6073020 	106	0	106
	DO6048371 	99	1	100

Nothing value for zn… :frowning:

8

which is exactly what you asked for

9

So wrong this time… :frowning:
Your suggestion?

10

no it isn’t, not always

11

ask for zn in your SELECT clause

12

OK:

select ZN AS zn, substring_index(line,'-',1) as r,
sum(if(dove=1,1,0)) as line_a,
sum(if(dove=2,1,0)) as line_b,
count(substring_index(line,'-',1)) as q from (
select NULL AS zn, line, 1 as dove from dotable_b
union all
select zn, line_mt, 2 as dove from dotable_a
) as tab
group by r
having q > 3
order by q desc

[MySQL][ODBC 5.1 Driver][mysqld-5.0.45-community-nt]Unknown column ‘zn’ in ‘field list’

The field zn exists only in dotable_a, not in dotable_b.

13

http://dev.mysql.com/doc/refman/4.1/en/identifier-case-sensitivity.html

14 
select zn=2 as zn, substring_index(line,'-',1) as r,
sum(if(dove=1,1,0)) as line_a,
sum(if(dove=2,1,0)) as line_b,
count(substring_index(line,'-',1)) as q from (
select NULL AS zn, line, 1 as dove from dotable_b
union all
select zn, line_mt, 2 as dove from dotable_a
) as tab
group by r
having q > 3
order by q desc

column ‘zn’ is always null…

:injured:

15

i have a feeling you are not showing us the real query

16

dotable_a


ID	ES	DATE		ZN	LINE_MT
12881	DO60	2010-02-26	DO6P	DO6015055-TERZIGNO
12882	DO60	2010-02-26	DS1M	DS1003405-DOLIANOVA
12883	DO60	2010-02-26	DI5N	DI5020317-CAVET
12884	DO60	2010-02-26	DI5N	DI5020317-CAVET
12885	DO60	2010-02-26	DI5N	DI5020317-CAVET
12886	DO60	2010-02-26	DI5N	DI5020317-CAVET

dotable_b


ID	DATE		ES	DATA		LINE
31608	2010-02-26	DO60	2010-02-26	DI5020317
31609	2010-02-26	DO60	2010-02-26	DO6007125

I need this output:


r		line_a	line_b	q	zn
DI5020317 	4	1	5	DI5N
...
...

For this query the field ‘zn’ is null…:


select zn=2 as zn, substring_index(line,'-',1) as r,
sum(if(dove=1,1,0)) as line_a,
sum(if(dove=2,1,0)) as line_b,
count(substring_index(line,'-',1)) as q from (
select NULL AS zn, line, 1 as dove from dotable_b
union all
select zn, line_mt, 2 as dove from dotable_a
) as tab
group by r
having q > 3
order by q desc
17

that is correct, because there is only one group that satisfies the HAVING clause, and for that group, zn is NULL, so of course zn=2 will also be NULL

what is “zn=2” supposed to do for you?

18

This output:


r		line_a	line_b	q	zn
DI5020317 	4	1	5	DI5N
...
...
19

i don’t think you understand what zn=2 will actually do

run this query –

select zn, zn=2 as wtf from dotable_a

and please show the results

20

i’m sorry, but i don;t think you’re capable of doing it, as you do not seem to understand sql very well

you should consider hiring someone to do it for you