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