August 8, 2005 at 10:37 am
Hello, I am new to this level of SQL and am seeking a little guidance. Any help you can provide is appreciated.
Currently I am trying to create a view from several tables. One of the tables has coulmns for for six article IDs (Article1_ID, Article2_ID, etc.). These IDs are to be used to get the article title and abstract back from another table whose primary key is the article IDs that populate the former table. In the view, for each record I want to have the article ID for each article as well as it's title and abstract. I was running the following code:
SELECT table1.ID, table1.Master_ID, table1.Firm_ID, table1.Message_Headline, table1.Message_Body,
table1.Template, table1.Article1_ID, table1.Article2_ID, table1.Article3_ID,
table1.Article4_ID, table1.Article5_ID, table1.Article6_ID, table2.title
FROM table1
INNER JOIN table2 ON table1.Article1_ID = table2.ID OR table1.Article2_ID = table2.ID
OR table1.Article3_ID = table2.ID OR table1.Article4_ID = table2.ID
OR table1.Article5_ID = table2.ID OR table1.Article6_ID = table2.ID
But this is returning six records per Eletter_Eclient.ID, one for each article title. I want to get columns in the view for Article1_Title, Article2_Title, etc., based on the article's ID.
If some one could point me in the right direction or let me know if it is even possible, I would really appreciate it.
Thanks.
August 8, 2005 at 10:46 am
You need to join once for each column using the same table.
SELECT table1.ID, table1.Master_ID, table1.Firm_ID, table1.Message_Headline, table1.Message_Body,
table1.Template, table1.Article1_ID, table1.Article2_ID, table1.Article3_ID,
table1.Article4_ID, table1.Article5_ID, table1.Article6_ID,
art1.title, art2.title, art3.title, art4.title, art5.title, art6.title
FROM table1
INNER JOIN table2 art1 ON table1.Article1_ID = art1.ID
INNER JOIN table2 art2 ON table1.Article2_ID = art2.ID
INNER JOIN table2 art3 ON table1.Article3_ID = art3.ID
INNER JOIN table2 art4 ON table1.Article4_ID = art4.ID
INNER JOIN table2 art5 ON table1.Article5_ID = art5.ID
INNER JOIN table2 art6 ON table1.Article6_ID = art6.ID
I'd be a little concerned with your data model, you want to stay away from repeating groups. But this will work this way.
August 8, 2005 at 12:03 pm
Thanks for the reply. I will give this a try. Unfortunately for the data model - I inherited it and don't really have much control over many of the aspects of it.
August 8, 2005 at 1:06 pm
that worked great! Thanks, again.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply