<Matthew Stuart wrote>
I have a home page where I need to show three news articles which are
specified by the administrator - so it might not be the latest articles
and they might not be in any order. I have designed the db to have two
tables to fulfill this function the first table 'tbl_press' holds the
news articles, and second 'tbl_hompageids' is what governs the three
articles to be shown.
tbl_homepageids has three columns, fld_article1, fld_article2 and
fld_article3 each having a value manually inputted to indicate which
article to select from tbl_press
tbl_press has many columns, but the key one in this instance is a field
called 'fld_id' which is the value I am trying to get the home page to
read via use of tbl_homepageids.
Basically, I am trying to use the table 'tbl_homepageids' as the engine
to select the correct articles from the table 'tbl_press'.
The recordset/SQL statement I am trying to create goes like this:
SELECT *
FROM tbl_homepageids, tbl_press
WHERE fld_article1,tbl_homepageids LIKE fld_id,tbl_press
I will create another two recordsets/SQL statements for the other two
press articles using fld_article2 and fld_article3.
Quite obviously the statement here isn't working and I have tried many
different variations on the same theme, but haven't managed to achieve
the results required, so I have written it in its simplest format
hoping somebody can understand what I need and help me through this
trying period in my life!!
TIA
Mat
</Matthew>
Hi Mat,
There are two ways to approach this issue. You chose to make one record
with 3 columns as the specifier of which articles become your headlines. To
get the 3 articles from tbl_press you will have to check the ID columns
against all 3 values. Now there are multiple ways to do this but this one
is less version specific:
SELECT tp.*
FROM tbl_homepageids h, tbl_press tp
WHERE tp.fld_ID IN (h.fld_article1, h.fld_article2, h.fld_article3)
While this method works there are several things to remember:
1. You will get 3 articles back for every 1 full row in your headlines
table.
2. Your headlines table is limited to just 3 articles per row. Changing
that limit will require a change to your database.
If you redesigned your headlines table to have 1 column in it, say
"fld_articleID" then you could have 1 article or 1000 articles on your
front page without needing to change your design. That would also simplify
your query to get the articles for the front page to:
SELECT tp.*
FROM tbl_homepageids h, tbl_press tp
WHERE tp.fld_ID = h.fld_articleID
-or you could write it this way, too-
SELECT tp.*
FROM tbl_press tp
INNER JOIN tbl_homepageids h
ON tp.fld_ID = h.fld_articleID
Best wishes!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
