1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access to mySQL query

Discussion in 'Business Applications' started by billgates2000, Jan 11, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. billgates2000

    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.
     
  2. OBP

    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.
     
  3. billgates2000

    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?
     
  4. draceplace

    draceplace

    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...
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1084680

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice