Professional Web Applications Themes

Tried distinct but does not work ... - MySQL

Tried distinct however does not work to eliminate repeat output of department name. Only want department name to print once with all members then next department name and members etc. select (cccb_name) as 'cccb', CONCAT(member_fname,' ',member_lname) as 'member ' from member, cccb, member_cccb_lnk where cccb.cccb_id = member_cccb_lnk.cccb_id Advisory department Tom Baster Advisory department Jim Hooker Advisory department Frank Mustard Advisory department Julie Trees Advisory department Jeff Black Advisory department Louis Chad Affirmative Action Department Jeff Black Approval Department Julie Trees Development department Jeff Black Ethics department Jim Hooker Finance department Tom Baster Finance department Tony Tomas Finance department Jim Hooker ...

  1. #1

    Default Tried distinct but does not work ...

    Tried distinct however does not work to eliminate repeat output of
    department name. Only want department name to print once with all
    members then next department name and members etc.

    select (cccb_name) as 'cccb', CONCAT(member_fname,' ',member_lname) as
    'member ' from member, cccb, member_cccb_lnk where cccb.cccb_id =
    member_cccb_lnk.cccb_id


    Advisory department Tom Baster
    Advisory department Jim Hooker
    Advisory department Frank Mustard
    Advisory department Julie Trees
    Advisory department Jeff Black
    Advisory department Louis Chad
    Affirmative Action Department Jeff Black
    Approval Department Julie Trees
    Development department Jeff Black
    Ethics department Jim Hooker
    Finance department Tom Baster
    Finance department Tony Tomas
    Finance department Jim Hooker
    Finance department Denny Rest
    user Guest

  2. #2

    Default Re: Tried distinct but does not work ...

    On May 7, 8:34 pm, user <org> wrote: 


    This is a problem of data display, as opposed to data retrieval and,
    as such, would usually be handled in PHP for instance. However, one
    solution was very recently described over at the mysql newbie forum:
    http://forums.mysql.com/read.php?10,151726,151939#msg-151939

    select if(prev=cccb_name,'',prev := cccb_name) as 'cccb',
    CONCAT(member_fname,' ',member_lname) as
    'member ' from member, cccb, member_cccb_lnk where cccb.cccb_id =
    member_cccb_lnk.cccb_id

    strawberry Guest

  3. #3

    Default Re: Tried distinct but does not work ...

    I will read your url but I used your sql and it presented a cartesian
    product.


    strawberry wrote: 
    >
    >
    >
    > This is a problem of data display, as opposed to data retrieval and,
    > as such, would usually be handled in PHP for instance. However, one
    > solution was very recently described over at the mysql newbie forum:
    > http://forums.mysql.com/read.php?10,151726,151939#msg-151939
    >
    > select if(prev=cccb_name,'',prev := cccb_name) as 'cccb',
    > CONCAT(member_fname,' ',member_lname) as
    > 'member ' from member, cccb, member_cccb_lnk where cccb.cccb_id =
    > member_cccb_lnk.cccb_id
    >[/ref]
    user Guest

  4. #4

    Default Re: Tried distinct but does not work ...

    Ok, the script I provided was incorrect. Following is correct.

    select (cccb_name) as 'cccb', CONCAT(member_fname,' ',member_lname) as
    'member ' from member, cccb, member_cccb_lnk where cccb.cccb_id =
    member_cccb_lnk.cccb_id
    and member.member_no = member_cccb_lnk.member_no order by cccb asc

    However, modified above with your mod and I think it is correct but it
    provides the same out. The cccb is repeated as before.


    select if(prev=cccb_name,'',prev := cccb_name) as 'cccb',
    CONCAT(member_fname,' ',member_lname) as 'member '
    from member, cccb, member_cccb_lnk where cccb.cccb_id =
    member_cccb_lnk.cccb_id
    and member.member_no = member_cccb_lnk.member_no order by cccb asc;


    select (cccb_name) as 'cccb', CONCAT(member_fname,' ',member_lname) as
    'member '
    from member, cccb, member_cccb_lnk where cccb.cccb_id =
    member_cccb_lnk.cccb_id
    and member.member_no = member_cccb_lnk.member_no order by cccb asc;





    strawberry wrote:
     
    >
    >
    >
    > This is a problem of data display, as opposed to data retrieval and,
    > as such, would usually be handled in PHP for instance. However, one
    > solution was very recently described over at the mysql newbie forum:
    > http://forums.mysql.com/read.php?10,151726,151939#msg-151939
    >
    > select if(prev=cccb_name,'',prev := cccb_name) as 'cccb',
    > CONCAT(member_fname,' ',member_lname) as
    > 'member ' from member, cccb, member_cccb_lnk where cccb.cccb_id =
    > member_cccb_lnk.cccb_id
    >[/ref]
    user Guest

  5. #5

    Default Re: Tried distinct but does not work ...

    user wrote: 
    >>
    >>
    >>
    >> This is a problem of data display, as opposed to data retrieval and,
    >> as such, would usually be handled in PHP for instance. However, one
    >> solution was very recently described over at the mysql newbie forum:
    >> http://forums.mysql.com/read.php?10,151726,151939#msg-151939
    >>
    >> select if(prev=cccb_name,'',prev := cccb_name) as 'cccb',
    >> CONCAT(member_fname,' ',member_lname) as
    >> 'member ' from member, cccb, member_cccb_lnk where cccb.cccb_id =
    >> member_cccb_lnk.cccb_id[/ref][/ref]

    It doesn't work because strawberry missed out a very important part of the
    total query when he posted here, although the clue was in the link he posted
    if you looked carefully.
    Try:
    SET prev = '';
    select if(prev=cccb_name,'',prev := cccb_name) as 'cccb',
    CONCAT(member_fname,' ',member_lname) as 'member '
    from member, cccb, member_cccb_lnk where cccb.cccb_id =
    member_cccb_lnk.cccb_id
    and member.member_no = member_cccb_lnk.member_no order by cccb asc;


    Paul Guest

  6. #6

    Default Re: Tried distinct but does not work ...

    Sorry Paul, but the script still gives me redundant 'cccb' names. I did
    look at the url and had added the

    SET prev = ''; and
    select if(prev=cccb_name,'',prev := cccb_name)

    which I think is what your script adds. Anyway, same redundant result.

    Thanks anyway


    Paul Lautman wrote: [/ref]
    >
    >
    > It doesn't work because strawberry missed out a very important part of the
    > total query when he posted here, although the clue was in the link he posted
    > if you looked carefully.
    > Try:
    > SET prev = '';
    > select if(prev=cccb_name,'',prev := cccb_name) as 'cccb',
    > CONCAT(member_fname,' ',member_lname) as 'member '
    > from member, cccb, member_cccb_lnk where cccb.cccb_id =
    > member_cccb_lnk.cccb_id
    > and member.member_no = member_cccb_lnk.member_no order by cccb asc;
    >
    >[/ref]
    user Guest

  7. #7

    Default Re: Tried distinct but does not work ...

    On 11 May, 04:36, user <org> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > - Show quoted text -[/ref]

    Itried it on one of my databses and it worked fine.

    Can you post the schemas and some sample data (from a phpmyadmin
    export so I can load them straight in) and I'll check to see what is
    wrong.

    Also, please do not top post.

    Captain Guest

Similar Threads

  1. SQL Distinct
    By DDhillon in forum Coldfusion Database Access
    Replies: 8
    Last Post: July 27th, 03:44 AM
  2. need help on DISTINCT
    By Krechting in forum ASP Database
    Replies: 1
    Last Post: January 12th, 01:07 AM
  3. Replies: 3
    Last Post: April 18th, 12:52 PM
  4. Replies: 0
    Last Post: April 15th, 01:22 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139