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.

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

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.

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.

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:

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

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

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.