SQL Query Question

LacOniC

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 + '%'

tomhath

I'm confused by the F_SUBJECT column in the FORUM_FORUM table. The values in that column have the member age as part of a string?

Is there some reason you can't just add a couple of columns to that table called min_age and max_age? That would solve #1 and #3

For #2, make the combination of MEMBER_ID and FORUM_ID a unique key on the table. The query will fail if you try to insert a duplicate; just ignore the error.

LacOniC

Originally posted by tomhath
I'm confused by the F_SUBJECT column in the FORUM_FORUM table. The values in that column have the member age as part of a string?

Is there some reason you can't just add a couple of columns to that table called min_age and max_age? That would solve #1 and #3

For #2, make the combination of MEMBER_ID and FORUM_ID a unique key on the table. The query will fail if you try to insert a duplicate; just ignore the error.

For example M_AGE is 50. F_SUBJECTs are "50. BlaBla", "51. BlaBla". In fact they are not ages but column name is that. U can think them as AGE and Forum Subjects include Ages.

Thank you.

tomhath

F_SUBJECTs are "50. BlaBla", "51. BlaBla". In fact they are not ages but column name is that.

I still think it would be easier to store the age in a separate column that holds a number and indicates the member's age. I just can't see the advantage of storing the age as part of the subject title. Putting the age and subject in separate columns will make your queries simpler; you can always concatenate them when you need a string with the two together.

What will keep the wildcard from matching an age of 8 with forums containing 18, 80, etc.?

LacOniC

This database belong to a forum (snitz). So editing is not easy. 8, 18, 80 is not possible. As i say, that column don't hold age values in fact. Real values are between 100 and 200.

Below code is a solution for 1. and 2. issues:

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)

LacOniC

Any idea about that one:

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)

BETWEEN '%' + FORUM_MEMBERS.M_AGE - 3 + '%' AND '%' + FORUM_MEMBERS.M_AGE + 3 + '%'

So absurd? 🙁