SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    The field exists only in dotable_a, not in dotable_b

    Hi.

    This is my query in db mysql:

    Code:
    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:

    Code:
    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:

    Code:
    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. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    In dotable_b, SELECT NULL AS zn, in your union.

  3. #3
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    In dotable_b, SELECT NULL AS zn, in your union.
    Many thanks x your answer, the new query:

    Code:
    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?:

    Code:
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by viki1967 View Post
    And output not change, why?
    because you did not ask for it to change
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    because you did not ask for it to change
    where mistake?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by viki1967 View Post
    where mistake?
    in your outer query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok:

    Code:
    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:

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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by viki1967 View Post
    Nothing value for zn...
    which is exactly what you asked for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So wrong this time...
    Your suggestion?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by viki1967 View Post
    Your suggestion?
    ask for zn in your SELECT clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    ask for zn in your SELECT clause
    OK:

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

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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...


  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i have a feeling you are not showing us the real query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by viki1967 View Post
    Code:
    select zn=2 as zn, ...
    column 'zn' is always null...
    no it isn't, not always
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    dotable_a
    Code:
    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
    Code:
    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:
    Code:
    r		line_a	line_b	q	zn
    DI5020317 	4	1	5	DI5N
    ...
    ...
    For this query the field 'zn' is null...:

    Code:
    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. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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:
    Code:
    r		line_a	line_b	q	zn
    DI5020317 	4	1	5	DI5N
    ...
    ...

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't think you understand what zn=2 will actually do

    run this query --
    Code:
    select zn, zn=2 as wtf from dotable_a
    and please show the results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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:
    Code:
    r		line_a	line_b	q	zn
    DI5020317 	4	1	5	NULL
    ...
    ...

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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:

    Code:
    idDTES_ZONA	wtf
    DM2N		0
    DS1M		0
    DM6M		0
    DM6M		0
    DI5M		0
    DI5M		0
    DI5M		0

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by viki1967 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    Non-Member
    Join Date
    Aug 2007
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •