MS SQL Server - Help with Query

Author

Help with Query

Crumb

2006-03-05, 8:24 pm

Hi,

I am trying to perform a query that is beyond my knewlegde and was
after little help,

I have two tables in my db, national and CDR. the National table
displays the codes and location for every std code in the UK, the CDR
table displays the information about calls made from people in my
company.

I am trying to reference both tables so that the result display the
location of the dailled numbers, here is the query I have built using
the query tool in SQL but it does not seem to work. could any tell me
where I am going wrong?

SELECT dbo.IpPbxCDR.OriginationNumber, dbo.[National].Location
FROM dbo.[National] CROSS JOIN
dbo.IpPbxCDR
WHERE (dbo.[National].Digits LIKE
'%dbo.IpPbxCDR. OriginationNumber%')

Many Thanks

Ian

Sreejith G

2006-03-05, 8:24 pm

SELECT dbo.IpPbxCDR.OriginationNumber, dbo.[National].Location
FROM dbo.IpPbxCDR LEFT JOIN dbo.[National]
ON (dbo.[National].Digits LIKE
'%dbo.IpPbxCDR. OriginationNumber%')

OR

SELECT dbo.IpPbxCDR.OriginationNumber, dbo.[National].Location
FROM dbo.IpPbxCDR LEFT JOIN dbo.[National]
ON (dbo.[National].Digits = dbo.IpPbxCDR.OriginationNumber)

Dont know why u used Like as you didnt specify the datatypes and the
condition you required...

--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]

"Crumb" wrote:

> Hi,
>
> I am trying to perform a query that is beyond my knewlegde and was
> after little help,
>
> I have two tables in my db, national and CDR. the National table
> displays the codes and location for every std code in the UK, the CDR
> table displays the information about calls made from people in my
> company.
>
> I am trying to reference both tables so that the result display the
> location of the dailled numbers, here is the query I have built using
> the query tool in SQL but it does not seem to work. could any tell me
> where I am going wrong?
>
> SELECT dbo.IpPbxCDR.OriginationNumber, dbo.[National].Location
> FROM dbo.[National] CROSS JOIN
> dbo.IpPbxCDR
> WHERE (dbo.[National].Digits LIKE
> '%dbo.IpPbxCDR. OriginationNumber%')

>
> Many Thanks
>
> Ian
>
>

Crumb

2006-03-05, 8:24 pm

Thanks Sree,

I don't think I gave enough information as this still did not work!

What I am trying is as follows:

IpPbxCDR Table

OriginationNumber St
artTime EndTime
9011839222222 03/03/2006 12:01 03/03/2006 12:12

National Table

Digits Location
0118 Reading

Required Output

OriginationNumber Lo
cation StartTime End
Time
9011839222222 Readin
g 03/03/2006 12:01 03/03/2006 12:12

All Times are DateTimes, the locations and Numbers as Nvarchar

Many Thanks

Ian

Hugo Kornelis

2006-03-05, 8:24 pm

On 5 Mar 2006 14:36:33 -0800, Crumb wrote:

(snip)
>SELECT dbo.IpPbxCDR.OriginationNumber, dbo.[National].Location
>FROM dbo.[National] CROSS JOIN
> dbo.IpPbxCDR
>WHERE (dbo.[National].Digits LIKE
>'%dbo.IpPbxCDR. OriginationNumber%')

Hi Ian,

Without knowledge of how your tables really look (please post CREATE
TABLE and INSERT statements next time - www.aspfaq.com/5006), I can only
offer a wild guess:

SELECT dbo.IpPbxCDR.OriginationNumber, dbo.[National].Location
FROM dbo.[National]
CROSS JOIN dbo.IpPbxCDR
WHERE dbo.[National].Digits LIKE '%'
+ dbo.IpPbxCDR.OriginationNumber
+ '%'

or (technically the same - just a matter of taste which you pick)

SELECT dbo.IpPbxCDR.OriginationNumber, dbo.[National].Location
FROM dbo.[National]
INNER JOIN dbo.IpPbxCDR
ON dbo.[National].Digits LIKE '%'
+ dbo.IpPbxCDR.OriginationNumber
+ '%'

--
Hugo Kornelis, SQL Server MVP

Sponsored Links