SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru downtroden's Avatar
    Join Date
    Dec 2004
    Location
    illinois
    Posts
    978
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    getting "unknown column" error from inner join

    here's my query:
    PHP Code:
    SELECT         t1.idt1.proposalTitlet1.proposalContent,
                        
    t2.idt2.proposalIdt2.fileId
    FROM            proposals t1
    proposalAttachments t2
    INNER JOIN   proposalAttachments
    ON                t1
    .id t2.proposalId
    WHERE          t1
    .id '1'
    the id column exists... but i don't understand why i'm getting the unknown column 't1.id' in 'on clause message'.
    your brain reacts in the same way whether you are
    looking at something or thinking about it...

  2. #2
    SitePoint Member
    Join Date
    Jan 2004
    Location
    cambridge uk
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you need to put the on clause in brackets?
    Code:
    ON  (t1.id = t2.proposalId)
    prolly wont make any difference tho.. i dont know what Im talking about

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    change this --
    Code:
    FROM            proposals t1, proposalAttachments t2
    INNER JOIN   proposalAttachments
    ON                t1.id = t2.proposalId
    to this --
    Code:
      FROM proposals AS t1
    INNER 
      JOIN proposalAttachments AS t2
        ON t2.proposalId = t1.id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    and change this --
    Code:
    WHERE          t1.id = '1'
    to this --
    Code:
    WHERE  t1.id = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru downtroden's Avatar
    Join Date
    Dec 2004
    Location
    illinois
    Posts
    978
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cool, thank you.

    got it working with a WHERE, AND, AND type setup. Which is more efficient, joins or stringing together AND's?

    Also, used this page as a reference, http://www.w3schools.com/sql/sql_join.asp is it just completely wrong or just not targeted to what i was needing?
    your brain reacts in the same way whether you are
    looking at something or thinking about it...

  6. #6
    SitePoint Guru downtroden's Avatar
    Join Date
    Dec 2004
    Location
    illinois
    Posts
    978
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh... and i added some more stuff... can you do multiple INNER JOINS?

    PHP Code:
                SELECT    t1.idt1.proposalTitlet1.proposalContent,
                        
    t2.idt2.proposalIdt2.fileId,
                        
    t3.idt3.fileTitlet3.fileTypet3.fileName
                FROM    proposals t1
    proposalAttachments t2siteFiles t3
                WHERE    t1
    .id t2.proposalId
                
    AND        t2.fileId t3.id
                
    AND        t1.id '#URL.entry#'
    your brain reacts in the same way whether you are
    looking at something or thinking about it...

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by downtroden View Post
    Which is more efficient, joins or stringing together AND's?
    as far as efficiency is concerned, they are equal

    however, JOIN syntax is much easier to write, to understand, and to debug

    and yes, you can do multiple INNER JOINs

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    p.s. instead of t1, t2, and t3, name your table aliases p, pa, and f
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    AND        t1.id = '#URL.entry#';
    not to beat you up or anything, but this has a wee sql injection vulnerability
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Guru downtroden's Avatar
    Join Date
    Dec 2004
    Location
    illinois
    Posts
    978
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yeah, i know... i'll add in the queryparam tag, i've been kinda lax on security since it's an internal site.
    your brain reacts in the same way whether you are
    looking at something or thinking about it...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •