phpmyadmin Question posted in
The official documentation can be found here.

SQL query doesn't work on fields added after a constraint was created – Phpmyadmin

So I know the title might seem confusing. To begin with, I created a table called Items that contained ItemNo as a primary key, along with Title and Description. I then created a table called Articles with ArticleNo as a primary key, along with Author and Text.

I then realised I wanted to link these two tables, so added another key to the Articles table (called ItemNo) and linked it to the Items table.

The query I’m using is: 

SELECT Articles.ArticleTitle, Articles.Author, Articles.Text, Images.ImagePath, Articles.ArticleNo
                  FROM Articles, Images
                  INNER JOIN RelatedImagesArticles
                  WHERE Articles.ArticleNo = RelatedImagesArticles.ArticleNo 
                  AND Images.ImageNo = RelatedImagesArticles.ImageNo AND Articles.ItemNo = ?

(The other tables and fields mentioned are working fully)

Now that I’ve added the ItemNo constraint, the above query only works on fields added before the constraint was added and completely ignores fields added after. I’m using PHPMyAdmin if that helps.

I know my issue doesn’t contain much information, not sure how else to add more relevant info in a clear way. If any more info is needed I’ll try and provide

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Figured it out. The query would only ever return articles that have a corresponding image. If an article doesn't have an image, then the article field is completely ignored


  2. 0

    Do not use commas in the FROM clause. Rephrase the query using proper JOIN syntax:

    SELECT a.ArticleTitle, a.Author, a.Text, i.ImagePath, a.ArticleNo
FROM Images i JOIN
     RelatedImagesArticles ria
     ON i.ImageNo = ria.ImageNo JOIN
     Articles a
     ON a.ArticleNo = ria.ArticleNo 
WHERE a.ItemNo = ?;

    I think you are trying to describe a scoping issue, but your language is hard to follow. You query doesn’t have a “constraint” in the SQL sense. Constraints are things in tables. It does have conditions, specifically JOIN conditions between tables and filtering conditions
    within a table.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search