[quoted text, click to view]

If you are in control of this database, start by at least putting it in
first normal form. The whole idea of Comma-separated fields is really,
really, really bad. Hopefully you now see this.

You will have to build the age string like so:
cast(@age-3 as varchar(2)) + ',' +
cast(@age-2 as varchar(2)) + ',' +
cast(@age-1 as varchar(2)) + ',' +
cast(@age as varchar(2)) + ',' +
cast(@age+1 as varchar(2)) + ',' +
cast(@age+2 as varchar(2)) + ',' +
cast(@age+3 as varchar(2))

I have following query to update database. But it's not complete.

1.) If FORUM_MEMBERS.M_AGE value is empty that user will be added to all forums's access list. Big problem. =)

2.) If user is exist in FORUM_ALLOWED_MEMBERS table, this query duplicates FORUM_ID for that MEMBER_ID. Another
problem.

3.) I want if M_AGE is (ie) 10, that user should see forums where M_SUBJECTs are 7,8,9,10,11,12,13 (I mean +3
and -3)

4.) This query should be processed for members that registered after a special date.

If u can help about one of these, thank you in advance!..



INSERT INTO

FORUM_ALLOWED_MEMBERS

(

Member_ID,

Forum_ID

)

SELECT

FORUM_MEMBERS.Member_ID,

FORUM_FORUM.Forum_ID

FROM

FORUM_MEMBERS

INNER JOIN

FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_AGE + '%'

[quoted text, click to view]
(snip)

Hi LacOniC,

Neither is your description of the problem. The best way to explain what
yoou want is to post:
* Table structere, as CREATE TABLE statements,
* Sample data, as INSERT statements,
* Expected results,
* And a description of the business problem you're trying to solve.

www.aspfaq.com/5006

Best, Hugo
--

I used commas to tell my issue. Don't know to do with comma or without comma. =) Some too newbie. I started
to read about T-SQL today. I'll work on them. But i need this code, urgent. So asking here. Especially about
those 2 problems:

1.) If FORUM_MEMBERS.M_AGE value is empty that user will be added to all forums's access list. Big problem. =)

2.) If user is exist in FORUM_ALLOWED_MEMBERS table, this query duplicates FORUM_ID for that MEMBER_ID. Another
problem.

[quoted text, click to view]

Hi Laconic,

After reading your messages, I'm afraind I don't really understand the
data that's in your tables and the results you try to achieve. Could you
please post:

1. The structure of your tables, as CREATE TABLE statements (including
all constraints and properties, though irrelevant columns may be
excluded).

2. Some illustrative rows of sample data, as INSERT statement (no need
to post thousands of rows, just enough to illustrate your problem).

3. The results you expect to get from the sample data posted.

4. A description of the business problem you're trying to solve.

Once you post that information, it'll be a lot easier to help you!

Best, Hugo
--

This is a forum (snitz) database. Ready one so i just edit it. My question was about tree tables of it:

Members -> MemberID, M_AGE
AllowedMembers -> MemberID, ForumID
Forums -> ForumID, F_SUBJECT

M_AGE values are between 100 and 200, F_SUBJECT values are like "100. BlaBla", "122. BlaBla"

I found answer for 1. and 2. issues, thanks to Terri from asp.net. 3 and 4 is still here, you can help about
them:

3.) I want if M_AGE is (ie) 150, that user should see forums where M_SUBJECTs are 147,148,149,150,151,152,153
(I mean +3
and -3)

4.) This query should be processed for members that registered after a special date.

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
||

INSERT INTO FORUM_ALLOWED_MEMBERS

(
Member_ID,

Forum_ID
)

SELECT

FORUM_MEMBERS.Member_ID, FORUM_FORUM.Forum_ID FROM FORUM_MEMBERS

INNER JOIN

FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_AGE + '%'

WHERE ISNULL(FORUM_MEMBERS.M_AGE,'') <> '' AND

NOT EXISTS(SELECT NULL FROM FORUM_ALLOWED_MEMBERS A

WHERE A.Member_ID = FORUM_MEMBERS.Member_ID AND

A.Forum_ID = FORUM_FORUM.Forum_ID)

|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
||

[quoted text, click to view]

Hi Laconic,

You didn't post the CREATE TABLE statements I requested, so I still
don't know what datatypes you use and what constraints you have.
You didn't post the INSERT statements I requested, so I still have no
idea what your data actually looks like.
You didn't post the requested output, so I can still only guess what
your narrative means - language has a tendency to be less than univocal.
You didn't give enough background of your problem. I know what a forum
is, but I don't know what "snitz" means, nor what the M_AGE column
represents. If the values are between 100 and 200, it can't be the
member's age, can it?
Also, the data in F_SUBJECT looks unnormalized. The rest of your message
suggests that the numeric part in the F-SUBJECT column has a rlevance of
it's own, therefor it should bhe in it's own column. Consider changing
this table to:

