Multiple Table Query Problem

Anon

Hi Everyone

I've been trying to find a good example of this kind of situation but have had no luck.

In an online publication, I have a one-to-many database that I need to be able to do a full keyword search on. The structure is shown below:

Issue_Table (Table 1)

Id (Primary Key)
Issue_Date
Issue_Header

||||||||||||||||

Features_Table (1st of Many Tables)

Issue_Id (Foreign Key)
Headline
Story

News_Table (2nd of Many Tables)

Issue_Id
Headline
Story

Reviews_Table (3rd of Many)

Issue_Id
Headline
Story

================

Each record in Issue_table is related to multiple records in each of the other tables by "Issue_Table.ID=News_Table.Issue_ID".

I want to return:

Issue_Id | Features.Headline | Features.Story
Issue_Id | News.Headline | News.Story

I hope this is clear...

I just want to query all these fields in one statement, so I don't have to perform 7 SELECT statements and build temporary tables etc... The thing is, the JOIN syntax is not what I'm looking for (I don't think) as it requires uniquely named fields and returns the table results side by side instead of in one list.

Can ANYONE help??

Anon

I'm not sure what you want your select statment to return but this is what i think your asking for.
Issue_Id | Features.Headline | Features.Story
Issue_Id | News.Headline | News.Story

select feature.headline, feature.story, news.headline, news.story
from news_table news, Features_table feature
where news.issue_id = feature.issue_id

I'm assuming that the issue_id is the same for any given article. For example say I write an article i'll be given an Issue_id of 2
and this id for my article will be the same in both the news_table, Features table, and the Review_table. If this is true then the above query should work.

Good luck
Varun

sarahk

Haven't got time to go through your query properly but you can do

select table1.field1 as field2, table2.field1
from table1, table2
where table1.field1 = table2.field1

etc

I've found it useful for new to SQL folk to use a query tool (Access, VFP) to construct the queries and then view the sql and see what's been done and learn from that. Obviously the extra work of duplicating your tables for a learning exercise may be offputting but the gain is worth it.

If your query makes sense to you then it should be possible.

Anon

Thanks guys, but no joy...

I tried both your methods (my end SQL statement is below), and get the results joined across the result array as:

Issue.id | feat.head | feat.body | news.head | news.body

So I get loads of duplicate news results and when it comes to print the results in a while loop I can't isolate news items from features and order them correctly.

Any further ideas? Otherwise I'm gonna have to go with a bunch of separate queries and a temporary table... bah...

Roki (current code & results follow)

$sql = "SELECT feat.headline, feat.body, news.headline, news.body ";
$sql.= "FROM feat, news ";
$sql.= "WHERE feat.issue_id=news.issue_id AND (feat.headline LIKE '%$keyword%' OR ";
$sql.= "feat.body LIKE '%$keyword%' OR news.headline LIKE '%$keyword%' OR news.body LIKE '%$keyword%') ";

Gives results like:

The houses of pain - Hilton's reassessment of relationship could see ad business leave Scotland

Reiding and writing - Hilton's reassessment of relationship could see ad business leave Scotland

Feature 1 - News 1

Feature 2 - News 1

Feature 1 - News 2

Feature 2 - News 2

Feature 1 - News 3

Feature 2 - News 3

(this goes on for some time...)