Solved: Access to mySQL query

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

billgates2000

Thread Starter
Joined
Nov 11, 2010
Messages
15
Hi,

I have a basic CMS that is running on MS Access. I'm trying to convert my queries to run on a mysql server too, but I'm having some trouble with one of them (and my sql knowledge is limited).

Basically, I have two tables, one is called [articles] and the other is called [authors].

The [articles] table has the following fields:
- articles_id (autonumber)
- article_title
- author_id

The [authors] table has the following fields:
- id (autonumber)
- author_name

The author_id field in the articles table stores the id of the articles's author. There is no relationship drawn between the two tables.

So... In MS Access, the following SQL displays all the articles in the database along with the names of their authors:

SELECT article_id, article_title, (SELECT author.author_name FROM authors WHERE articles.author_id = authors.id) AS author_name FROM articles ORDER BY id

However, in mysql, only the article_id and article_title are returned. Any idea what I'm doing wrong?

Thanks.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
I have to ask the obvious question, why is there no relationship between the 2 tables when there obviously should be.
That is what "Relational" databases do.
 

billgates2000

Thread Starter
Joined
Nov 11, 2010
Messages
15
Because MS Access retrieved the data sucessfully so the job was done. And when a job gets done, you don't change the procedure :)

Do you believe that the query will work on mysql server as soon as I put a relationship?
 
Joined
Jun 8, 2001
Messages
2,583
Seems the SQL for this would be

SELECT [articles].[article_id], [articles].[article_title], [authors].[author_name]

FROM [articles] JOIN [authors] on [authors].[id] = [articles].[author_id]

ORDER BY [articles].[article_id]

How ever...as OBP points out if the ID to the [author] table is truely auto number this won't ever work. There would be no way to point an author at an article...
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top