Snitz Forums 2000 - Combine two SQL queries

Snitz Forums 2000

Username:	Password:
Save Password
Forgot your Password?

All Forums

Help Groups for Snitz Forums 2000 Users

Help: General / Classic ASP versions(v3.4.XX)

Combine two SQL queries

New Topic

Topic Locked

Printer Friendly

Author

Topic

Page: of 2

Podge
Support Moderator

Ireland
3776 Posts

Posted - 28 April 2005 : 15:45:15

Got casting working with sql server;

SELECT FORUM_TOPICS.T_SUBJECT, FORUM_MEMBERS.M_NAME, FORUM_TOPICS.T_DATE, (CAST(FORUM_TOPICS.TOPIC_ID as VARCHAR(20)) + '_0') as ART FROM FORUM_TOPICS, FORUM_MEMBERS WHERE (FORUM_MEMBERS.MEMBER_ID=FORUM_TOPICS.T_AUTHOR) AND FORUM_TOPICS.FORUM_ID=2 UNION SELECT ('Re: ' + FORUM_TOPICS.T_SUBJECT), FORUM_MEMBERS.M_NAME, FORUM_REPLY.R_DATE, (CAST(FORUM_REPLY.TOPIC_ID AS VARCHAR(20)) + '_' + CAST(FORUM_REPLY.REPLY_ID as varchar(20))) FROM FORUM_REPLY, FORUM_MEMBERS, FORUM_TOPICS WHERE (FORUM_MEMBERS.MEMBER_ID=FORUM_REPLY.R_AUTHOR) AND (FORUM_TOPICS.TOPIC_ID=FORUM_REPLY.TOPIC_ID) AND FORUM_TOPICS.FORUM_ID=2 Order By T_DATE

Podge.

The Hunger Site - Click to donate free food | My Blog | Snitz 3.4.05 AutoInstall (Beta!)

My Mods: CAPTCHA Mod | GateKeeper Mod
Tutorial: Enable subscriptions on your board

Warning: The post above or below may contain nuts.

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 28 April 2005 : 17:13:57

Hmmm... I don't think I can figure out exactly what you're wanting...

Snitz 3.4 Readme | Like the support? Support Snitz too

Podge
Support Moderator

Ireland
3776 Posts

Posted - 28 April 2005 : 19:47:46

Well its working quite well. Its for a mod I'm working on. I'll be close to having a beta version ready by the weekend.
If I can fix this next problem.

I've added FORUM_TOPICS.T_MESSAGE and FORUM_REPLY.R_MESSAGE but get this error from SQL Server. Don't know why.

The text, ntext, or image data type cannot be selected as DISTINCT.

I cannot find a DISTINCT in the following Sql Statement

SELECT FORUM_TOPICS.T_SUBJECT, FORUM_MEMBERS.M_NAME, FORUM_TOPICS.T_DATE, FORUM_TOPICS.T_MESSAGE, (CAST(FORUM_TOPICS.TOPIC_ID as VARCHAR(20)) + '_0') as ART FROM FORUM_TOPICS, FORUM_MEMBERS WHERE (FORUM_MEMBERS.MEMBER_ID=FORUM_TOPICS.T_AUTHOR) AND FORUM_TOPICS.FORUM_ID=2 UNION SELECT ('Re: ' + FORUM_TOPICS.T_SUBJECT), FORUM_MEMBERS.M_NAME, FORUM_REPLY.R_DATE, FORUM_REPLY.R_MESSAGE, (CAST(FORUM_REPLY.TOPIC_ID AS VARCHAR(20)) + '_' + CAST(FORUM_REPLY.REPLY_ID as varchar(20))) FROM FORUM_REPLY, FORUM_MEMBERS, FORUM_TOPICS WHERE (FORUM_MEMBERS.MEMBER_ID=FORUM_REPLY.R_AUTHOR) AND (FORUM_TOPICS.TOPIC_ID=FORUM_REPLY.TOPIC_ID) AND FORUM_TOPICS.FORUM_ID=2 Order By T_DATE

By the way. Its really fast compared to the other way I was doing it in spite of the complexity of the query.

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 28 April 2005 : 19:58:59

The DISTINCT is somehow implicit when you use UNION, since duplicate rows are removed from the joined recordset. To avoid this, use UNION ALL, instead of UNION:

SELECT FORUM_TOPICS.T_SUBJECT, FORUM_MEMBERS.M_NAME, FORUM_TOPICS.T_DATE, FORUM_TOPICS.T_MESSAGE, (CAST(FORUM_TOPICS.TOPIC_ID as VARCHAR(20)) + '_0') as ART FROM FORUM_TOPICS, FORUM_MEMBERS WHERE (FORUM_MEMBERS.MEMBER_ID=FORUM_TOPICS.T_AUTHOR) AND FORUM_TOPICS.FORUM_ID=2 UNION ALL SELECT ('Re: ' + FORUM_TOPICS.T_SUBJECT), FORUM_MEMBERS.M_NAME, FORUM_REPLY.R_DATE, FORUM_REPLY.R_MESSAGE, (CAST(FORUM_REPLY.TOPIC_ID AS VARCHAR(20)) + '_' + CAST(FORUM_REPLY.REPLY_ID as varchar(20))) FROM FORUM_REPLY, FORUM_MEMBERS, FORUM_TOPICS WHERE (FORUM_MEMBERS.MEMBER_ID=FORUM_REPLY.R_AUTHOR) AND (FORUM_TOPICS.TOPIC_ID=FORUM_REPLY.TOPIC_ID) AND FORUM_TOPICS.FORUM_ID=2 Order By T_DATE

