Yeah Dan that was actually what I was thinking, unfortunetly I need a SQL guru to pipe in, since that

is drastically over my level ;)

On Wed, Jan 23, 2013 at 8:48 AM, Dan Munro <[address removed]> wrote:

Maybe you can make the sub query a temp table? I'm not the most experienced with using them, but temp tables can be very effective for indexing and memory management. I once wrote a reporting tool that took anywhere from 10-30 seconds per search that went down to 1-2 seconds per search after a rewrite to use temp tables.

On Wed, Jan 23, 2013 at 8:43 AM, Jd Daniel <[address removed]> wrote:

I actually ended up with something like this, but the issue is I creating a near infinite query (each id creates a sub query for 19k up, and 19k down technically), so its hits my server pretty hard and doesn't return results =(

  SELECT
    e.baseName AS baseName,
    s.baseName AS masterName,

    (SELECT COUNT(IF(bossId = e.id, 1, 0)) FROM dataSet WHERE entityId < e.entityId) as parentCount,
    (SELECT COUNT(IF(bossId = e.id, 1, 0)) FROM dataSet WHERE entityId > e.entityId) as siblingCount
  FROM
    dataSet e
    LEFT JOIN dataSet s ON s.id = e.entityId

On Wed, Jan 23, 2013 at 8:21 AM, Keith Grey <[address removed]> wrote:

How about a sub-query ?  ie something like this:

 

SELECT   e.baseName,
        s.baseName AS masterName,
(select count(*) from dataSet b where b.entityId=e.id) AS siblingCount
    FROM dataSet e

        INNER JOIN dataSet s ON s.id = e.entityId

    ORDER BY siblingCount desc

Best Regards,

 

Keith Grey
[address removed]

----- Original Message -----

From: Jd Daniel

To: [address removed]

Sent: Sunday, January 20,[masked]:57 PM

Subject: [php-49] Combining Joins help?

Hey guys I'm trying to join together two queries and am having an issue,
maybe you can take a look, I have these two separate queries i'm trying
to combine. There is a third join that will be in there to get a field
that will count how many parents it also has which I haven't started on.

    SELECT
        e.baseName AS baseName,
        s.baseName AS masterName
    FROM dataSet e
    INNER JOIN dataSet s
        ON s.id = e.entityId
    ORDER BY e.entityId

    SELECT 
        e.baseName AS baseName,
        b.baseName AS masterName,
        COUNT(*)-1 AS siblingCount
    FROM dataSet e
    JOIN dataSet b
        ON b.id=e.entityId
    GROUP BY b.id, b.baseName

I came up with this, but am having issues with it

    SELECT
        e.baseName AS baseName,
        s.baseName AS masterName,
        COUNT(*)-1 AS siblingCount
    FROM dataSet e
        JOIN dataSet b ON b.id = e.entityId
        INNER JOIN dataSet s ON s.id = e.entityId
    GROUP BY b.id, b.baseName
    ORDER BY e.entityId


Table structure is like so

    +----+--------------------+----------+
    | id | baseName           | entityId |
    +----+--------------------+----------+
    |  1 | [masked] |        1 |
    |  2 |[masked] |        1 |
    |  3 |[masked] |        1 |
    |  4 |[masked] |        2 |
    |  5 |[masked] |        2 |
    |  6 |[masked] |        2 |
    |  7 |[masked] |        2 |
    |  8 |[masked] |        2 |
    |  9 |[masked] |        3 |
    | 10 |[masked] |        3 |
    | 11 | [masked] |        3 |
    | 12 |[masked] |        3 |
    | 13 | [masked] |        3 |
    | 14 |[masked] |        3 |
    | 15 | [masked] |        3 |
    | 16 |[masked] |        4 |
    | 17 | [masked] |        4 |
    | 18 |[masked] |        4 |
    | 19 | [masked] |        4 |
    | 20 |[masked] |        4 |
    +----+--------------------+----------+


What I'm really looking for is this

    +--------------------+--------------------+--------------+-------------+
    |      baseName      |      masterName    | siblingCount | parentCount |
    +--------------------+--------------------+--------------+-------------+
    |[masked] |[masked] |            2 |           0 |
    |[masked] |[masked] |            4 |           1 |
    |[masked] |[masked] |            4 |           1 |
    |[masked] |[masked] |            6 |           2 |
    |[masked] |[masked] |            4 |           2 |
    | ....               | ....               |         .... |        .... |
    +--------------------+--------------------+--------------+-------------+



Help would be appreciated


--

Jd Daniel
Sr. Zend Developer
7901 Delridge Way SW #36D, Seattle

C.    [masked]

--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Jd Daniel ([address removed]) from The Seattle PHP Meetup Group.
To learn more about Jd Daniel, visit his/her member profile
Set my mailing list to email me As they are sent | In one daily email | Don't send me mailing list messages

Meetup, POB 4668 #37895 NY NY USA 10163 | [address removed]

--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])

This message was sent by Keith Grey ([address removed]) from The Seattle PHP Meetup Group.
To learn more about Keith Grey, visit his/her member profile

Set my mailing list to email me As they are sent | In one daily email | Don't send me mailing list messages

Meetup, POB 4668 #37895 NY NY USA 10163 | [address removed]

--

Jd Daniel
Sr. Zend Developer
7901 Delridge Way SW #36D, Seattle

C.    [masked]

--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Jd Daniel ([address removed]) from The Seattle PHP Meetup Group.
To learn more about Jd Daniel, visit his/her member profile
Set my mailing list to email me As they are sent | In one daily email | Don't send me mailing list messages

Meetup, POB 4668 #37895 NY NY USA 10163 | [address removed]

--
From the desk of Dan Munro

--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])

This message was sent by Dan Munro ([address removed]) from The Seattle PHP Meetup Group.
To learn more about Dan Munro, visit his/her member profile

Set my mailing list to email me As they are sent | In one daily email | Don't send me mailing list messages

Meetup, POB 4668 #37895 NY NY USA 10163 | [address removed]

--

Jd Daniel
Sr. Zend Developer
7901 Delridge Way SW #36D, Seattle

C.    [masked]

--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Jd Daniel ([address removed]) from The Seattle PHP Meetup Group.
To learn more about Jd Daniel, visit his/her member profile
Set my mailing list to email me As they are sent | In one daily email | Don't send me mailing list messages

Meetup, POB 4668 #37895 NY NY USA 10163 | [address removed]