Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45

    Question Unanswered: Correct syntax on this SQL INNER JOIN Query.

    Hi

    Trying to get this sql - query to run under Query Analyzer but not sure how to correct anything in it to the right

    ______INFO_______
    Table : iptable
    Fields : ip_start, ip_end, location

    Table : PageLog
    Fields : pl_ipaddress, pl_sessionid, pl_remotehost
    _________________

    ______________CODE_______________
    select
    iptable.location
    , count(pl_ipaddress)
    from (
    select distinct
    pl_sessionid
    , pl_ipaddress
    , pl_remotehost
    from PageLog
    where pl_datetime
    between '2003-12-25 00:00:00'
    and '2003-12-25 23:59:59'
    and pl_ipaddress <> ''
    ) as dt_pagelog
    inner
    join iptable
    on dt_pagelog.pl_ipaddress
    between iptable.ip_start
    and iptable.ip_end
    group
    by iptable.location
    order
    by count(pl_ipaddress) desc
    ___________END CODE_________________

    1) If i run this i get :
    --> "Column dt_pagelog.pl_ipaddress is invalid in the select list because it's not contained in a aggregate function or in the GROUP BY clause.

    2) If i include it in the GROUP BY i get :
    --> The text, ntext and image datatypes cannot be used in WHERE, HAVING or ON clause, except with the LIKE or IS NULL predicate.

    Soo.. how on earth should i put this right to get to use it with a INNER JOIN, since that has to have a ON to it ?

    Not very familiar with INNER JOIN's so any help will be very much appreciated..

    Best regards
    Mirador.

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    62

    Thumbs up

    select a.col1, a.col2, b.col1, b.colxxxx
    from table1 as a
    inner join table2 as b
    on a.col1 = b.col1
    where a.col2 = value
    Regards,

    Rushi

  3. #3
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45
    Hi Rushi and thanx for your reply..

    What am i to do with the SELECT DISTINCT ........... FROM PageLog ?

    Should i add PageLog.fieldname to each of the sentences in the select there to ?

    Mirador.

  4. #4
    Join Date
    Jan 2004
    Location
    India
    Posts
    62
    select distinct a.location, ...........
    from pagelog as a
    inner join iptable as b
    on a.--------- = b.========


    In inner join syntax
    you have to join the common fields of the 2 tables in inner join clause and the actual where condition in where clause.
    Regards,

    Rushi

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the error message doesn't make sense

    "dt_pagelog.pl_ipaddress is invalid in the select list because it's not contained in a aggregate function"

    dt_pagelog.pl_ipaddress is contained in a aggregate function -- the COUNT()

    maybe it's the subquery's DISTINCT, although i seriously doubt it

    try this:
    Code:
    select iptable.location
         , count(dt_pagelog.pl_ipaddress) 
    from (
         select pl_sessionid
              , pl_ipaddress
              , pl_remotehost 
           from PageLog 
          where pl_datetime 
                between '2003-12-25 00:00:00'
                    and '2003-12-25 23:59:59'
            and pl_ipaddress <> '' 
         group
             by pl_sessionid
              , pl_ipaddress
              , pl_remotehost   
         ) as dt_pagelog
    inner
      join iptable
        on dt_pagelog.pl_ipaddress 
           between iptable.ip_start 
               and iptable.ip_end
    group 
        by iptable.location
    order 
        by count(dt_pagelog.pl_ipaddress) desc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45

    Smile

    Hi again Rudy

    This is indeed a bit weird...
    Tried the exact query u posted and got this errormsg :


    ---------------Error-----------------

    Server: Msg 306, Level 16, State 1, Line 1
    The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates.

    Server: Msg 306, Level 16, State 1, Line 1
    The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates.
    --------------------------------------

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, see if you can understand where i'm going with this...

    which one of your columns is text, ntext, or image?

    and just to give you a little advnace notice, my next question will be why

    session id, ip address, remote host, location -- those all sound like varchars to me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45

    Wink

    Oh my... i finally got it....

    The ip_start and ip_end was text while the others were varchar..

    As soon as i put all of them to varchar it worked...

    Well.. ended up with a easy solution after all!... I thought this had to be something really really tricky stuff..

    Now i know hehe.. varchars dont match very good with text when it comes to comparing..

    Thanx for all your help Rudy...

    Best regards
    Terje.

Posting Permissions

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