SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

Alex007: Jul 8, 2004

Victor posted:

Who knows how much intellisense will make it into SQL2008.

It doesn't display the nullable status of fields, but SQL Prompt does a great job at doing what intellisense would do:

http://www.red-gate.com/products/SQL_Prompt/index.htm

# ¿ Nov 2, 2007 14:51

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 17:36

Alex007: Jul 8, 2004

jwnin posted:

(...)

Can anyone provide me with some more ammo as to why we would not want to go down this path?

Are the database running on your server for your clients (IE they are using your server all the time) or is it just a backup you want to have access to ?

Well, at first, updating thoses databases is gonna be a BITCH. "Hey joe, can you fix that stored procedure in the clients databases ? Sure boss, let me run that query in 1097 database, I'll be right back !"

The risk of mistales is big too, because one or a few clients DB could end up different from the rest because of all that updating. comparing all theses database is also gonna be a bith, even with great tools.

Also, you absolutely WON'T be able to query all clients at once, even if your boss thinks he's never gonna need it, he WILL. "Hey let's find out if somebody had that bug where the transactions were written all reversed..."

Caching is gonna be ugly, if all clients use stored procedure X, they all gonna end up with a cached copy of the plan for that SP, even if it's exactly the same SP. That will mean fewer cached plans since you'll have so much identical plans cached (1000 copies of storedproc X's plan will be flushed when they try to run storedproc Y 1000 times too.

# ¿ Nov 9, 2007 12:55

Alex007: Jul 8, 2004

chocojosh posted:

Bringing the discussion back to SQL Server 2005 for a quick moment.

There is one add-on that would help me save HOURS of work. I'd love it if I have some text highlighted, that one of my right click options would be to "go to definition" for a stored proc, user defined function (and even table, although I'd prefer just a select *)

For example, right now I want to see a stored procedure called up_extractfirstvalue because I am not sure how it is being used in my stored procedure. If I could simply highlight up_extractfirstvalue, right click, and select "Go To definition" instead of finding it in our list of thousands of stored procs, it would be so helpful!

In SQL Server management studio, just map CTRL-F1 to "sp_helptext".

Go to Tools > Options > Environment > Keyboard.

Then, all you have to do is higlight a storedproc / table / function, and press CTRL-F1.

Edit:

# ¿ Nov 12, 2007 16:31

Alex007: Jul 8, 2004

chocojosh posted:

Great tip!

Would be better if it opened a new window and allow me to edit the query also (although I didn't ask for that initially, I know).

I agree, that would be even cooler.

I just end up copy-pasting what I need into a new window and modify from there.

On thing that [b]SUCKS[b] with that, when doing that for a stored proc, each line is padded with some spaces (usually 3). It doesn't do anything wrong, but it's frustrating to press END and end up 3 spaces after the last character of that line. And if you do alter the stored proc from that script, the spaces are saved with the script and stay forever (until osmeone removes them).

# ¿ Nov 12, 2007 19:04

Alex007: Jul 8, 2004

fletcher posted:

What does the INNER do? What happens if you take it out?

It JOINs tables for a SELECT, you don'T want to take it out, that'S your solution.

It's like your "WHERE a.id = b.id" clause, that's a JOIN too, but that's the old way to join tables.

# ¿ Nov 21, 2007 12:59

Alex007: Jul 8, 2004

Melraidin posted:

(...)

Is there a way to get a running total from MS SQL in the same manner as from MySQL? Or, as I suspect, is getting a running total from MySQL a bad idea to begin with?

Calculating Running Totals, Subtotals and Grand Total in MSSQL Without a Cursor

# ¿ Dec 5, 2007 16:10

Alex007: Jul 8, 2004

Melraidin posted:

Thanks for the link, and this does do what I need. This article shows examples of solving this in the manner I would expect, but hadn't thought of. The major difference between this method (a sub-query for each row) and the MySQL method, at least from my understanding, is the MySQL method will only do the single query on the table, while the article's method will be doing Number of Rows + 1 queries on the table.

If you want a GREAT book with a lot of complex problems solved, read Joe Celko's "SQL for Smarties - Advanced SQL Programming", it's an AWESOME book with lots of great solutions.

# ¿ Dec 5, 2007 16:39

Alex007: Jul 8, 2004

foghorn posted:

I've been banging my head against the wall over this one all night.

The following throws an T_ENCAPSED_STRING error, assume properly passed variables from HTML form:
code:
$description = addslashes($description);

$query = "INSERT INTO nations (name, description, population, flag, active, language, mod, kind, password) VALUES ('".$name"', '".$description."', '".$population."', '".$flag."', ".$active.", '".$language."', '0', '".$type."', '".$password."')";
mysql_query($query) or die(mysql_error());
Everything else on that page is used elsewhere in the app somewhere, so I know it's not the problem.

You missed a period after $name :

code:

(...) VALUES ('".$name[u].[/u]"', (...)

# ¿ Dec 11, 2007 12:51

Alex007: Jul 8, 2004

fletcher posted:

PDO/MySQL question:

If I'm executing the following query: SELECT id, name, detail FROM user WHERE detail = :filter LIMIT 0, 25 is there an easy way to get the total number of results or know if there are more than 25 for pagination without running the query twice, one time without the LIMIT portion and just doing a SELECT COUNT(id) FROM user WHERE detail = :filter?

Meet your new friend SQL_CALC_FOUND_ROWS !!!

http://www.arraystudio.com/as-workshop/mysql-get-total-number-of-rows-when-using-limit.html

# ¿ Dec 21, 2007 17:05

Alex007: Jul 8, 2004

ray2k posted:

In sql server 2000, is there any way to check if a Text or varchar parameter is a valid xml document or fragment? Sql 2005 is not available for financial and political reasons. Basically, calling sp_xml_preparedocument with inputs that aren't valid xml causes an error that appears to be un-trappable. I'd like to have this SP indicate that condition by return code instead of bailing during execution.

This should work:

code:

CREATE PROCEDURE GetHandleToXMLDocument

        /*
        This procedure expects 1 parameter:
                1. An output parameter that is a handle to a DomDocument

        A temporary table named #T_XML ( doc_xml ntext ) with 1 register and any XML text must also exists.
        Instead of using this temp table you can add one parameter of type ntext with your XML. Inside the
        procedure you can create the table and insert that ntext. This procedure is part of a database project
        and I use a temporary table because I have XML stored if column fields and I can't create ntext variables
        so this is a solution.

        This procedure creates a stream object, loads the XML text from the #T_XML table, creates a DomDocument object
        and then loads this DomDocument object from the stream.

        This procedure returns 0 if text from #T_XML was a well formed XML document or <> 0 if it wasn't.
        It also returns in the output parameter a handle to that DomDocument.

        This procedure uses calls to sp_OA's extended stored procedures, so if you have not permissions to execute that
        extended stores procedures it will fail.
        You must also have to install MDAC 2.6 in the server if you hasn't. It is possible to use other MDAC version, simply
        replace 'ADODB.Stream.2.6' , 'Msxml2.DOMDocument.2.6' with your version. If think if you have installed Enterprise
        Manager in your server MDAC 2.6 will be already installed.

        */

        @oXMLDoc int output                     -- handle to a DomDocument

AS

        /* Variables */
        declare @oStream int,
                @hr int,
                @VaLen int,
                @VaPortion varbinary(8000),
                @VaRest nvarchar(200),
                @VaOffset int,
                @VaNocount int,
                @VaLoad int,
                @VaEncoding varchar(10)

        /* No count */
        select @VaNocount = @@options & 512
        set nocount on

        /* Default encoding */
        select @VaEncoding = 'UTF-8'

        /* Creation of the stream object */
        execute @hr = sp_OACreate 'ADODB.Stream.2.6', @oStream output
        if @hr <> 0 goto Finish
        /* a binary stream */
        execute @hr = sp_OASetProperty @oStream, 'Type', 1
        if @hr <> 0 goto Finish
        /* Open stream */
        execute @hr = sp_OAMethod @oStream, 'Open', null
        if @hr <> 0 goto Finish

        /* Put text into stream: I read portions of 100 characters; you can change it (up to 3000) */

        select @VaLen = datalength ( doc_xml ) / 2 FROM #T_XML  -- length in bytes of text
        select @VaOffset = 1
        while @VaOffset <= @VaLen
        begin
                /* Take 100 characters */
                select @VaPortion = convert ( varbinary(8000), convert ( varchar(3000), substring ( doc_xml, @VaOffset, 100 ) ) ) from #T_XML
                /* put data in stream */
                execute @hr = sp_OAMethod @oStream, 'Write', null, @VaPortion
                if @hr <> 0 goto Finish
                /* next portion */
                select @VaOffset = @VaOffset + 100
        end

        /* reset stream */
        execute @hr = sp_OASetProperty @oStream, 'Position', 0
        if @hr <> 0 goto Finish
        /* change stream to text */
        execute @hr = sp_OASetProperty @oStream, 'Type', 2
        if @hr <> 0 goto Finish
        /* set encoding */
        execute @hr = sp_OASetProperty @oStream, 'Charset', @VaEncoding
        if @hr <> 0 goto Finish

        /* create DomDocument */
        execute @hr = sp_OACreate 'Msxml2.DOMDocument.2.6', @oXMLDoc output
        if @hr <> 0 goto Finish
        /* Load DomDocument from stream */
        execute @hr = sp_OAMethod @oXMLDoc, 'Load', @VaLoad output, @oStream
        if @hr <> 0 goto Finish
        /* if @VaLoad is 0, XML is not well formed. return anything <> 0 to show there was an error */      
        if @hr = 0 and @VaLoad = 0 select @hr = 2

Finish:
        /* kill objects */
        if @oStream is not null execute sp_OADestroy @oStream        
        /* no count */
        if @VaNocount > 0 set nocount on else set NOCOUNT off
        /* Delete data from temp table */
        delete from #T_XML

        /* return result */
        return ( @hr )
GO

(From: http://tinyurl.com/3684yt)

# ¿ Jan 23, 2008 17:55

Alex007: Jul 8, 2004

Im going out on a limb here, but I think you have to name the result of your inner SELECT if you do something with it (like your WHERE):

code:

	SELECT *
	FROM
	(
		SELECT id, title, subtitle, body,
		ROW_NUMBER() OVER(ORDER BY id DESC) as row
		FROM blog
	) [b]AS MyRows[/b]
	WHERE row BETWEEN (@blogPage - 1) * (@pageSize + 1) and (@blogPage * @pageSize)
	ORDER BY id DESC

# ¿ Feb 8, 2008 03:55

Alex007: Jul 8, 2004

GroceryBagHead posted:

Hey. A very stupid question

let's say I need to construct a query that goes something like this.

1. There are many grassy knolls
2. Each knoll can have a bunch of unicorns or no unicorns at all

Problem.

I need to select all grassy knolls where there are no dead unicorns.

It should look like this:

code:

SELECT
  GrassyKnolls.*
FROM
  GrassyKnolls
  LEFT JOIN (
    --Subselect to count the dead motherfuckers per grassy knoll
    SELECT
      Unicorns.GrassyKnollId
      COUNT(*) AS DeadCount
    FROM
      Unicorns
    WHERE
      Unicorns.Dead = 1  
    GROUP BY
      Unicorns.GrassyKnollId
  ) AS DeadUnicorns
  ON GrassyKnolls.GrassyKnollId = DeadUnicorns.GrassyKnollId
WHERE
  DeadUnicorns.DeadCount IS NULL
  OR DeadUnicorns.DeadCount = 0

VVVV You were right about the OR, thanks !

Alex007 fucked around with this message at 22:24 on Feb 18, 2008

# ¿ Feb 18, 2008 22:14

Alex007: Jul 8, 2004

GroceryBagHead posted:

Thanks Alex, you're the best. I caught the OR thing as well

Hey, no problem

IsaacNewton's solution is great too, depending on the number or grassy knolls and/or dead unicorns, his solution may be faster (if there are fewer grassy knoll with dead unicorns in them).

# ¿ Feb 18, 2008 22:47

Alex007: Jul 8, 2004

Even faster now, with a LEFT anti-JOIN the exclude knolls with dead unicorns in them:

code:

SELECT
  GrassyKnolls.*
FROM
  GrassyKnolls
  LEFT JOIN (
    --Subselect to list ONLY the knolls with dead motherfuckers in them
    SELECT
      Unicorns.GrassyKnollId
    FROM
      Unicorns
    WHERE
      Unicorns.Dead = 1  
    GROUP BY
      Unicorns.GrassyKnollId
    HAVING
      COUNT(*) > 0
  ) AS DeadUnicorns
  ON GrassyKnolls.GrassyKnollId = DeadUnicorns.GrassyKnollId
WHERE
  DeadUnicorns.GrassyKnollId IS NULL

# ¿ Feb 18, 2008 22:50

Alex007: Jul 8, 2004

Victor posted:

code:

select  g.*
from    GrassyKnolls g
left join Unicorns u on u.Dead = 1 and u.GrassyKnollId = g.id
where   u.id is null

You're gonna have to throw a DISTINCT there, otherwise hello grassy knolls multiplication !

# ¿ Feb 19, 2008 16:54

Alex007: Jul 8, 2004

Victor posted:

I don't know how to say this, other than no. Anti-joins like that don't need DISTINCT.

You're right, I'm a dumbass for not thinking about it, sorry.

# ¿ Feb 19, 2008 18:24

Alex007: Jul 8, 2004

nbv4 posted:

That actually doesn't work because value1 is actually "SUM(another_value) AS value1" so it gives me this error: "#1247 - Reference 'value1' not supported (reference to group function)"

I think making a third column would be best because some instances there may be a value in both value1 and value2.

You can't sort on grouped fileds, but you can do it (sort) in an outer select:

code:


SELECT * FROM (SELECT SUM(field1) AS f1, SUM(field2) AS f2 FROM mytable) AS mydata ORDER BY f1+f2

Alex007 fucked around with this message at 19:48 on Mar 25, 2008

# ¿ Mar 25, 2008 19:37

Alex007: Jul 8, 2004

nbv4 posted:

as you can see, it seems to think by "ORDER BY" I really meant "RANDOMIZE BY" If I order it by either total or simulator alone, it works as expected, but adding that plus sign screwes it all up.

In the outer SELECT, convert NULLs to zeros, because NULL + anything = NULL.

code:

ORDER BY (COALESCE(mytable.total, 0) + COALESCE(mytable.simulator,0))

# ¿ Mar 26, 2008 00:54

Alex007: Jul 8, 2004

GroceryBagHead posted:

Fun little problem.

Let's say I have following tables:
---
flower_bees <= 2 join tables with flower_id, and bee_id in both of them
bee_flowers
--
bees
--

I need to grab all bees that are found in both flower_bees and bee_flowers.

I have a solution using Union, but wondering if there's a nicer way

If I understand your problem, this is what I would do:

code:

SELECT DISTINCT bees.beeid 
FROM bees
INNER JOIN flower_bees ON flower_bees.beeid = bees.beeid  
INNER JOIN bee_flowers ON bee_flowers.beeid = bees.beeid

# ¿ Apr 3, 2008 15:08

Alex007: Jul 8, 2004

GroceryBagHead posted:

Sorry, I need to clarify: I need bees that are in flower_bees OR bee_flowers.

code:

SELECT DISTINCT bees.beeid 
FROM bees
LEFT JOIN flower_bees ON flower_bees.beeid = bees.beeid  
LEFT JOIN bee_flowers ON bee_flowers.beeid = bees.beeid  
WHERE flower_bees.beeid IS NOT NULL OR bee_flowers.beeid IS NOT NULL

# ¿ Apr 3, 2008 15:24

Alex007: Jul 8, 2004

Rakshas posted:

I have two tables - the first one has a bunch of company information and user#s. The second table is strange, as it has a bunch of rows I need formatted as columns in the first table. I need one query to rule them all. I'd like to run the query and get something that resembles the Result Table.

What platform is this gonna run on ? SQL 2005, SQL 2000, MySQL, Postgres ?

# ¿ Apr 8, 2008 14:09

Alex007: Jul 8, 2004

Grigori Rasputin posted:

What's the proper way to output a string from a stored procedure in Transact-SQL(MS SQLServer 2000)? I am finishing up an ancient project for someone and am not too familiar with it.

How do I return a string value through an output param? Is there a better way to do this?

Here's how to use an output parameter:

code:

CREATE PROCEDURE MyProc(@Param1 int, @Param2 int, @OutParam varchar(200) output) AS ...

Then, when you call the SP, you MUST pass it a variable and add the "output" keyword.

code:

DECLARE @MyOutParam varchar(200)
EXEC MyProc 1, 2, @MyOutParam output

Edit: Also, why are you trying to return in the same param that you received, without changing it at all ?

Alex007 fucked around with this message at 21:48 on May 12, 2008

# ¿ May 12, 2008 21:45

Alex007: Jul 8, 2004

Re-join with the same table, and use an alias:

code:

SELECT TASKS.CDESCRIPTION,
       USERS.CUSERNAME    AS [RESPONDER],
       USERS2.CUSERNAME    AS [ASSIGNEDTO]
FROM   TASKS
       INNER JOIN USERS
         ON USERS.NUSERS_ID = TASKS.NRESPONDERS_ID
       INNER JOIN USERS AS USERS2
         ON USERS2.NUSERS_ID = TASKS.NASSIGNEDTO_ID

Also, hungarian notation :barf:

# ¿ May 13, 2008 14:58

Alex007: Jul 8, 2004

cLin posted:

I am using phpMyAdmin. It has a Length/Value field right after you select the data type.

It's the size of the field.

It's clearer for char data types, it's the max number of chars you can fit in that field. For INT types, it's not the max value, it's the number of bytes the field has.

Leave it empty for INT data types, mysql know the right size according to your INT type selection.

Here's the table for int sizes:

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

# ¿ May 13, 2008 19:49

Alex007: Jul 8, 2004

MuppetPastor posted:

Awesome. Thank you so much. Now I'm on to my next oddity. I use a parameter defaulted to null in a stored procedure for each field in the table. Then I use isnull() to test if the parameter was passed in. If so, I match it in my where clause, and if not, I just match the field to itself. Like this:

However, if Field2 is a varchar field and @field2 is null, the query returns no rows.

Any idea why?

The correct way to do it would be:

code:

SELECT Field1, Field2
FROM Table1
WHERE (Table1.Field1 = @Field1 OR @Field1 IS NULL)
AND (Table1.Field2 = @Field2 OR @Field2 IS NULL)

# ¿ May 13, 2008 20:09

Alex007: Jul 8, 2004

MuppetPastor posted:

code:
...
WHERE Field1 = isnull(@field1, field1)
AND field2 = isnull(@field2, field2)
This won't return any rows in which field1 or field 2 IS NULL

It's because NULL <> NULL (they are not equal).

So when @field1 is NULL, SQL sees this

code:

WHERE NULL = isnull(NULL, NULL)

Which is:

code:

WHERE NULL = NULL

And it's false.

# ¿ May 13, 2008 20:27

Alex007: Jul 8, 2004

Civil posted:

This should be really simple, but I'm just not getting it. Pretty new to mysql.

...

My first query was:
SELECT * FROM tablea left join tableb on tablea.field1 = tableb.field1

Which join do I want, or do I need another operator?

The answer is not in the JOIN, it's in the GROUP BY:

code:

SELECT TableA.Field1, MIN(TableB.Field2) AS MyField2
FROM TableA INNER JOIN TableB ON TableA.Field1 = TableB.Field1
GROUP BY TableA.Field1

Edit: Or LEFT join if you want rows from TableA even if they don'T have a row in TableB

# ¿ May 21, 2008 00:55

Alex007: Jul 8, 2004

Civil posted:

I do want records from TableA, even if they don't have a database match in TableB. My problem is that LEFT JOIN is giving me multiple returns on TableA when there are multiple matches on TableB. I only want the first match on TableB.

This is why you need to GROUP BY, because JOIN *WILL* return all rows from table B when the join condition is met.

Either you GROUP BY, just like I posted, of you join on a subquery to "prepare" table B, which would be slower and stupid. Here's what it would look like anyway:

code:

SELECT TableA.Field1, MyTableB.Field2
FROM TableA LEFT JOIN (SELECT TableB.Field1, MAX(TableB.Field2) AS Field2 GROUP BY TableB.Field1) AS MyTableB

But the correct way is what I posted earlier:

code:

SELECT TableA.Field1, MAX(TableB.Field2) AS MyField2 
FROM TableA LEFT JOIN TableB ON TableA.Field1 = TableB.Field1 
GROUP BY TableA.Field1

# ¿ May 21, 2008 01:09

Alex007: Jul 8, 2004

Stephen posted:

...

Untested, but this is what you need I'm pretty sure

code:

SELECT a.id 
FROM autos a 
LEFT JOIN votes v ON v.auto_id = a.id 
WHERE (v.ip_address != "This IP address" AND v.date_added != "Today") [u]OR V.id IS NULL[/u]

VVV Too slow old man

# ¿ May 21, 2008 21:36

Alex007: Jul 8, 2004

Stephen posted:

Hah, this is perfect, thanks.

Just doing my job

Stephen posted:

I just assumed that since the IP address and Date values did not equal null, it should be returning the results anyways.

Thanks again.

You can't test NULL with = or != because NULL is unknown.

If I show you two boxes and you do not know what's in them, you can't say the content is the same because they are BOTH unknown. = and != would BOTH return FALSE.

code:

SELECT CASE WHEN NULL = NULL THEN 'TRUE!!' ELSE 'FALSE' END AS What1
SELECT CASE WHEN NULL != NULL THEN 'TRUE!!' ELSE 'FALSE' END AS What2

# ¿ May 21, 2008 21:47

Alex007: Jul 8, 2004

Xae posted:

Update the Column, set =0 where col is null
Create a Before Insert Row Trigger
Add Constraint

Triggers ? For Default values ? God dammit this is a stupid idea.

This is what you're looking for:

code:

ALTER TABLE MyTable ADD DEFAULT (0) FOR MyCol
ALTER TABLE MyTable ALTER COLUMN MyCol int NOT NULL

Yes, if you INSERT into that table without specifying a value for MyCol, it will use your default value.

# ¿ May 27, 2008 23:58

Alex007: Jul 8, 2004

Xae posted:

What the gently caress do you think a default value is?

Default values are better implemented using, well, default values, not triggers.

# ¿ May 28, 2008 01:11

Alex007: Jul 8, 2004

chocojosh posted:

At my work we have a C#/ASP.NET application that all use the same database on one development server.

When I go to use the profiler to see the parameters of a stored procedure that is called ("New Trace"), all the developers' stored procs are shown. It becomes annoying sometimes to figure out which

There's an NTUserName column that lists the user who is calling the database command. Is there any way to have it work through the application based on the user's windows login? I.e. If I'm logged in to windows as jk, run the C#/ASP.NET application off localhost and then call a stored procedure through a database connection, is there any way to specify the NTUserName column that will appear in the trace?

On my servers, the NTUSername isn't always filled, but I prefer to filter by SPID anyway. run sp_who2 to list the connections, decide which one you want to trace (you can see usernames and machine names here) and enter that SPID in the trace filter.

# ¿ Jun 4, 2008 15:58

Alex007: Jul 8, 2004

chocojosh posted:

sp_who2 returns *10* different results for my user. I think that it's making one different spid for each "New Query" tab I have opened in Sql server studio. Also, it seems that if I log out and log back into my application I have a different spid.

Is there a way to filter the results of sp_who2. Something similar to

SELECT * FROM "exec sp_who2" where
Login = mylogin
and hostname = myhostname

sp_who2 takes a parameter, the login name, run it like this:

EXEC sp_who2 'MYDOMAIN\MyUserName' (if you're using NT Auth)

# ¿ Jun 4, 2008 16:56

Alex007: Jul 8, 2004

I'm probably overthinking the solution to this problem so I'm posting here to get some fresh ideas.

I have a table:

code:

Table: BatchParams

BatchId   ParamId   Value
-----------------------------
     17         1   "Something"
     17         2   "2007"
     17         8   ""
...

For a given BatchId, I have to find all other batches with the exact same parameters. The number of parameters is unknown but will always be the same across all batches.

I'm currently joining the table with itself to find matching ParamId's and Value's with different BatchId's, but validating that all params are there is getting complex and I've been running in circles for the past hour trying to figure this one out.

Any pointers ?

# ¿ Sep 3, 2010 17:34

Adbot: ADBOT LOVES YOU

# ¿ Apr 28, 2024 17:36

Alex007: Jul 8, 2004

Sprawl posted:

What SQL you using?

MSSQL Server 2000.

EDIT: This is what I currently have, it seems to work, but for some reason I feel like I'm not doing it right at all.

"Batch" is the header table for "BatchParams", which only indicates the batch type (must match) and "BatchTypeParams" is used to decided which parameters must match (UsedInUnicity)

code:

DECLARE @BatchId int
SELECT @BatchId = 20

DECLARE @RequiredParamCount int

SELECT 
  @RequiredParamCount = COUNT(*)
FROM 
  Grn_Batch AS MyBatch
  INNER JOIN Grn_BatchParams AS MyBatchParams
  ON MyBatch.BatchId = MyBatchParams.BatchId
  INNER JOIN Grn_BatchTypeParams AS MyBatchTypeParams
  ON MyBatchParams.ParamId = MyBatchTypeParams.ParamId
  AND MyBatchTypeParams.UsedInUnicity = 1
WHERE
  MyBatch.BatchId = @BatchId  
GROUP BY 
  MyBatch.BatchId
    
SELECT 
  OtherBatches.BatchId
FROM 
  Grn_Batch AS MyBatch
  INNER JOIN Grn_BatchParams AS MyBatchParams
  ON MyBatch.BatchId = MyBatchParams.BatchId
  INNER JOIN Grn_BatchTypeParams AS MyBatchTypeParams
  ON MyBatchParams.ParamId = MyBatchTypeParams.ParamId
  AND MyBatchTypeParams.UsedInUnicity = 1
  
  INNER JOIN Grn_Batch AS OtherBatches
  ON MyBatch.BatchId <> OtherBatches.BatchId
  AND MyBatch.TypeId = OtherBatches.TypeId
  INNER JOIN Grn_BatchParams AS OtherBatchesParams
  ON OtherBatches.BatchId = OtherBatchesParams.BatchId
  AND MyBatchParams.ParamId = OtherBatchesParams.ParamId
  AND CAST(MyBatchParams.Value AS varchar(8000)) = CAST(OtherBatchesParams.Value AS varchar(8000))
WHERE
  MyBatch.BatchId = @BatchId
GROUP BY
  OtherBatches.BatchId  
HAVING 
  COUNT(*) = @RequiredParamCount

Alex007 fucked around with this message at 18:06 on Sep 3, 2010

# ¿ Sep 3, 2010 17:53

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'