Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Special Summary Fields in Views

davesbdavesb Member Posts: 2
I'm trying to find a neat and elegant way of including a special kind of summary field in a SQL statement. I have 2 tables in a one-to-many relationship and the second table contains a field with text in (first table is contact information, 2nd contains areas that the person is prepared to work).
I want to find a neat way of pulling up contact records with a summary field that contains ALL the areas separated by commas e.g
Field:Data
id:123
Name:Dave Sunerton-Burl
Address:blah blah
Areas:North West,North East,Wales,Scotland

Anyone got any good ideas? I could create a summary table and make a trigger (on the 2nd table) to create the summary information whenever there's a change, but I'd like a purely (client-side) SQL based solution if possible.

Comments

  • paulj59paulj59 Member Posts: 420
    : I'm trying to find a neat and elegant way of including a special kind of summary field in a SQL statement. I have 2 tables in a one-to-many relationship and the second table contains a field with text in (first table is contact information, 2nd contains areas that the person is prepared to work).
    : I want to find a neat way of pulling up contact records with a summary field that contains ALL the areas separated by commas e.g
    : Field:Data
    : id:123
    : Name:Dave Sunerton-Burl
    : Address:blah blah
    : Areas:North West,North East,Wales,Scotland
    :
    : Anyone got any good ideas? I could create a summary table and make a trigger (on the 2nd table) to create the summary information whenever there's a change, but I'd like a purely (client-side) SQL based solution if possible.
    :

    Something along these lines might work
    select tbl1.*, isnull(tbl2_1.area,'') + ',' + isnull(tbl2_2.area,'') + ',' + isnull(tbl2_3.area,'') as areas
    from (((tbl1 left join tbl2 as tbl2_1 on whatever)
    left join tbl2 as tbl2_2 on whatever)
    left join tbl2 as tbl2_3 on whatever)
    where tbl2_1.area<>tbl2_2.area
    and tbl2_1.area <>tbl2_3.area
    and tbl2_2.area <>tbl2_3.area


  • davesbdavesb Member Posts: 2
    Thanks for the idea, but I think it would be unweildy to put into practice - the 2nd table could contain 1 or 2 linked records, it could also contain 30. Plus, the list of areas is dynamic anyway.
    Maybe there isn't a way - I'm just being hopeful!!

    : : I'm trying to find a neat and elegant way of including a special kind of summary field in a SQL statement. I have 2 tables in a one-to-many relationship and the second table contains a field with text in (first table is contact information, 2nd contains areas that the person is prepared to work).
    : : I want to find a neat way of pulling up contact records with a summary field that contains ALL the areas separated by commas e.g
    : : Field:Data
    : : id:123
    : : Name:Dave Sunerton-Burl
    : : Address:blah blah
    : : Areas:North West,North East,Wales,Scotland
    : :
    : : Anyone got any good ideas? I could create a summary table and make a trigger (on the 2nd table) to create the summary information whenever there's a change, but I'd like a purely (client-side) SQL based solution if possible.
    : :
    :
    : Something along these lines might work
    : select tbl1.*, isnull(tbl2_1.area,'') + ',' + isnull(tbl2_2.area,'') + ',' + isnull(tbl2_3.area,'') as areas
    : from (((tbl1 left join tbl2 as tbl2_1 on whatever)
    : left join tbl2 as tbl2_2 on whatever)
    : left join tbl2 as tbl2_3 on whatever)
    : where tbl2_1.area<>tbl2_2.area
    : and tbl2_1.area <>tbl2_3.area
    : and tbl2_2.area <>tbl2_3.area
    :
    :
    :

  • Andre YoungAndre Young USAMember Posts: 0

    ______ \ http://forcoder.org \ free video tutorials and ebooks about || Swift JavaScript Ruby MATLAB Perl Delphi Visual Basic .NET PL/SQL C++ PHP Objective-C Python C# Go C Java Assembly Scratch R Visual Basic Julia Alice Fortran Erlang COBOL Scheme Lisp Awk Apex VBScript LabVIEW Rust Bash Prolog Hack F# Kotlin Dart D ABAP Ada Scala Clojure FoxPro Transact-SQL Lua SAS ML Logo Crystal || _

Sign In or Register to comment.