CODEWITHSUNDEEP
mysqlselect
heron
heron

Reputation: 3661

MySQL query issue: Can't select columns from another table

My SQL statement looks like that

SELECT SQL_CALC_FOUND_ROWS q.id, q.question, sb.subject, c.chapter, sc.section, p.paragraph 
FROM questions AS q
     , subjects AS sb
     , sections AS sc
     , chapters AS c
     , paragraphs AS p 
WHERE (sb.id=q.subject AND c.id=q.chapter AND sc.id=q.section AND p.id=q.paragraph) AND (q.author_id=1) 
ORDER BY  q.id asc 
LIMIT 0, 25

As you see, theese tables related with each other by id. The problem is, I'm getting subject column of questions table instead of subject column of subjects table. What am I missing here?

Note

I provided subject as example. Actually all three sections AS sc, chapters AS c, paragraphs AS p are not working. I'm getting only subject.

Upvotes: 0

Views: 243

Answers (2)

talha2k
talha2k

Reputation: 25473

Apparently the query in your question is fine, but still to dig it out use this statement instead and see what comes up in subjects_subject column:

SELECT SQL_CALC_FOUND_ROWS q.id, q.question, sb.subject as subjects_subject, c.chapter, sc.section, p.paragraph 
FROM questions AS q
     , subjects AS sb
     , sections AS sc
     , chapters AS c
     , paragraphs AS p 
WHERE (sb.id=q.subject AND c.id=q.chapter AND sc.id=q.section AND p.id=q.paragraph) AND (q.author_id=1) 
ORDER BY  q.id asc 
LIMIT 0, 25

Upvotes: 2

Benjamin Cox
Benjamin Cox

Reputation: 6120

I normally use JOIN syntax explicitly. This will sometimes give you better error output, to help track down the issue.

Here's the original, formatted so you can easily see the differences.

SELECT SQL_CALC_FOUND_ROWS q.id, q.question, sb.subject, c.chapter, sc.section, p.paragraph 
FROM questions AS q, 
     subjects AS sb, 
     sections AS sc, 
     chapters AS c, 
     paragraphs AS p 
WHERE (sb.id=q.subject 
       AND c.id=q.chapter 
       AND sc.id=q.section 
       AND p.id=q.paragraph) 
      AND (q.author_id=1) 
ORDER BY  q.id asc 
LIMIT 0, 25

And here's the edited version - notice the WHERE clause parameters that define how the tables hook together have been moved up to the table names list as ON clauses. This is part of the INNER JOIN syntax, which is: INNER JOIN ON .

SELECT SQL_CALC_FOUND_ROWS q.id, q.question, sb.subject, c.chapter, sc.section, p.paragraph 
FROM questions AS q
  INNER JOIN subjects AS sb ON sb.id = q.subject
  INNER JOIN sections AS sc ON sc.id = q.section 
  INNER JOIN chapters AS c ON c.id = q.chapter
  INNER JOIN paragraphs AS p ON p.id = q.paragraph
WHERE (q.author_id=1) 
ORDER BY  q.id asc 
LIMIT 0, 25

If you try to run that against MySQL directly, what happens?

Upvotes: 1

Related Questions