filter data from multiple tables crashes app!

[jonezy]

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="GetResult s" datasource="#da tasource#">
SELECT *
FROM Content, Content_Sites
WHERE Content.Content ID <> Content_Sites.C ontentID
ORDER BY Content.Content ID DESC
</cfquery>

equals works, but when i try not equals, it all goes haywire. any ideas?

TIA

[Erland Sommarskog]

jonezy (jo****@donotma ilmejonezy.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="GetResult s" datasource="#da tasource#">
SELECT *
FROM Content, Content_Sites
WHERE Content.Content ID <> Content_Sites.C ontentID
ORDER BY Content.Content ID 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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Cam Penner]

In article <vl************ @corp.supernews .com>,
jo****@donotmai lmejonezy.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="GetResult s" datasource="#da tasource#">
SELECT *
FROM Content, Content_Sites
WHERE Content.Content ID <> Content_Sites.C ontentID
ORDER BY Content.Content ID 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

[jonezy]

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" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...

jonezy (jo****@donotma ilmejonezy.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="GetResult s" datasource="#da tasource#">
SELECT *
FROM Content, Content_Sites
WHERE Content.Content ID <> Content_Sites.C ontentID
ORDER BY Content.Content ID 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, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp