complicated statement

the master
10-20-2004, 11:35 AM
hi, i got a database with 3 tables for my forums. Forums, Threads and Posts.

the posts table stores an ID number of a thread so i can tell which thread its in. the threads table stores an ID of a forum and its used in the same way.

the forums table doesnt store a string of which threads it has in it because it isnt needed (i can filter for all threads which are in that forum)

now heres the prob. i get to a record in the forum table and i need to filter the posts table to posts in that forum. i need to say, "get every post that is in a thread in this forum" basicly. any ideas how to do this?

before i used to use a list of threads in each forum so it could say "WHERE posts.inthread IN(<forums.threadlist>)" but i cant do that now.

im using ADODB.record set BTW. Please help ASAP

Granty
10-20-2004, 01:10 PM
You need to link all 3 tables via their PK/FK.

Kind of like:

SELECT * FROM Posts
INNER JOIN Threads ON Posts.ThreadID = Threads.ThreadID
INNER JOIN Forums ON Threads.ForumID = Forums.ForumID
WHERE Forums.ForumID = MyValue

the master
10-20-2004, 01:14 PM
You need to link all 3 tables via their PK/FK.

Kind of like:

SELECT * FROM Posts
INNER JOIN Threads ON Posts.ThreadID = Threads.ThreadID
INNER JOIN Forums ON Threads.ForumID = Forums.ForumID
WHERE Forums.ForumID = MyValue

right, erm, i see what you put but i dont see what it all means. could you explain how it works please cos i mightn eed to use something similar in other areas. whats PK/FK mean?


and one other small question. for the forums. each foruum can be inside another forum by specifying the other forums ID in forums!inforum. there could be forums inside forums inside forums. is there an easy way to search them all and return a list of forums similar to this

main forum1
>sub forum1
>>sub sub forum 1
>>sub sub forum 2
>sub forum2
main forum2

etc.

Granty
10-20-2004, 01:26 PM
I havent worked with forum stuff so Im not really sure what you are asking in your 2nd part :confused:

PK = Primary Key, FK = Foreign Key. This is how relational databases work. So for each record in your forum table where your ForumID is your primary key, you would have many records in the Threads table which contain the ForumID as a foreign key. For each record in your Thread Table which has ThreadID as its Primary Key, you have many records in your Posts Table which has ThreadID as its Foreign Key.

the master
10-20-2004, 01:29 PM
ok thats starting to make sense but i dont have any primary keys cos i dont know what they are or do :chuckle:

also, whats that statement that you posted actually doing? i cant seem to get my head round it

Granty
10-20-2004, 01:39 PM
A primary key is a field containing only unique values. For your forums table, I would expect you to have a Primary Key ForumID and probably a Description. Threads would have a ThreadID (Unique PK), a ForumID(FK, so you know which forum it belonged to) and some other info like poster, title etc.
Posts would have a PostID (Unique PK), a ThreadID(FK so you know which Thread it belonged to) and all the post information.

The statement I gave is joining the tables on those key values. So you get all the information from Posts, linked to Threads so you know all the Threads those posts belong to, linked to Forums so you know which forum all those threads belong to. Then if u specify a Forum in the WHERE clause, and work back, you get all the threads from that forum, and all the posts from the threads in that forum.

To just get the post info you have to replace
SELECT *
with
SELECT Posts.field1, posts.field2,.... etc

Hope that makes some sense, I can suck at explaining :whoops:

loquin
10-20-2004, 01:56 PM
Take a look at the database installments in the *** for more info on keys and normalization.

the master
10-21-2004, 03:03 AM
granty, that explanation wasnt all that bad cos i reckon i understand how its working now. kinda complicated but i think i can manage.

loquin, ill have a look at that when i get home and start altering my DB.

other question: do i have to tell it that its an FK? right now i just told it to store a number value, i didnt do one of those thingys in access where it has a list but i was going to soon to make things look ok if the GUI fails (or isnt yet finished) on the plugin

MKoslof
10-21-2004, 07:10 PM
You don't tell your table it has a FK. Primary keys and foreign keys should be designed apppropriately and added to your tables before you start any sort of VB development. You can add constraints at any time via the ADOX or an ALTER TABLE statement. But a true FK or PK needs to be explicitally added and named. There is no such thing as a "pseudo" key. Sure, you can try to mimick its functionality, but add the keys in the appropriate manner, and then the table does the work for you, provided you run the appropriate queries against the tables you have designed.

the master
10-22-2004, 02:17 AM
rite, dont forget im still new to databases and ive never used a primary key before ;) i think i know how to make one. my current database just stores the ID of something else in the in<whatever> field and uses an SQL thingy to filter everything to display only stufdf from there

loquin
10-22-2004, 09:19 AM
Well, you HAVE to be able to guarantee that a record is unique. If a record is not unique, how can you ever find an existing record?

THAT is what a primary key is used for. It's just a field, or combination of fields, that make up a unique identifier. Most tables have existing information that already makes them unique. For instance, suppose I have a table of invoices. This table has an invoice number, a line number, an item description, an item cost, and other fields. The invoice number itself WON'T make this table unique by itself though. Suppose I have five items in the invoice? That would mean 5 records with the same invoice number. BUT, if I think about it, the combination of invoice number and line number together make a unique key. So. I don't have to add a surrogate primary key, as a perfectly good primary key already exists. In Access, I would just select both fields, and specify that they are the PK.

the master
10-22-2004, 01:11 PM
so basicly, can i just set the ID numbers to primary? the ID numbers are all autonumber columns set to increment and i told it NOT to allow duplicates

the master
10-22-2004, 02:46 PM
hi, just got round to trying your code and its not working. it says theres a missing operator and gives me this part of the code

'Posts.ThreadID = Threads.ThreadID
INNER JOIN Forums ON Threads.ForumID = Forums.ForumID'

the master
10-22-2004, 04:24 PM
YAY, fixed it :D took me ages to figure it out but i got therer in the end. it seems that it doesnt like using INNER JOIN twice in the same statement. might be the way i was doing it or whatever but instead i used it once and filtered the thread list so the forum part was cut out all together. i figured, why scan for the forum with a certain ID then scan for threads in a forum with that ID, might aswell just scan for the threads:p

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum