April 2, 2003 at 5:55 pm
I'm implementing a site about Contracts, and everything around (laws, Court decisions, Monographies about, etc). The SQL database has (up to now) 7 tables.
In the Homepage, I want to put an option for poping-up a window where the main news, added (each day) to the site, will be "announced", inviting the Visitors to the page with their full story.
All tables have fields for the date when I'll be INSERTing each record.
And more: all tables have a field -- naturally called "remark" -- where I can mark-or-not-mark if each respective record should be "announced" in that pop-up window in the Homepage, or not. The method is simple: if I want to "announce" that specific record, I fill "1" in that field, if not, I leave it blank. In all the tables, that field has the same name: "remark".
The problem is that, when trying to use JOINS to SELECT some fileds of two (or more) tables to work together, using WHERE to make the relation through the field "remark" filled with "1", ORDER BY the date-of-inclusion DESC,
The output looks haunted: some "remarked" records appear, others don't, some *not* "remarked" also appear, some are repeated...
I did not find any code that could put things in order! Would You be kind in giving me some help?
Many thanks in advance!
Dalton
April 3, 2003 at 12:06 am
Rather than using a join, try a UNION:
select <column_name> as output_column
from table 1
where remark = 1
and date >= @datevalue
union
select <column_name> as output column
from table 2
where remark = 2
and date >= @datevalue
etc
This will get all the rows from table 1 and all rows from table 2 and so on. Just make sure that each select statement outputs the same column names in the same order.
Jeremy
April 3, 2003 at 4:42 am
Jeremy
Thanks for your help, but, before I try to implement it, please give me a 2nd help:
- Each table, naturally, has different goals, so they have different contents, and (almost all) different columns. Besides the common subject on "Contracts", there are only 02 "common" specifications: (1) for each table I put a date-of-inclusion of each record in the table; and (2) each table has a "remark" field to inform SQL-ASP if that record will-or-not be "announced" in the said pop-up window in the Homepage.
So, please explain me what You mean by "make sure that each select statement outputs the same column names in the same order" -- more specifically: "the same column names"!
* MORE: excuse me, but I forgot saying I'll pick just the latest 03 (for instance) records of each: so, I must put "SELECT top 3.....", isn't it?
* AND MORE, AGAIN: is it possible to pick (for instance) the 03 more recent records from TABLE1 - and - the 02 more recent records from TABLE2?
Thanks and thanks again for your kind help.
Dalton
April 3, 2003 at 4:55 am
So, please explain me what You mean by "make sure that each select statement outputs the same column names in the same order" -- more specifically: "the same column names"!
I have amended my code to show
select top n <table1_col1> as common_col1, <table1_col2> as common_col2, <table1_col3> as common_col3
from table 1
where remark1 = 1
and datefield1 >= @datevalue
order by date_field1 desc
union
select top n <table2_col1> as common_col1, <table2_col2> as common_col2, <table2_col3> from table 2
where remark2 = 1
and datefield2 >= @datevalue
order by date_field2 desc
A UNION needs the results of each select statement to be the same. If you don't have the same column names in the different tables, you can use the AS operator in the select statement to create a common set of output column names.
Each of the select statements is independent so you can select top 3 from table1, 5 from table 2, 4 from table 3 etc. You could also use different where clauses on different tables to select a different set of criteria.
Jeremy
April 3, 2003 at 4:59 am
Just another thought, why bother with a single SQL statement anyway? Why not have a separate query for each table? There will be a small performance overhead in executing the separate queries but if you resuse the ADO connection then it is a very small overhead.
Jeremy
April 3, 2003 at 6:36 am
Dear Jeremy
...as a matter of fact, I had the same feeling, indeed: why complicate a so simple process, of SELECTing data -- instead of putting each type (recent laws, recent articles, etc) in separate-and-more relevant, showed-up headings?!
Then, the problem will be: We shall not have to repeat the same openDB-configurate-closeDB process for each table, isn't it?
- WHAT part of the normal process will be "common" -- that's to say: WHICH settings, or commands, will no be repeated (I suppose the communication with the DB will remain open, just the opening-SELECTing-[response writing]-and-closing each table is what will be repeated)!
...Am I right? Is it that simple? HOW will be the congifuration?
Thanks once more, since now!
Dalton
April 3, 2003 at 6:47 am
Dalton,
What I do in my ASP scripts is to open the database connection at the top of the page, execute as many sql statements as I need for the page and then close it at the end of the script:
open connection
source = "exec procedure parm1a, parm1b, parm1c"
source.open
reponse.write statements
source.close
more html
source = "exec procedure2 parm2a, parm2b "
source.open
reponse.write statements
source.close
more html if necessaary
etc
close connection
Hope this helps
Jeremy
April 3, 2003 at 7:02 am
Here's a sample way to normalize your structure:
CREATE TABLE ArticleType
(
Code CHAR(2) NOT NULL
, Description VARCHAR(100) NOT NULL
)
CREATE TABLE Article
(
ID INT NOT NULL IDENTITY(1,1)
, Type CHAR(2) NOT NULL
, DateIncluded SMALLDATETIME NOT NULL
, ShowInPopup BIT NOT NULL DEFAULT(0)
, Headerline VARCHAR(100) NOT NULL
, Content TEXT NOT NULL
)
INSERT INTO ArticleType ('LW','Laws')
INSERT INTO ArticleType ('CD','Court Decisions')
/* Keep going here... */
Once you've gotten your articles in this type of normalized structure, you can write SQL such as:
SELECT Headline, Content
FROM Article
WHERE DateIncluded > GETDATE() - 1
AND ShowInPopup = 1
And Type = 'CD'
or...
UPDATE Article
SET ShowInPopup = 0
WHERE DateIncluded <= GETDATE() - 2
I hope this gives you some ideas...
🙂
Jay
April 3, 2003 at 7:56 am
Jeremy, Jay
Wow!, I'll (try to) implement it: and hope I'll succeed, although -- after reading your advices -- I'm becoming even more dummy than I was!
Thanks even once more, since now -- I'll return You my feedback.
Dalton
April 6, 2003 at 6:03 am
Dear Jeremy, Jay
Heureka! Fiat lux! Indeed, "the great ideas ...are simple!" Simple, and efficient:
1) open DB connection
2) open - write - close eachever table
3) close DB connection.
Aslthough I am not but a dummy-self-made-programmer, the performance I got, from your advice, is not poor then the best expertise-code should do!!!
Thanks many times more, for your kind attention, help, and patience!
Dalton
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply