SQL - the final solution

Two blogs ago, there was a problem. Now there is not. Thanks to Clive Eisen for telling me I was being a wanker.

Here is said solution:

Table:

DROP TABLE MAPELEMENTS;
CREATE TABLE MapElements (Auto int, Element int);
Insert into MapElements values(1,2);
Insert into MapElements values(1,3);
Insert into MapElements values(1,4);
Insert into MapElements values(1,5);
Insert into MapElements values(1,6);
Insert into MapElements values(2,7);
Insert into MapElements values(2,8);
Insert into MapElements values(2,9);
Insert into MapElements values(7,10);
Insert into MapElements values(7,11);
Insert into MapElements values(10,12);
Insert into MapElements values(10,13);
Insert into MapElements values(4,12);
Insert into MapElements values(5,13);

Procedure:

DROP PROCEDURE AlarmHierarchy

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:

-- Create date: <14>

-- Description:

-- =============================================

CREATE PROCEDURE AlarmHierarchy

-- Add the parameters for the stored procedure here

@p1 int

AS

BEGIN

SET NOCOUNT ON;

with cte as (

select auto, element from MapElements where

element = @p1 

union all

select a.auto, a.element from MapElements a

INNER JOIN cte r

on a.Element = r.Auto 

)

select * from cte order by auto, element;

END

GO

C Code (to get hierarchy containing Element 12):

SQLINTEGER iAuto, iElement, dw;

sqlRet=SQLAllocStmt(hDBC, &hStmt);

if (sqlRet!=SQL_SUCCESS&&sqlRet!=SQL_SUCCESS_WITH_INFO)

SQLError(hEnv, hDBC, hStmt, wszSqlState, &pfNativeError, wszErrorMsg, sizeof wszErrorMsg, &howBigIsItThen);

swprintf_s(TSQL, L"alarmHierarchy 12");

sqlRet=SQLPrepare(hStmt, TSQL, SQL_NTS);

if (sqlRet!=SQL_SUCCESS&&sqlRet!=SQL_SUCCESS_WITH_INFO)

SQLError(hEnv, hDBC, hStmt, wszSqlState, &pfNativeError, wszErrorMsg, sizeof wszErrorMsg, &howBigIsItThen);

sqlRet=SQLBindCol(hStmt, 1, SQL_C_LONG, &iAuto, sizeof(iAuto), &dw);

if (sqlRet!=SQL_SUCCESS&&sqlRet!=SQL_SUCCESS_WITH_INFO)

SQLError(hEnv, hDBC, hStmt, wszSqlState, &pfNativeError, wszErrorMsg, sizeof wszErrorMsg, &howBigIsItThen);

sqlRet=SQLBindCol(hStmt, 2, SQL_C_LONG, &iElement, sizeof(iElement), &dw);

if (sqlRet!=SQL_SUCCESS&&sqlRet!=SQL_SUCCESS_WITH_INFO)

SQLError(hEnv, hDBC, hStmt, wszSqlState, &pfNativeError, wszErrorMsg, sizeof wszErrorMsg, &howBigIsItThen);

sqlRet=SQLExecute(hStmt);

if (sqlRet!=SQL_SUCCESS&&sqlRet!=SQL_SUCCESS_WITH_INFO)

SQLError(hEnv, hDBC, hStmt, wszSqlState, &pfNativeError, wszErrorMsg, sizeof wszErrorMsg, &howBigIsItThen);

while(true)

{

sqlRet=SQLFetch(hStmt);

if (sqlRet!=SQL_SUCCESS&&sqlRet!=SQL_SUCCESS_WITH_INFO&&sqlRet!=SQL_NO_DATA)

{

SQLError(hEnv, hDBC, hStmt, wszSqlState, &pfNativeError, wszErrorMsg, sizeof wszErrorMsg, &howBigIsItThen);

break;

}

if (sqlRet==SQL_NO_DATA)

break;

}

SQLFreeStmt(hStmt, SQL_DROP);

Gratuitous picture of Norks :

Train

Here's another thing.

I used to have a house, once. It was near a railway.

