sql - MySQL select command / pivot table -


i trying produce report has pivot table in it. other writing few sql statements, , doing long hand through code (excel / vba) , loops, can't seem anywhere trying build all-encompassing sql statement.

i have listed example tables below, , example of output @ end.

basically need find contact in group, , display either total occurrences or true false whether in group or not.

so, groups in groups table list contacts out indication (count etc) of them having group in contact_groups table.

is possible single statement, or better sequentially running through records. produce ridiculous amount of sql calls.

tables:  groups ------------------- | key_id | group  | ------------------- |  1     | group1 | |  2     | group2 | |  3     | group3 | |  4     | group4 | |  5     | group5 |  contacts                                           ------------------------------------------------   | key_id | account_code | first_name | surname |   ------------------------------------------------   |  1     | abc001       | john       | smith   |   |  2     | abc001       | philip     | doe     |   |  3     | abc002       | peter      | thomas  |    contact_groups --------------------------------------------------- | key_id | contact_id | group_code | account_code | --------------------------------------------------- |  1     | 1          | group1     | abc001       | |  2     | 1          | group2     | abc001       | |  3     | 2          | group1     | abc001       | |  4     | 3          | group2     | abc002       |  output req'd ----------------------------------------------------------------------- | name         | account | group1 | group2 | group3 | group4 | group5 |  ----------------------------------------------------------------------- | john smith   | abc001  | 1      | 1      |        |        |        | | philip doe   | abc001  | 1      |        |        |        |        | | peter thomas | abc002  |        | 1      |        |        |        |

sorry size of post, wanted make clear tables working with.

any appreciated.

you can use example:

select c.firstname + ' ' c.surname, account     if(g.group_code='group1',1,null) group1,     if(g.group_code='group2',1,null) group2,     if(g.group_code='group3',1,null) group3,     if(g.group_code='group4',1,null) group4,     if(g.group_code='group5',1,null) group5 groups g inner join contact_groups cg         on g.group = cg.group -- not sure, guess use key_id example isn't clear should use on place inner join contacts c         on c.contact_id = cg.contact_id group c.firstname, c.surname, c.account

well mentioned in code, i'm not sure if join g.group = cg.group good. try join key column key_id. mentioned output given table content shows won't give expected result. maybe it's bug in expected content demo.


Comments

Post a Comment

Popular posts from this blog

How to connect android app to App engine -

i'm trying connect app in android server i've opened on appengine. my server's name : dddd-daniel-2345 , website server : http://dddd-daniel-2345.appspot.com/ this how connect android app server in app engine: app.yaml: application: dddd-daniel-2345 version: 1 runtime: python27 api_version: 1 threadsafe: yes handlers: - url: .* script: main.app libraries: - name: webapp2 version: "2.5.2" main.py: #!/usr/bin/env python # # copyright 2007 google inc. google.appengine.api import users import webapp2 class userhandler(webapp2.requesthandler): def get(self): user = users.get_current_user() if user: greeting = ('%s,%s,%s' % (user.email(),user.user_id(),user.nickname())) else: greeting = ('not logged in') self.response.out.write(greeting) app = webapp2.wsgiapplication([('/', userhandler),], debug=true) my applications code : add manifast main....
Read more

gcc - MinGW's ld cannot perform PE operations on non PE output file -

i know there other similar questions out there, stackoverflow or not. i've researched lot this, , still didn't find single solution. i'm doing operative system side project. i've been doing in assembly, wanna join c code. test, made assembly code file (called test.asm): [bits 32] global _a section .text _a: jmp $ then made c file (called main.c): extern void a(void); int main(void) { a(); } to link, used file (called make.bat): "c:\mingw\bin\gcc.exe" -ffreestanding -c -o c.o main.c nasm -f coff -o asm.o test.asm "c:\mingw\bin\ld.exe" -ttext 0x100000 --oformat binary -o out.bin c.o asm.o pause i've been researching ages, , i'm still struggling find answer. hope won't flagged duplicate. acknowledge existence of similar questions, have different answers, , none work me. question: what doing wrong? old mingw versions had problem "ld" not able create non-pe files @ all. maybe current versions...
Read more

php - display validation error message next to the textbox in codeigniter -

Image
i'm newbie codeigniter.i want display error message next text box.my view file create.php contains following code. <?php echo validation_errors(); //validation echo form_open('news/create'); //create form ?> <div class="boxes"> <div class="common"> <div class="lables"> <label for="name">name</label> </div> <div class="text_boxes"> <input type="text" name="uname" /><div id="infomessage"><?php echo $message;?></br> </div> </div>` and below controller model: public function create() { $this->load->helper('form'); $this->load->library('form_validation'); $data['title'] = 'insert items'; $this->form_validation->set_rules('uname','required|min_length[6]|m...
Read more