filter data from multiple tables crashes app!

i didnt think my sql qeury was that complicated that it would crash my web
app. all im trying to do is filter data between two tables. heres my query

<cfquery name="GetResults" datasource="#datasource#">
SELECT *
FROM Content, Content_Sites
WHERE Content.ContentID <> Content_Sites.ContentID
ORDER BY Content.ContentID DESC
</cfquery
equals works, but when i try not equals, it all goes haywire. any ideas?

TIAjonezy (jonezy@.donotmailmejonezy.com) writes:
> i didnt think my sql qeury was that complicated that it would crash my
> web app. all im trying to do is filter data between two tables. heres
> my query
><cfquery name="GetResults" datasource="#datasource#">
> SELECT *
> FROM Content, Content_Sites
> WHERE Content.ContentID <> Content_Sites.ContentID
> ORDER BY Content.ContentID DESC
></cfquery>
> equals works, but when i try not equals, it all goes haywire. any ideas?

Yes and no. Since I don't know your tables, and neither know what you
are trying to achieve, how could I really have any ideas?

But, OK, having seen people using <> in the wrong place before, I can
make a guess. Say that both table have a thousand rows. You are now
asking for all million combinations of these two thousand rows - save
those that have the same ID.

I guess what you are looking for is really something like:

SELECT *
FROM Content c ,
WHERE NOT EXISTS (SELECT *
FROM Content_Sites cs
WHERE c.ContentID = cs.ContentID)
ORDER BY c.ContentID DESC

That is, list all Content that does not have any content site.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <vl786sjaijmr91@.corp.supernews.com>,
jonezy@.donotmailmejonezy.com says...
> i didnt think my sql qeury was that complicated that it would crash my web
> app. all im trying to do is filter data between two tables. heres my query
> <cfquery name="GetResults" datasource="#datasource#">
> SELECT *
> FROM Content, Content_Sites
> WHERE Content.ContentID <> Content_Sites.ContentID
> ORDER BY Content.ContentID DESC
> </cfquery>
> equals works, but when i try not equals, it all goes haywire. any ideas?

How big are the tables? You DO realize you've asked for a
cross product? That means the database is returning the
ENTIRE contents (minus one row) of the Content_Sites table
for each ROW of the Content table.

Assuming 500 rows in Content_Sites and 1000 rows in
Content, you are getting back 500,000 rows in your query.

--
Cam|||thanks.., dunno how i overlookd NOT EXISTS.

i also realized i forgot to include the table connection between content and
content_sites. guess i was in a hurry.

thanks again!

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93E9E8B075BFAYazorman@.127.0.0.1...
> jonezy (jonezy@.donotmailmejonezy.com) writes:
> > i didnt think my sql qeury was that complicated that it would crash my
> > web app. all im trying to do is filter data between two tables. heres
> > my query
> ><cfquery name="GetResults" datasource="#datasource#">
> > SELECT *
> > FROM Content, Content_Sites
> > WHERE Content.ContentID <> Content_Sites.ContentID
> > ORDER BY Content.ContentID DESC
> ></cfquery>
> > equals works, but when i try not equals, it all goes haywire. any
ideas?
> Yes and no. Since I don't know your tables, and neither know what you
> are trying to achieve, how could I really have any ideas?
> But, OK, having seen people using <> in the wrong place before, I can
> make a guess. Say that both table have a thousand rows. You are now
> asking for all million combinations of these two thousand rows - save
> those that have the same ID.
> I guess what you are looking for is really something like:
> SELECT *
> FROM Content c ,
> WHERE NOT EXISTS (SELECT *
> FROM Content_Sites cs
> WHERE c.ContentID = cs.ContentID)
> ORDER BY c.ContentID DESC
> That is, list all Content that does not have any content site.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.aspsql

fileLen function in stored procedure

Heres an extract of a stored procedure creating a column.
Path = FileLen([CacheServers].[CachePath]+
(left([DOCUMENT]. [PHYSICAL_DOC_GUID],6))+''''+[DOCUMENT]
.[PHYSICAL_DOC_GUID]+[DOCUMENT].[FileType])
Reult:
\\comp-ap- 70c\Imxxs$\data\docs\70393C\70393CE0EC6D
11D8BB64000D568A4637.tif
The above is a file path of an image stored on the SAN server. The code
above works perfectly fine in MS Access but not in SQL server 2K
It should return the size in bytes of the file.
Books online state the VBA function fileLen() works in SQL Analyser,
however, when I execute the stored procedure I receive an error message
"FileLen is not a recognised function name".
I cant find a thing on the Microsoft Tech communities the specifically
relates to calling VBA functions in SQL.
Any ideas?
Learning SQL and AccessIs there something wrong with the LEN function? Or DATALENGTH?
MC
"sebastian stephenson" <sebastianstephenson@.discussions.microsoft.com> wrote
in message news:E9F88AB0-05A3-4BE6-9EB0-6314A7685359@.microsoft.com...
> Heres an extract of a stored procedure creating a column.
> Path = FileLen([CacheServers].[CachePath]+
> (left([DOCUMENT]. [PHYSICAL_DOC_GUID],6))+''''+[DOCUMENT]
.[PHYSICAL_DOC_GUID]+[DOCUMENT].[FileType])
> Reult:
> \\comp-ap- 70c\Imxxs$\data\docs\70393C\70393CE0EC6D
11D8BB64000D568A4637.tif
> The above is a file path of an image stored on the SAN server. The code
> above works perfectly fine in MS Access but not in SQL server 2K
> It should return the size in bytes of the file.
> Books online state the VBA function fileLen() works in SQL Analyser,
> however, when I execute the stored procedure I receive an error message
> "FileLen is not a recognised function name".
> I cant find a thing on the Microsoft Tech communities the specifically
> relates to calling VBA functions in SQL.
> Any ideas?
> --
> Learning SQL and Access|||> Books online state the VBA function fileLen() works in SQL Analyser,
> however, when I execute the stored procedure I receive an error message
Where does it say that?
If you really need to check for file sizes from T-SQL you should look at the
sp_OA* system procedures. I'd suggest using an appropriate client applicatio
n
to supply the values to the server.
ML
http://milambda.blogspot.com/|||I obviously didnt understand you correctly. Is SQL Server 2005 an option?
You could use CLR for something like this.
MC
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:u%23FgSFLVGHA.524@.TK2MSFTNGP10.phx.gbl...
> Is there something wrong with the LEN function? Or DATALENGTH?
>
> MC
>
> "sebastian stephenson" <sebastianstephenson@.discussions.microsoft.com>
> wrote in message
> news:E9F88AB0-05A3-4BE6-9EB0-6314A7685359@.microsoft.com...
>

Filegroups and RAID 1+0

Here's a question I hadn't encountered before: Does SQL Server populate and
utilize files in a filegroup sequentially; i.e., utilizing only the first
file until it is full, then moving on to the next file, or does it use any
sort of "storage-balancing" or striping algorithms? In other words, if I
create a filegroup for my database with 3 files on 3 separate hard drives,
(i.e., "C:\Data1.mdf", "D:\Data2.ndf", "E:\Data3.ndf"), will SQL Server fill
up Data1.mdf before ever using Data2.ndf, and will it fill up Data2.ndf
before utilizing Data3.ndf?
Thanks,
Michael C#
http://msdn.microsoft.com/library/en...es_02_2ak3.asp
David Portas
SQL Server MVP
|||Thank you!
Michael C#
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1107190503.761891.194640@.z14g2000cwz.googlegr oups.com...
> http://msdn.microsoft.com/library/en...es_02_2ak3.asp
> --
> David Portas
> SQL Server MVP
> --
>