SQL query

knickerlas

Hi
I have a table 'MASTER' that that has a primary auto incrementing key.

Then I have several other tables that hold records linked to the master by that key like this:

MASTER
1 one record
2 two record
3 three record
4 four record

ANOTHER TABLE
1 (one record) linked to MASTER table
2 (two record) linked to MASTER table

YET ANOTHER TABLE
3 (three record) linked to MASTER
4 (four record) linked to MASTER

My problem is, how can I select from the matching record when all I know is the key of the MASTER?

Do I have to provide another key or what?

Cheers

Nick

nightowl

select * from mastertable, another table where a_master_field = xxx AND mastertable. master_id = another_table.master_id

the key is to set in the where clause that the master id and the id in the other table(s) need to be the same...

neuromancer

SELECT n,... FROM MASTER INNER JOIN [ANOTHER TABLE] ON MASTER.ID = [ANOTHER TABLE].MASTER_ID ?

neuromancer

Your syntax is wrong

knickerlas

Okay guys sorry I didn't make it so clear, I know how to select if I know the name of the table, my point is how to select if I only know the key in MASTER?
I could loop through all the tables untill I found the matching key but if I have many tables it would be inefficient:

Hope you get it!

Nick

nightowl

You could do it like you said, indeed. however, the syntax given by me works too. it is less complicated for beginners. Which i figured Nick would be since he was asking a trivial question...

nightowl

Are you sure the structure of your database (tables) is right? It seems like a very odd thing for me if you don't know in which table to look for the data you search...

neuromancer

wtf are you trying to do again?

So you have designed this database and you have some tables with keys but you don't know what tables relate to them?

knickerlas

No, they might need re-designing, the Idea is a content system where the master holds the page id, title keywords etc and the other tables are 'sections' of the site and hold the content to be passed through templates.
The reason for the different tables is different 'sections' have different fields to be loaded into the templates.
Now I'm thinking that I will have to create a seperate DB for each section containing the MASTER and CONTENT tables in each.
Do you have any thoughts on this?

Cheers

Nick

neuromancer

Why not make a joining table with some kind of "type" flag?

knickerlas

The problem is that if I want to search for the corresponding record to 'pageid 12' from the MASTER it could be in table 1,2,3,4 etc

Nick

knickerlas

Yeah, I'm toying with that idaea at the moment. The problem I see is that if the 'type' table where to hold the name of the 'section table' how would I retrieve it efficiently?
Correct me if I'm wrong but I can't say somthing like this can I?
(pseudo code)

select content from master(joinedto)type(joinedto)unknownTable where unknownTable=(get type field)

I know this is supremely fucked up so thanks for the assistance.
The system was nicely set with just two tables but now I want to introduce new 'sections' with different field set ups it's getting dead trickY.
At present I see know way to do this but use mutiple DB's with two tables in each. This means that to get all the latest pages on the sight I will have to query each DB and then sort the results though.

How is this generally done? do you have any experience that might point the way to an efficient structure for data driven content?

Thanks again

Nick

nightowl

You should make it possible for yourself -by adding a field in the master-table- to know what the record will be used for, what section (and so what table) it will be in .