Mysql merging queries

Question

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 | 000000000000000001 |        1 |
|  2 | 000000000000000002 |        1 |
|  3 | 000000000000000003 |        1 |
|  4 | 000000000000000004 |        2 |
|  5 | 000000000000000005 |        2 |
|  6 | 000000000000000006 |        2 |
|  7 | 000000000000000007 |        2 |
|  8 | 000000000000000008 |        2 |
|  9 | 000000000000000009 |        3 |
| 10 | 000000000000000010 |        3 |
| 11 | 000000000000000011 |        3 |
| 12 | 000000000000000012 |        3 |
| 13 | 000000000000000013 |        3 |
| 14 | 000000000000000014 |        3 |
| 15 | 000000000000000015 |        3 |
| 16 | 000000000000000016 |        4 |
| 17 | 000000000000000017 |        4 |
| 18 | 000000000000000018 |        4 |
| 19 | 000000000000000019 |        4 |
| 20 | 000000000000000020 |        4 |
+----+--------------------+----------+

What I'm really looking for is this

+--------------------+--------------------+--------------+-------------+
|      baseName      |      masterName    | siblingCount | parentCount | 
+--------------------+--------------------+--------------+-------------+
| 000000000000000001 | 000000000000000001 |          999 |           0 |
| 000000000000000002 | 000000000000000001 |          998 |           1 |
| 000000000000000003 | 000000000000000001 |          998 |           1 |
| 000000000000000004 | 000000000000000002 |          997 |           2 |
| 000000000000000005 | 000000000000000002 |          997 |           2 |
| ....               | ....               |         .... |        .... |
+--------------------+--------------------+--------------+-------------+

Help would be appreciated

I'm having some trouble understanding your table structure and the relationships. Can you explain it a little? That may help me craft a relevant query. — Jordan Kasper, Jan 21 '13 at 16:47
@jakerella I updated my question with some important notes and info. — ehime, Jan 21 '13 at 22:41
Much obliged! Thanks for looking into this, I got the PHP no sweat, but the sql parts been sticking me in the rear. — ehime, Jan 21 '13 at 23:32

score 0 · Accepted Answer · answered Jan 22 '13 at 16:49

0

Okay, so my biggest hurdle was just understanding your data, but I think I got it after the "job"/"tree" analogy. Below is an SQL query that I think will get your desired output. There is also an sqlfiddle with the test data I used for you to play with. Hope this helps.

SELECT
    e.baseName AS baseName, 
    s.baseName AS masterName,
    (SELECT COUNT(id) FROM dataSet WHERE entityId < e.entityId) as parentCount,
    (SELECT COUNT(id) FROM dataSet WHERE entityId > e.entityId) as siblingCount
  FROM 
    dataSet e 
    LEFT JOIN dataSet s ON s.id = e.entityId
  ORDER BY 
    e.entityId

Good luck!

answered Jan 22 '13 at 16:49

Jordan Kasper

13,153
3
36
55

Awesome, glad I was able to clear it up for you! Thanks for the stamp. – ehime Jan 22 '13 at 17:16
Glad it helped, but why did you remove the table structure and desired output? This information will greatly help someone looking for a similar answer to their problem! It also helps to see what else you tried, so they don't try it. – Jordan Kasper Jan 22 '13 at 17:26
Sorry, opportunely its proprietary and I was asked to remove it by my company after an answer was derived =( If you look at my track record, I never remove stuff unless I have a gun to my head, like right now. – ehime Jan 22 '13 at 17:34
That is really unfortunate. Perhaps you could add a similar structure (based on a family tree perhaps?); again this would really help for future answer seekers. Also, a boss who lets you find answers on a public forum, but then makes you remove half of the helpful part needs to rethink their strategy, IMO. – Jordan Kasper Jan 22 '13 at 17:44
Yeah I'm not staying here long, I'm just wrapping up the last project, they're... you know... I'll think something up and refill the question based on that later on. – ehime Jan 22 '13 at 18:32
I readded the origional question. Looks like the answer you provided has an infinite loop in it somewhere. Queries last forever until process is killed =( – ehime Jan 22 '13 at 22:07
I identified the issue as we are casting "near infinite" queries with those sub queries, where it is looking up AND down 19k times per individually id that we are operating over. Yeash! – ehime Jan 22 '13 at 23:46
Weird... I wasn't getting any of that in the sqlfiddle. I'll take s look on my local instance and see what I can find. Let me know if you figure something out. – Jordan Kasper Jan 23 '13 at 00:47
Yeah from what I see its pretty much cross selecting 19k times for each id when its running greater than, then another 19k times for less than. running limit 10 gets the results, but passes up a lot of the stuff that would be needed. – ehime Jan 23 '13 at 01:48

Mysql merging queries

1 Answers1