Professional Web Applications Themes

JOIN count query dilemma - MySQL

I've been playing with this for awhile and can't achieve the desired results. I have seen messages with similar requests, but nothing that quite matches my need. I have the following query which counts the casestatusid's found in table cases if the associated caseid is found in either casespecialty or examspecialty and the uid equals 17. I need casestatusid counts without any matches to return 0. When RIGHT JOINing casespecialty OR examspecialty the query works as desired. For example: SELECT DISTINCT Count(c.casestatusid) AS statuscount, css.casestatusid AS statusid, css.casestatus FROM casestatus css LEFT JOIN cases c ON c.casestatusid = css.casestatusid RIGHT ...

  1. #1

    Default JOIN count query dilemma

    I've been playing with this for awhile and can't achieve the desired
    results. I have seen messages with similar requests, but nothing that quite
    matches my need.

    I have the following query which counts the casestatusid's found in table
    cases if the associated caseid is found in either casespecialty or
    examspecialty and the uid equals 17.

    I need casestatusid counts without any matches to return 0.

    When RIGHT JOINing casespecialty OR examspecialty the query works as
    desired. For example:


    SELECT DISTINCT Count(c.casestatusid) AS statuscount, css.casestatusid AS
    statusid, css.casestatus
    FROM casestatus css
    LEFT JOIN cases c ON c.casestatusid = css.casestatusid
    RIGHT JOIN casespecialty cs ON c.caseid = cs.caseid AND cs.uid = 17
    GROUP BY css.casestatusid
    ORDER BY css.casestatusid

    ....or...

    SELECT DISTINCT Count(c.casestatusid) AS statuscount, css.casestatusid AS
    statusid, css.casestatus
    FROM casestatus css
    LEFT JOIN cases c ON c.casestatusid = css.casestatusid
    RIGHT JOIN examspecialty es ON c.caseid = es.caseid AND es.uid = 17
    GROUP BY css.casestatusid
    ORDER BY css.casestatusid


    However, I need to combine both casespecialty and examspecialty in one query
    (preferably.) In other words, I need to effectively combine


    RIGHT JOIN casespecialty cs ON c.caseid = cs.caseid AND cs.uid = 17

    ....and...

    RIGHT JOIN examspecialty es ON c.caseid = es.caseid AND es.uid = 17


    Doing so will throw a cross dependency OUTER JOIN error.

    Thanks in advance.


    Bosconian Guest

  2. #2

    Default Re: JOIN count query dilemma

    On 13 Feb, 03:34, "Bosconian" <com> wrote: 

    If you could post the schema as a create table and some sample data,
    I'll have a play with the JOINs.
    However one way of achieving this might be to UNION ALL the 2 queries
    either as they are and then SUMming the statuscount in an outer select
    or removing the individual counts/groups/orders and just counting in
    teh outer select.

    Captain Guest

  3. #3

    Default Re: JOIN count query dilemma

    "Captain Paralytic" <com> wrote in message
    news:googlegroups.com... 
    >
    > If you could post the schema as a create table and some sample data,
    > I'll have a play with the JOINs.
    > However one way of achieving this might be to UNION ALL the 2 queries
    > either as they are and then SUMming the statuscount in an outer select
    > or removing the individual counts/groups/orders and just counting in
    > teh outer select.
    >[/ref]

    Thanks for your response.

    I have included the schema for 'casespecialty' and 'examspecialty' tables
    plus 'casestatus' below. The data should hopefully be obvious according to
    the column definitions.

    Your solution sounds promising. Could you post an example of what you mean?

    ---------------------------------------------------

    CREATE TABLE `casespecialty` (
    `casespecialtyid` int(11) NOT NULL default '0',
    `caseid` int(11) NOT NULL default '0',
    `specialtyid` int(11) NOT NULL default '0',
    `uid` int(11) NOT NULL default '0',
    PRIMARY KEY (`casespecialtyid`),
    KEY `caseid` (`caseid`),
    KEY `specialtyid` (`specialtyid`),
    KEY `uid` (`uid`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;

    CREATE TABLE `examspecialty` (
    `examspecialtyid` int(11) NOT NULL default '0',
    `caseid` int(11) NOT NULL default '0',
    `examid` int(11) NOT NULL default '0',
    `specialtyid` int(11) NOT NULL default '0',
    `uid` int(11) NOT NULL default '0',
    PRIMARY KEY (`examspecialtyid`),
    KEY `caseid` (`caseid`),
    KEY `examid` (`examid`),
    KEY `specialtyid` (`specialtyid`),
    KEY `uid` (`uid`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;

    CREATE TABLE `casestatus` (
    `casestatusid` int(11) NOT NULL default '0',
    `casestatus` varchar(50) NOT NULL default '',
    PRIMARY KEY (`casestatusid`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;


    Bosconian Guest

  4. #4

    Default Re: JOIN count query dilemma

    On 13 Feb, 13:25, "Bosconian" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Thanks for your response.
    >
    > I have included the schema for 'casespecialty' and 'examspecialty' tables
    > plus 'casestatus' below. The data should hopefully be obvious according to
    > the column definitions.
    >
    > Your solution sounds promising. Could you post an example of what you mean?
    >
    > ---------------------------------------------------
    >
    > CREATE TABLE `casespecialty` (
    > `casespecialtyid` int(11) NOT NULL default '0',
    > `caseid` int(11) NOT NULL default '0',
    > `specialtyid` int(11) NOT NULL default '0',
    > `uid` int(11) NOT NULL default '0',
    > PRIMARY KEY (`casespecialtyid`),
    > KEY `caseid` (`caseid`),
    > KEY `specialtyid` (`specialtyid`),
    > KEY `uid` (`uid`)
    > ) ENGINE=MyISAM DEFAULT CHT=latin1;
    >
    > CREATE TABLE `examspecialty` (
    > `examspecialtyid` int(11) NOT NULL default '0',
    > `caseid` int(11) NOT NULL default '0',
    > `examid` int(11) NOT NULL default '0',
    > `specialtyid` int(11) NOT NULL default '0',
    > `uid` int(11) NOT NULL default '0',
    > PRIMARY KEY (`examspecialtyid`),
    > KEY `caseid` (`caseid`),
    > KEY `examid` (`examid`),
    > KEY `specialtyid` (`specialtyid`),
    > KEY `uid` (`uid`)
    > ) ENGINE=MyISAM DEFAULT CHT=latin1;
    >
    > CREATE TABLE `casestatus` (
    > `casestatusid` int(11) NOT NULL default '0',
    > `casestatus` varchar(50) NOT NULL default '',
    > PRIMARY KEY (`casestatusid`)
    > ) ENGINE=MyISAM DEFAULT CHT=latin1;- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Whilst the data may be obvious, I really don't have the time to work
    out suitable test data for this scenario and then put it all into the
    table. That's why I was hoping you'd post some extended inserts of
    ready made sample data.

    Anyway, here is a snapshot of 1 of my 2 suggestions. Completely
    untested so it may have syntax errors:

    SELECT COUNT(comb.casestatusid) statuscount, comb.casestatusid
    statusid, comb.casestatus
    FROM (
    (SELECT c.casestatusid, css.casestatusid AS
    statusid, css.casestatus
    FROM casestatus css
    LEFT JOIN cases c ON c.casestatusid = css.casestatusid
    RIGHT JOIN casespecialty cs ON c.caseid = cs.caseid AND cs.uid = 17
    )
    UNION ALL

    (SELECT c.casestatusid, css.casestatusid AS
    statusid, css.casestatus
    FROM casestatus css
    LEFT JOIN cases c ON c.casestatusid = css.casestatusid
    RIGHT JOIN examspecialty es ON c.caseid = es.caseid AND es.uid = 17
    )) comb
    GROUP BY comb.casestatusid
    ORDER BY comb.casestatusid

    Captain Guest

  5. #5

    Default Re: JOIN count query dilemma

    "Captain Paralytic" <com> wrote in message
    news:googlegroups.com... 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >> 
    >>
    >> Thanks for your response.
    >>
    >> I have included the schema for 'casespecialty' and 'examspecialty' tables
    >> plus 'casestatus' below. The data should hopefully be obvious according
    >> to
    >> the column definitions.
    >>
    >> Your solution sounds promising. Could you post an example of what you
    >> mean?
    >>
    >> ---------------------------------------------------
    >>
    >> CREATE TABLE `casespecialty` (
    >> `casespecialtyid` int(11) NOT NULL default '0',
    >> `caseid` int(11) NOT NULL default '0',
    >> `specialtyid` int(11) NOT NULL default '0',
    >> `uid` int(11) NOT NULL default '0',
    >> PRIMARY KEY (`casespecialtyid`),
    >> KEY `caseid` (`caseid`),
    >> KEY `specialtyid` (`specialtyid`),
    >> KEY `uid` (`uid`)
    >> ) ENGINE=MyISAM DEFAULT CHT=latin1;
    >>
    >> CREATE TABLE `examspecialty` (
    >> `examspecialtyid` int(11) NOT NULL default '0',
    >> `caseid` int(11) NOT NULL default '0',
    >> `examid` int(11) NOT NULL default '0',
    >> `specialtyid` int(11) NOT NULL default '0',
    >> `uid` int(11) NOT NULL default '0',
    >> PRIMARY KEY (`examspecialtyid`),
    >> KEY `caseid` (`caseid`),
    >> KEY `examid` (`examid`),
    >> KEY `specialtyid` (`specialtyid`),
    >> KEY `uid` (`uid`)
    >> ) ENGINE=MyISAM DEFAULT CHT=latin1;
    >>
    >> CREATE TABLE `casestatus` (
    >> `casestatusid` int(11) NOT NULL default '0',
    >> `casestatus` varchar(50) NOT NULL default '',
    >> PRIMARY KEY (`casestatusid`)
    >> ) ENGINE=MyISAM DEFAULT CHT=latin1;- Hide quoted text -
    >>
    >> - Show quoted text -[/ref]
    >
    > Whilst the data may be obvious, I really don't have the time to work
    > out suitable test data for this scenario and then put it all into the
    > table. That's why I was hoping you'd post some extended inserts of
    > ready made sample data.
    >
    > Anyway, here is a snapshot of 1 of my 2 suggestions. Completely
    > untested so it may have syntax errors:
    >
    > SELECT COUNT(comb.casestatusid) statuscount, comb.casestatusid
    > statusid, comb.casestatus
    > FROM (
    > (SELECT c.casestatusid, css.casestatusid AS
    > statusid, css.casestatus
    > FROM casestatus css
    > LEFT JOIN cases c ON c.casestatusid = css.casestatusid
    > RIGHT JOIN casespecialty cs ON c.caseid = cs.caseid AND cs.uid = 17
    > )
    > UNION ALL
    >
    > (SELECT c.casestatusid, css.casestatusid AS
    > statusid, css.casestatus
    > FROM casestatus css
    > LEFT JOIN cases c ON c.casestatusid = css.casestatusid
    > RIGHT JOIN examspecialty es ON c.caseid = es.caseid AND es.uid = 17
    > )) comb
    > GROUP BY comb.casestatusid
    > ORDER BY comb.casestatusid
    >[/ref]

    Thanks again for your reply. It was very informative.

    Your example does return a combine list, but excludes 0 counts. It also
    counts the same caseid twice when found in both casespecialty and
    examspecialty. I need unique caseid values only.

    Sorry about the incomplete data.

    If it's not too late, here the full schema for casestatus. 'cases' is an
    extensive table, but the only relevant field is caseid.

    CREATE TABLE `casestatus` (
    `casestatusid` int(11) NOT NULL default '0',
    `casestatus` varchar(50) NOT NULL default '',
    PRIMARY KEY (`casestatusid`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;

    --
    -- Dumping data for table `casestatus`
    --

    INSERT INTO `casestatus` VALUES (10, 'Unassigned');
    INSERT INTO `casestatus` VALUES (15, 'Awaiting Scheduling');
    INSERT INTO `casestatus` VALUES (20, 'Awaiting Letter');
    INSERT INTO `casestatus` VALUES (25, 'Awaiting Records');
    INSERT INTO `casestatus` VALUES (30, 'File Preparation');
    INSERT INTO `casestatus` VALUES (40, 'Awaiting Summary');
    INSERT INTO `casestatus` VALUES (50, 'File Review');
    INSERT INTO `casestatus` VALUES (60, 'Awaiting Transcription');
    INSERT INTO `casestatus` VALUES (70, 'Quality Assurance');
    INSERT INTO `casestatus` VALUES (80, 'Review/Signoff');
    INSERT INTO `casestatus` VALUES (95, 'Awaiting Billing');
    INSERT INTO `casestatus` VALUES (99, 'Closed (Invoiced)');


    Bosconian Guest

  6. #6

    Default Re: JOIN count query dilemma

    On 13 Feb, 21:02, "Bosconian" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]




    >
    > Thanks again for your reply. It was very informative.
    >
    > Your example does return a combine list, but excludes 0 counts. It also
    > counts the same caseid twice when found in both casespecialty and
    > examspecialty. I need unique caseid values only.
    >
    > Sorry about the incomplete data.
    >
    > If it's not too late, here the full schema for casestatus. 'cases' is an
    > extensive table, but the only relevant field is caseid.
    >
    > CREATE TABLE `casestatus` (
    > `casestatusid` int(11) NOT NULL default '0',
    > `casestatus` varchar(50) NOT NULL default '',
    > PRIMARY KEY (`casestatusid`)
    > ) ENGINE=MyISAM DEFAULT CHT=latin1;
    >
    > --
    > -- Dumping data for table `casestatus`
    > --
    >
    > INSERT INTO `casestatus` VALUES (10, 'Unassigned');
    > INSERT INTO `casestatus` VALUES (15, 'Awaiting Scheduling');
    > INSERT INTO `casestatus` VALUES (20, 'Awaiting Letter');
    > INSERT INTO `casestatus` VALUES (25, 'Awaiting Records');
    > INSERT INTO `casestatus` VALUES (30, 'File Preparation');
    > INSERT INTO `casestatus` VALUES (40, 'Awaiting Summary');
    > INSERT INTO `casestatus` VALUES (50, 'File Review');
    > INSERT INTO `casestatus` VALUES (60, 'Awaiting Transcription');
    > INSERT INTO `casestatus` VALUES (70, 'Quality Assurance');
    > INSERT INTO `casestatus` VALUES (80, 'Review/Signoff');
    > INSERT INTO `casestatus` VALUES (95, 'Awaiting Billing');
    > INSERT INTO `casestatus` VALUES (99, 'Closed (Invoiced)');- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    To remove duplicates change UNION ALL to UNION.

    To add the zero counts just UNION the outer query with another query
    that checks for failed matches using WHERE c.casestatusid IS NULL and
    hard codes the 0 into the SELECT statement.

    Captain Guest

Similar Threads

  1. Join Query
    By semi in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 22nd, 01:20 PM
  2. Count GROUP BY query dilemma
    By Bosconian in forum MySQL
    Replies: 5
    Last Post: December 23rd, 07:03 AM
  3. (simple?) query dilemma--help please
    By Bosconian in forum MySQL
    Replies: 2
    Last Post: November 19th, 02:00 AM
  4. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM
  5. SQL join query help
    By poff in forum PHP Development
    Replies: 2
    Last Post: July 13th, 02:36 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