Apr
21
awarded Caucus
Mar
9
comment SQL left outer join multiple tables
I am so used to certain behaviors I hope i have this correct but: If you left outer join a->b and then left outer join that to a table c, you will never get rows from table c where there are none in table b. let me know if am wrong on that, its just something i don't do anymore.
Feb
12
awarded Popular Question
Feb
1
awarded Famous Question
Jan
30
comment SQL - Get first level nodes from a nested structure
good News!, glad you got it sorted.
Jan
30
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.
Jan
30
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)
Jan
30
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
1 2 3 4 5