Forum Moderators: open
Beyond the basic SELECT FROM WHERE ORDER BY LIMIT sql statements, I know nothing about SQL.
I'm using PHP to send a SQL query to a mysql 5 database on my test machine.
This is what I would like to do:
I have 2 tables that store RSS information that I have imported and stored from various websites like CNN and FOXNews. One table has the information on the rss source, feedid, the last time it was pulled, etc...
The other table has the rss items (ie...itemid, feedid, titles, descriptions, item URL).
I want to pull and display a limited number of rss items from the rss items table and (based on the feedid) pull information from the table holding the rss source table.
I know I need a join but not sure how to go about doing it. Here is the code that doesn't work, but hopefully it will give more idea on what I would like to do...
SELECT feeditems.itemID, feeditems.itemTitle, feedinfo.feedURL FROM feedItems, feedInfo WHERE feedID = 1 ORDER BY itemID DESC LIMIT 10
Does this make sense?
Thanks for any help!
Kevin
SELECT feeditems.itemID, feeditems.itemTitle, feedinfo.feedURL FROM feedItems LEFT JOIN feedInfo ON feeditems.feeid = feedinfo.feeid WHERE feeditems.feedID = 1 ORDER BY feeditems.itemID DESC LIMIT 10;
Something like that should work.
dc
Thank you for the reply! I want to be able to understand what is going on so correct me if my understanding of the code is wrong:
LEFT JOIN connects one table to another. So this SELECT statement selects the rows from feeditems and joins the rows from feedinfo ON the feedid value. Then the WHERE statement only selects the rows based on the stated condition.
I guess it never struck me that you join the tables first with the SELECT statement first.
Is this about right? I just want to make sure I understand the logic so when this comes up again....
There are different types of joins, which you can see here:
[dev.mysql.com...]
What you have to be careful of is both tables having a row the same name if you want to display that info. For example, 'feeid' is ambiguous as it belongs to both tables. If you need to access either you would have to assign it to another var in your statement:
SELECT feeditems.feeid AS first,feedinfo.feeid AS second...
dc
