The field exists only in dotable_a, not in dotable_b

[viki1967]

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

[Dan Grossman]

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

[viki1967]

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

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

[r937]

And output not change, why?

because you did not ask for it to change

[viki1967]

because you did not ask for it to change

where mistake?

[r937]

where mistake?

in your outer query

[viki1967]

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...

[r937]

Nothing value for zn...

which is exactly what you asked for

[viki1967]

So wrong this time...
Your suggestion?

[r937]

Your suggestion?

ask for zn in your SELECT clause

[viki1967]

ask for zn in your SELECT clause

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.

[r937]

http://dev.mysql.com/doc/refman/4.1/...nsitivity.html

[viki1967]

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...

[r937]

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

[r937]

select zn=2 as zn, ...

column 'zn' is always null...

no it isn't, not always

[viki1967]

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

[r937]

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?

[viki1967]

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?

This output:

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

[r937]

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

[viki1967]

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 , NULL as wtf, line, 1 as dove from dotable_b
union all
select zn, zn=2 as wtf, line_mt, 2 as dove from dotable_a
) as tab
group by r
having q > 3
order by q desc

Output:

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

[r937]

you're not listening again

if you keep coming to this forum to get problems solved for you, you will have to follow along when we try to teach you sql

if you don't want to learn sql, that's okay too, but don't keep asking us to solve your problems for you

i'm finished with this thread

good luck

[viki1967]

you're not listening again

if you keep coming to this forum to get problems solved for you, you will have to follow along when we try to teach you sql

if you don't want to learn sql, that's okay too, but don't keep asking us to solve your problems for you

i'm finished with this thread

good luck

If I write in the forum is because they want to learn.
But they may not understand the suggestions.
This is my big work, I'am sorry but I need only help in this forum.

This is the output required:

idDTES_ZONA	wtf
DM2N		0
DS1M		0
DM6M		0
DM6M		0
DI5M		0
DI5M		0
DI5M		0

[r937]

This is my big work

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

[viki1967]

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

I don't explain so good.
You know my english is not good.
I don't work in this things, I need your help.

[r937]

your english is fine

your sql skills are terrible, and you do not seem to be able to learn

i am not helping you any more, sorry