Hi,

I've had few times the same problem and i'd like to go over it.
I'm facing this kind of data:

say that i need to store documents, all of them have a set of fields in common and all also have specific fields:

articles [ TITLE, AUTHOR, DATE, PIC, EXTRACT, KEYWORDS, ... ]
news [ TITLE, AUTHOR, DATE, URL, SOURCE, KEYWORDS, ... ]
...

my first solution was to store all of this data like this:
doc [ TITLE, AUTHOR, DATE, KEYWORDS, TABLENAME, TABLEID ]
doc_articles [ ID, PIC, EXTRACT ]
doc_news [ID, URL, SOURCE ]

It works fine BUT i need 2 queries for each doc to get its corresponding specific data.

Is there a way to dynamicaly link the corresponding fields leading to a result set with all table fields??

Is there another way to design tables to make it easier?

Thx in advance for reading,

++

Check to see how your database engine supports the JOIN operation. I think it will do what you want.

-PatP

Yes, i may not have been precise enough,

I actually JOIN when i know TABLENAME:

If i want news rows i do a

select * from doc,doc_news left join doc.TABLEID=doc_news.ID where doc.TABLENAME=doc_news


BUT i sometimes get a doc row without knowing if its a news or an article.... i then need a 2nd query to get the specific table rows.

Would there be a query to select the doc row AND the specific table row with one query?

I'd say, Is there dynamic SQL possilities like

SELECT * from doc,doc.TABLENAME where doc.ID=X ??
(doesn't work "as is" in mysql)...

++

use LEFT OUTER JOINs to join the doc table to both the news and the articles table

each PK in doc will have only one matching FK in either news or articles, so bob's your uncle -- still only one query!!



but check your syntax carefully, because this isn't even close to being valid syntax --

select * from doc
,doc_news
left join doc.TABLEID=doc_news.ID
where doc.TABLENAME=doc_news

if you must continue with your current design you could also consider aliasing
eg
select Article.docID as ArtDoc, Article.Author as ArtAuthor from blah

not nice but it may help