CREATE TABLE Forums
(ForumID int NOT NULL -- int is just a guess
,ForumNumber int NOT NULL
,ForumDescr varchar(40) NOT NULL -- change length as req'd
,F_SUBJECT AS CAST(ForumNumber AS char(3))
+ '.' + ForumDescr
,PRIMARY KEY (ForumID)
,CHECK (ForumNumer BETWEEN 100 AND 200)
)

I included F_SUBJECT as computed column, so that your existing code
won't stop working. You will need to change the insert and update
commands, though!


[quoted text, click to view]

M_SUBJECT? I suppose you mean F_SUBJECT? And I also suppose that you
want to check only the numeric part of F_SUBJECT (the first three
characters) agains M_AGE?

With the design I suggest above, this is simple:
WHERE ForumNumber BETWEEN M_AGE - 3 AND M_AGE + 3

With your original design, this gets a bit harder (and slower, since the
expression will ruin the usability of indexes)
WHERE CAST(LEFT(ForumNumber, 3) AS int) BETWEEN M_AGE - 3 AND M_AGE + 3

Though you can regain some of the speedby reversing the expression:
WHERE M_AGE BETWEEN CAST(LEFT(ForumNumber, 3) AS int) - 3
AND CAST(LEFT(ForumNumber, 3) AS int) + 3

[quoted text, click to view]

Since there is no column in your design that holds the date registered,
there is no way that this requirement can be fulfilled.

Best, Hugo
--

Snitz means: http://forum.snitz.com So i can't do what you say especially creating table and columns.

I just wrote table and column names that is about my situation. Or there is more tables and columns. For
example Forum Table includes 28 columns, Members Table includes 48 columns.

Thank you for your message, very helpful to imagine what to do. I'll try them as soon as possible.

I tried some and always same convert error. I understood why do you ask for types. =)

FORUM_MEMBERS.M_MARSTATUS nvarchar (100)
FORUM_MEMBERS.M_MARSTATUS INT (4)
FORUM_FORUM.F_SUBJECT nvarchar (100)
FORUM_FORUM.FORUM_ID INT (4)
FORUM_ALLOWED_MEMBERS.MEMBER_ID INT (4)
FORUM_ALLOWED_MEMBERS.FORUM_ID INT (4)

For example when i add this under exact code:

EXISTS (SELECT FORUM_MEMBERS.M_MARSTATUS WHERE M_MARSTATUS BETWEEN LEFT(FORUM_FORUM.F_SUBJECT,3)+3 AND
LEFT(FORUM_FORUM.F_SUBJECT,3)-3)

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value 'Gen' to a column of data type int.

Line 1 is: INSERT INTO FORUM_ALLOWED_MEMBERS

[quoted text, click to view]

Hi Laconic,

[quoted text, click to view]

I think you made an error here - the same column can't have two
different datatypes.


[quoted text, click to view]

That suggests that you have at least one forum that doesn't follow the
naming pattern outlined in your previous post (nnn: xxxxx, where nnn is
a three-digit numeric and xxxxx is a textual description). Maybe you've
got a forum called "General questions" (or anything else that starts
with "Gen")?

The query will have to be extended with special treatment for Forum
subjects that don't start with a number. How to change it depends on
whether you want these Forums to be included or excluded from the
results.

Best, Hugo
--

[quoted text, click to view]

(snip)
[quoted text, click to view]

Hi Laconic,

In that case, try if this works for you:

WHERE M_MARSTATUS
BETWEEN WHEN FORUM_FORUM.F_SUBJECT LIKE '[0-9][0-9][0-9]%'
THEN LEFT(FORUM_FORUM.F_SUBJECT,3) - 3
ELSE M_MARSTATUS + 1
END
AND WHEN FORUM_FORUM.F_SUBJECT LIKE '[0-9][0-9][0-9]%'
THEN LEFT(FORUM_FORUM.F_SUBJECT,3) + 3
ELSE M_MARSTATUS - 1
END
(untested)

Quite ugly, eh? That's the unavoidable result of using unnormalised
data.

I've taken a quick peek at the Snitz site you refered to. I couldn't
find a description of all tables and columns in the short time I had
available for this. But seeing that you already use columns named Age
and MarStatus to store information completely unrelated to ages and
marital statuses, you could check out if you could abuse any of the
other unused columns to store the forum's starting number seperately.
That would make the above query lots easier.

Also, I noticed that Snitz is distributed under the GPL. I couldn't find
any indication if it's open source as well. If it is, then you could
just download the source, add all extra columns you need, change the
names of columns to indicate what you do store in them (since one day,
you'll get back to your design and find that you have forgotten what
actual data you stored in the Age and MarStatus columns - and you'll be
unable to maintain your own code!)

Best, Hugo
--

Hi Hugo,

[quoted text, click to view]

My typing mistake (copy/paste), one of them is FORUM_MEMBERS.MEMBER_ID INT (4)

