1d
comment SQL - Get first level nodes from a nested structure
good News!, glad you got it sorted.
1d
comment SQL - Get first level nodes from a nested structure
Dang! if you could run the last query for something you know if a top level node and something you know is not a top level node and post the result then we can look for clues together.
1d
comment SQL - Get first level nodes from a nested structure
That's a left outer join.. iits saying give me all the docman_categories that don't have matching rows in docman_category_relations (based on descendant_id) .. if the above query returns no rows then it means even top level categories are present in the relations table as descendants. now try this with a docman_category_id you KNOW is at the top level and see what you get select * from docman_categories as c left outer join docman_category_relations as r on c.docman_category_id = r.descendant_id where c.docman_category_id = 5 (assuming docman_categery_id 5 is a top level menu item)
1d
comment SQL - Get first level nodes from a nested structure
what do you get when you do select * from docman_categories as c left outer join docman_category_relations as r on c.docman_category_id = r.descendant_id where r.descendant_id is null? as in look for items that are not ever descendants
Jan
22
comment Count values of a particular column and display it monthwise and partwise
pivot may be in oracle now? sorry I use MSSQL mostly oracle.com/technetwork/articles/sql/11g-pivot-097235.html
Jan
22
comment Check if a pair of values is already used and dont allow entering differenet pair that shares one of the values with already used pair
Can you just add unique constraints on ID and Name individually? they each need to be unique independantly from the sounds of things. if this messes with your 3 column unique constrain I am not sure that one is required as easch name or ID can only be there once so therefore will only ever have one location assigned.
Jan
22
answered SQL Update to increment existing fields
Jan
16
awarded Nice Question
Jan
14
comment SQL left outer join multiple tables
LOL, thanks! such an easy mistake to make yet you could go a long time without noticing its there!
Jan
14
revised SQL left outer join multiple tables
added 2 characters in body
Jan
14
comment Query two similar tables and combine sorted results
I do a lot of data migration / master data / data review work and techniques like this are life savers :D
Jan
14
answered SQL left outer join multiple tables
Jan
14
comment Query two similar tables and combine sorted results
Agreed on the testing for this type of thing! if there is 1 payment then orders left outer join payments will have one row and ap.n will not be null. if you then have 2 projected payments there will be nothing for the second row to join to as there is no row where ap.n is null. (unless i have something wrong here).. i have never done the chat thing before but SO doesn't want us to continue here :D
Jan
14
revised Query two similar tables and combine sorted results
added 53 characters in body
Jan
14
comment Query two similar tables and combine sorted results
yep.. what i mean is if you have 1 payment but 5 projected ones then you wont have a row with an orderid but null in ap.n for the projected payments to join to.. not sure i am explaining that very well. note also on option two that if you have more than one payment per day that would have issues i think.
Jan
14
revised Query two similar tables and combine sorted results
added 53 characters in body
Jan
14
comment Query two similar tables and combine sorted results
do you not need ot force there to be a row with o.orderid not null and p.n is null to allow third table to join to it? sorry i am curious as i have not used this technique before and its interesting
Jan
14
comment Query two similar tables and combine sorted results
does this not have issues if there are more projected payments than actuals?
Jan
14
answered Query two similar tables and combine sorted results
Jan
14
comment Complex join and distinct operation does not output correct information
maybe just post example tables and what you expect the final output to be? i have no idea what you actually want to do sorry - i can see the join but why?. the only thing i can say is that the things you are trying to join on must not be the same (or at least not the same enough). sorry if this is off the mark, just trying to help.
1 2 3 4 5