Using UNIONS compares favorably speedwise with queries with outer joins. My mod to speed up SQL Server Snitz searching does it exactly this way.

Snitz 3.4 Readme | Like the support? Support Snitz too

Edited by - ruirib on 28 April 2005 20:00:09

Podge
Support Moderator

Ireland
3776 Posts

Posted - 28 April 2005 : 20:11:20

It works. The speed increase really is noticable.

One last thing.

When I'm retrieving a post after I have the unique post number e.g. 18_45 could I use something like this;

SELECT FORUM_TOPICS.T_SUBJECT, FORUM_MEMBERS.M_NAME, FORUM_TOPICS.T_DATE, FORUM_TOPICS.T_MESSAGE, (CAST(FORUM_TOPICS.TOPIC_ID as VARCHAR(20)) + '_0') as ART FROM FORUM_TOPICS, FORUM_MEMBERS WHERE (FORUM_MEMBERS.MEMBER_ID=FORUM_TOPICS.T_AUTHOR) AND FORUM_TOPICS.FORUM_ID=2 UNION ALL SELECT ('Re: ' + FORUM_TOPICS.T_SUBJECT), FORUM_MEMBERS.M_NAME, FORUM_REPLY.R_DATE, FORUM_REPLY.R_MESSAGE, (CAST(FORUM_REPLY.TOPIC_ID AS VARCHAR(20)) + '_' + CAST(FORUM_REPLY.REPLY_ID as varchar(20))) as ART FROM FORUM_REPLY, FORUM_MEMBERS, FORUM_TOPICS WHERE ((FORUM_MEMBERS.MEMBER_ID=FORUM_REPLY.R_AUTHOR) AND (FORUM_TOPICS.TOPIC_ID=FORUM_REPLY.TOPIC_ID) AND FORUM_TOPICS.FORUM_ID=2) and ART = '18_45' Order By T_DATE

It would save me iterating through each record to find it and probably some kb's of memory too.

Edited by - Podge on 28 April 2005 20:12:10

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 29 April 2005 : 03:50:42

Still not sure what you mean, but you can't use an alias for a calculated value in a WHERE clause. You can use the expression to calculate the value instead. So, just repeat the expression for ART in the WHERE clause and test its value.

Snitz 3.4 Readme | Like the support? Support Snitz too

Edited by - ruirib on 29 April 2005 03:51:55

Podge
Support Moderator

Ireland
3776 Posts

Posted - 29 April 2005 : 04:38:16

Like this? (I can't test it for a while, I'm at work)

SELECT FORUM_TOPICS.T_SUBJECT, FORUM_MEMBERS.M_NAME, FORUM_TOPICS.T_DATE, FORUM_TOPICS.T_MESSAGE, (CAST(FORUM_TOPICS.TOPIC_ID as VARCHAR(20)) + '_0') as ART FROM FORUM_TOPICS, FORUM_MEMBERS WHERE (FORUM_MEMBERS.MEMBER_ID=FORUM_TOPICS.T_AUTHOR) AND FORUM_TOPICS.FORUM_ID=2 UNION ALL SELECT ('Re: ' + FORUM_TOPICS.T_SUBJECT), FORUM_MEMBERS.M_NAME, FORUM_REPLY.R_DATE, FORUM_REPLY.R_MESSAGE, (CAST(FORUM_REPLY.TOPIC_ID AS VARCHAR(20)) + '_' + CAST(FORUM_REPLY.REPLY_ID as varchar(20))) as ART FROM FORUM_REPLY, FORUM_MEMBERS, FORUM_TOPICS WHERE ((FORUM_MEMBERS.MEMBER_ID=FORUM_REPLY.R_AUTHOR) AND (FORUM_TOPICS.TOPIC_ID=FORUM_REPLY.TOPIC_ID) AND FORUM_TOPICS.FORUM_ID=2) and ((CAST(FORUM_TOPICS.TOPIC_ID as VARCHAR(20)) + '_0') = '18_45' or ((CAST(FORUM_REPLY.TOPIC_ID AS VARCHAR(20)) + '_' + CAST(FORUM_REPLY.REPLY_ID as varchar(20))) ='18_45')) Order By T_DATE

I have to say, I've learned a lot from this thread. Is there any chance we could have a forum dedicated to sql queries (forum & non-forum related)?

Edited by - Podge on 29 April 2005 07:32:03

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 29 April 2005 : 07:07:31

Yeah, but you're missing a closing ' at the last 18_45 and a closing parenthesis as well.

Snitz 3.4 Readme | Like the support? Support Snitz too

Podge
Support Moderator

Ireland
3776 Posts

Posted - 29 April 2005 : 07:36:11

I corrected it.

All I need to do now is write a function to convert dates to a specific format and a few other minor things and I'll have a beta version ready so you can see where all your hard work is contributing to.

Podge
Support Moderator

Ireland
3776 Posts

Posted - 05 July 2005 : 03:57:34

quote:
Using UNIONS compares favorably speedwise with queries with outer joins. My mod to speed up SQL Server Snitz searching does it exactly this way.

Ruirib, is this mod available for download anywhere?