[quoted text, click to view]

Yes. I have forums those subjects don't start with numbers. (i.e. "Genel")

[quoted text, click to view]

I just want to include forums those start with numbers. Because others such as Genel don't have a "access
list" already.

Hi;

Server: Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'WHEN'.

That Part of Query:

BETWEEN WHEN FORUM_FORUM.F_SUBJECT LIKE '[0-9][0-9][0-9]%'

Whole Query That I Try:

INSERT INTO FORUM_ALLOWED_MEMBERS

(
Member_ID,

Forum_ID
)

SELECT

FORUM_MEMBERS.Member_ID, FORUM_FORUM.Forum_ID FROM FORUM_MEMBERS

INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_MARSTATUS + '%'

WHERE ISNULL(FORUM_MEMBERS.M_MARSTATUS,'') <> '' AND

NOT EXISTS(SELECT NULL FROM FORUM_ALLOWED_MEMBERS A

WHERE A.Member_ID = FORUM_MEMBERS.Member_ID AND

A.Forum_ID = FORUM_FORUM.Forum_ID) AND

EXISTS (SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE = 20050322000000) AND

EXISTS (SELECT FORUM_MEMBERS.M_MARSTATUS WHERE M_MARSTATUS
BETWEEN WHEN FORUM_FORUM.F_SUBJECT LIKE '[0-9][0-9][0-9]%'
THEN LEFT(FORUM_FORUM.F_SUBJECT,3) - 3
ELSE M_MARSTATUS + 1
END
AND WHEN FORUM_FORUM.F_SUBJECT LIKE '[0-9][0-9][0-9]%'
THEN LEFT(FORUM_FORUM.F_SUBJECT,3) + 3
ELSE M_MARSTATUS - 1
END)

[quoted text, click to view]
(snip)

Sorry, my fault. I forgot to include the CASE keyword before WHEN.

Best, Hugo
--

Well, it worked with CASE without error but it do nothing different. =) Just add one user to one forum's
access list as before.

---

INSERT INTO FORUM_ALLOWED_MEMBERS

(
Member_ID,

Forum_ID
)

SELECT

FORUM_MEMBERS.Member_ID, FORUM_FORUM.Forum_ID FROM FORUM_MEMBERS

INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_MARSTATUS + '%'

WHERE ISNULL(FORUM_MEMBERS.M_MARSTATUS,'') <> '' AND

NOT EXISTS(SELECT NULL FROM FORUM_ALLOWED_MEMBERS A

WHERE A.Member_ID = FORUM_MEMBERS.Member_ID AND

A.Forum_ID = FORUM_FORUM.Forum_ID) AND

EXISTS (SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE >= 20050327000000) AND

EXISTS (SELECT FORUM_MEMBERS.M_MARSTATUS WHERE M_MARSTATUS
BETWEEN CASE WHEN FORUM_FORUM.F_SUBJECT LIKE '[0-9][0-9][0-9]%'
THEN LEFT(FORUM_FORUM.F_SUBJECT,3) - 3
ELSE M_MARSTATUS + 1
END
AND CASE WHEN FORUM_FORUM.F_SUBJECT LIKE '[0-9][0-9][0-9]%'
THEN LEFT(FORUM_FORUM.F_SUBJECT,3) + 3
ELSE M_MARSTATUS - 1
END)

Found it. What a happiness. =D Thank you very much Hugo for your interest. Maybe no solution here but I
learned more by you. =)

---

REPLACE(LEFT(FORUM_FORUM.F_SUBJECT, CHARINDEX('.',FORUM_FORUM.F_SUBJECT)),'.','')

BETWEEN FORUM_MEMBERS.M_MARSTATUS-3 AND FORUM_MEMBERS.M_MARSTATUS+3

---

WHOLE CODE:

INSERT INTO FORUM_ALLOWED_MEMBERS

(
Member_ID,

Forum_ID
)

SELECT

FORUM_MEMBERS.Member_ID, FORUM_FORUM.Forum_ID FROM FORUM_MEMBERS

INNER JOIN FORUM_FORUM ON REPLACE(LEFT(FORUM_FORUM.F_SUBJECT, CHARINDEX('.',FORUM_FORUM.F_SUBJECT)),'.','')

BETWEEN FORUM_MEMBERS.M_MARSTATUS-3 AND FORUM_MEMBERS.M_MARSTATUS+3

WHERE ISNULL(FORUM_MEMBERS.M_MARSTATUS,'') <> '' AND

NOT EXISTS(SELECT NULL FROM FORUM_ALLOWED_MEMBERS A

WHERE A.Member_ID = FORUM_MEMBERS.Member_ID AND

A.Forum_ID = FORUM_FORUM.Forum_ID) AND

EXISTS (SELECT FORUM_MEMBERS.M_DATE WHERE M_DATE >= 20050322000000)