Newbie's Last Question

Current code is below:
||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||
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_M
ARSTATUS + '%'
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)
||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||
F_SUBJECT Values = ... "147. BlaBla" "148. BlaBla" "149. BlaBla" "150. BlaBl
a" .......
F_MARSTATUS Values = ... "147" "148" 149" "150" "151" "152" "153" ........
Current code add members to forum which subject is like member's m_marstatus
value.
How to add members to forums which subject contains m_marstatus value's -3 a
nd +3.
For example M_MARSTATUS = 150, member will be allowed by forums between "147
. BlaBla" and "153. BlaBla"
Can i use 7 different inner joins to do thissuch as:
INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_M
ARSTATUS + '%'
INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_M
ARSTATUS -3 + '%'
INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M_M
ARSTATUS -2 + '%'
etc.I'm sorry to say that you are now experiencing the difficulties of coding
against a poorly designed database schema (structure) . The first thing to
do would be to redesign the tables so they are at least in "3rd Normal form"
.
If you don't know what that means, read up on it a bt...
Your immediate problem is caused by having violated one of the first rules
of database normalization, that is of putting more than one piece of
information into a single column. Each attribute, or aspect about a "thing"
should be in it's own column. You have Marital Status, and Subject mixed
together in one column. Then, you also have multiple individual data
elements of the SAME type in that single column. If you fix these issues yo
u
will find the coding problem you are facing will just go away.
"LacOniC" wrote:

> Current code is below:
> ||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||
> 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)
> ||||||||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||||||||||
||||||||||||||||||||||||||
> F_SUBJECT Values = ... "147. BlaBla" "148. BlaBla" "149. BlaBla" "150. Bla
Bla" .......
> F_MARSTATUS Values = ... "147" "148" 149" "150" "151" "152" "153" ......
.
> Current code add members to forum which subject is like member's m_marstat
us value.
> How to add members to forums which subject contains m_marstatus value's -3
and +3.
> For example M_MARSTATUS = 150, member will be allowed by forums between "1
47. BlaBla" and "153. BlaBla"
> Can i use 7 different inner joins to do thissuch as:
> INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M
_MARSTATUS + '%'
> INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M
_MARSTATUS -3 + '%'
> INNER JOIN FORUM_FORUM ON FORUM_FORUM.F_SUBJECT LIKE '%' + FORUM_MEMBERS.M
_MARSTATUS -2 + '%'
> etc.
>
>|||This is Snitz Forum Database. If i edit database structure, i get more probl
ems. (For example when i update
forum version)
"You have Marital Status, and Subject mixed together in one column. "
Didn't understand this one. They are in different tables and columns.
Thank you.|||In your first post, you said...
F_SUBJECT Values = ... "147. BlaBla" "148. BlaBla" "149. BlaBla" "150.
BlaBla"
I assumed the numeric values are Marital Status, and the "BlaBla" (based on
the column name "F_SUBJECT"), have something to do with, Subjects? And thi
s
is all in the same column, no?
"LacOniC" wrote:

> This is Snitz Forum Database. If i edit database structure, i get more
problems. (For example when i update
> forum version)
> "You have Marital Status, and Subject mixed together in one column. "
> Didn't understand this one. They are in different tables and columns.
> Thank you.
>
>|||Probably my mistake. Column values are such as below:
F_SUBJECT
...
147. BlaBla
148. BlaBla
150. BlaBla
...
M_MARSTATUS
...
147
148
149
...
M_MARSTATUS column holds an user information, F_SUBJECT column holds forum s
ubject. They are relative but not
same. So i use LIKE.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:4A0BFEA5-5996-4442-A411-BEFB84A611FA@.microsoft.com...
> In your first post, you said...
> F_SUBJECT Values = ... "147. BlaBla" "148. BlaBla" "149. BlaBla" "150.
> BlaBla"
> I assumed the numeric values are Marital Status, and the "BlaBla" (based o
n
> the column name "F_SUBJECT"), have something to do with, Subjects? And t
his
> is all in the same column, no?
> "LacOniC" wrote:
>
update|||If F_Subject contains...
147. BlaBla
148. BlaBla
150. BlaBla
What is the value "147" in the first row above represent? Is it a Subject?
If it is some Subject, you can't link it (using LIKE) to the values in the
M_MARSTATUS
column. If it IS Marital Status, then it should be in it's own column as a
foreign Key, NOT concatenated wit hSubject data in the F_SUBJET Column.
"LacOniC" wrote:

> Probably my mistake. Column values are such as below:
> F_SUBJECT
> ...
> 147. BlaBla
> 148. BlaBla
> 150. BlaBla
> ...
> M_MARSTATUS
> ...
> 147
> 148
> 149
> ...
> M_MARSTATUS column holds an user information, F_SUBJECT column holds forum
subject. They are relative but not
> same. So i use LIKE.
>
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:4A0BFEA5-5996-4442-A411-BEFB84A611FA@.microsoft.com...
> update
>
>|||Oh no. I don't hold marital status values in that column or anywhere else. I
use that column to hold user's
sh information.
If user belongs to "150. period of school", he should see that period and ot
hers that is closed his period.
(147,148,149,151,152,153).
With query that i wrote in first message, user just see 150.|||If you don't "hold marital status values in that column or anywhere else"...
Then how can you be joining this column (FORUM_FORUM.F_SUBJECT), with the
FORUM_MEMBERS.M_MARSTATUS column? If the numbers in this column are NOT
marital statuses, then what is this join doing '
From your original post...
FROM FORUM_MEMBERS
INNER JOIN FORUM_FORUM ON
FORUM_FORUM.F_SUBJECT LIKE '%' +
FORUM_MEMBERS.M_MARSTATUS + '%'
"LacOniC" wrote:

> Oh no. I don't hold marital status values in that column or anywhere e
lse. I use that column to hold user's
> sh information.
> If user belongs to "150. period of school", he should see that period
and others that is closed his period.
> (147,148,149,151,152,153).
> With query that i wrote in first message, user just see 150.
>
>|||I wrote what column includes which value. I didn't say MaritalStatus cloumn
is empty. It just doesn't hold
what it says. It holds numbers as 150. In fact i can't say what join is doin
g there, lol. But it works very
well. =)|||Laconic,
Well if it works, then the value in there must mean something... Your
first task, before anyone can help you, (including yourself) is t ofind out
what is in the database, (Tables and Columns) and understand what the query
is supposed to be doing, and what it is doing...
That includes knowing, and understanding, the data that is in the database.
Until you do, you really shouldn't even be messing with it. If you're the
DBA, your first task should be to hire someone who knows enough about
database technology to do that. I say that reluctantlly, but, based on your
comments in this thread, it seems that you have a great deal to learn about
SQL and RDBMS technology in general, before your skills will be at the level
necessary to do what needs doing on this system.
"LacOniC" wrote:

> I wrote what column includes which value. I didn't say MaritalStatus c
loumn is empty. It just doesn't hold
> what it says. It holds numbers as 150. In fact i can't say what join is do
ing there, lol. But it works very
> well. =)
>
>