Because some fuckwit decided to top themselves (and I don't offer sympathy, because he was a fuckwit), he decided to do the deed a mile down the line.

So, the Health and Safety Executive thus decided to knee-jerk and insist that the train should blast its fucking abominable horn right outside my house, just in case the next jumper didn't know when to throw his worthless body on the line.

The house was therefore worthless.

This is what the state can do to you, with not a worry, nor a jot of compensation.

Fuck the state.

I now live on a worthless boat having been made skint by them. Fuck them. FUCK THEM.

I have hundreds of stories like this. Yet you still vote. You fucking idiots.

SQL

If anyone can help me with this I'll pay in chocolate/beer.

I have some objects which are linked via a link table, which has Parent/Child columns.

Here is the scenario. The ones above are parents, the ones below are children.

The table linking these like this is thus:

P    C
1    2
1    3
1    4
1    5
1    6
2    7
2    8
2    9
7   10
7   11
10  12
10  13
4    12
6    13

Where P=Parent, C=Child.

I want to devise a query where if I pick a Child (say 13) I get the rows back which are affected by said child.

As in the ones asterisked:

P    C
1    2 *
1    3
1    4
1    5
1    6
2    7 *
2    8
2    9
7   10 *
7   11
10  12
10  13 *
4    12
6    13 *

I know it's doable. I'm too thick/tired to work it out. It must be easy.

The DB is SQLserver.

Miliband

FACTS:

A few years ago, Tony Blair was Prime Minister.

Gordon Brown was Chancellor.

David Miliband was head of DEFRA.

The EU introduced a Single Farm Subsidy (SFP) rule.

David Miliband commissioned a computer system to process said rule, even though every other country in the EU had one which worked.

His didn't.

He lost 50,000,000 pounds.

Miliband went to Gordon Brown who said he couldn't have the money and that other parts of DEFRA had to find it.

Miliband went to British Waterways and the Environment Agency and suggested that they ask people who pay river licences (such as boaters) pay it.

Both raised their licence fees by around 15% p.a for three years. Neither stopped raising it since. Neither thought of charging fishermen or canoeists, despite their huge numbers. Neither spent any money at all, AT ALL, on flood defences because although they knew it was coming, the cuts were made. Gordon. Tony. David.

Those are all facts, plain and simple, no reporting, no partisan crap, just facts.

Somerset is flooded. Berkshire is flooded. David Miliband has a job at 1,000,000 pounds a year talking shit in America.

His brother is a cunt.

Thanks for reading.

PS some farmers are still waiting for their SFP payment, which won't matter much, as they're mainly pig farmers and their farms are underwater, so we're importing pig, most of which has swine flu at the moment. You carry on signing petitions. I'm reloading.

Pete Wishart

I've probably got this all wrong, but there seems to be a chap called Pete Wishart, who is an MP, who went to a school in Scotland (do Scotland have schools?) and they called him a gimp. I don't know what a gimp is, so I can only imagine it's a contraction of "talentless twat who didn't try too hard at school and couldn't do anything useful so became an MP so he could suck the life out of working people".

So he took offence because they were in a "public school" and because he's a lefty he thinks anyone, however bright, or thick, is the same.

I'm probably wrong, as I say. If I'm not, can you draw his attention to this so he can have me barred from Twitter, and Blogger?

Many thanks, people. The police have my name and address so I'll put the kettle on. Hope they can swim.

BTW I was thrown out of public school. I consider myself lucky.

Also, I'm thick, my IQ was only 168 last time I took the test, so, well, fuck you, really.

xxx

Job. MkII.

I require a chap/chapess with a serious geek thing who is interested in electronics and computing, who can program passably in C, and knows Ohm's Law, and is going to be treated like shit and come out the other end a bloody genius. They need to realise that life is hard, so is work, and that they are fucking useless, but wish to better themselves.

I would really like someone who knows what they're doing who wears their underpants outside their trousers and can change in a telephone box, which is what we've asked agencies to send us.

The problem is that we can't find the people because the stupid bastards put their CVs on a web site to which only bastard job agents have access. Said job agents then make sure we get sent folk who fit any description other than the one we're looking for, because they think they understand. They do NOT.

Back in the day it was possible to find someone who did not think that the world owed them a living. Nowadays the yoof think that the world does, and the only reason people work is to pay their fucking benefits.

There is nothing wrong with learning stuff on the job, and having the benefit of some top-rate chaps who know what they're doing, from whom to learn, and in return be sent to the stores for a long weight or a new box of sparks for the grinder. And having the piss taken mercilessly. But at the end of it they'll be useful.

FUCK knows what they teach people at Uni these days. I suspect mostly its how to justify getting in 10 minutes late then making coffee and microwaving toast before discussing football or what pile of shite was on telly last night. Those folk need not apply.

But if someone wants to work and is willing to learn and isn't as thick as two planks, and his mates have a problem with him/her having the bollocks/tits to go for it instead of going to Uni to do a degree in washing teatowels, I'll come with them to the pub and put their mates right for them, with my trusty bat.

Any questions such as "how much holiday/money/statutory sick benefit do I get?" will be greeted with the bum's rush. We're not a charity. We want to know what you can do for us. At least I'm honest.

Find me someone.