Help with Query

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

IanPlease post your DDL + INSERT statements of sample data + desired results.

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Crumb" <rowan.ian@.gmail.com> wrote in message
news:1141597991.414860.283020@.j33g2000cwa.googlegr oups.com...
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|||On 5 Mar 2006 14:33:11 -0800, Crumb wrote:

>Hi,
(snip)

Hi Ian,

Answered in microsoft.public.sqlserver.server.

Please don't post copies of the same question to multiple locations; it
makes it harder for you to keep track of the answers and yields extra
work for those who try to answer.

--
Hugo Kornelis, SQL Server MVP

Help with Query

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
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
>
|||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
OriginationNumberStartTime EndTime
901183922222203/03/2006 12:0103/03/2006 12:12
National Table
DigitsLocation
0118Reading
Required Output
OriginationNumberLocationStartTimeEndTime
9011839222222Reading03/03/2006 12:0103/03/2006 12:12
All Times are DateTimes, the locations and Numbers as Nvarchar
Many Thanks
Ian
|||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

Help with Query

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
IanSELECT 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
>|||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 StartTime EndTime
9011839222222 03/03/2006 12:01 03/03/2006 12:12
National Table
Digits Location
0118 Reading
Required Output
OriginationNumber Location StartTime End
Time
9011839222222 Reading 03/03/2006 12:01 03/03/2006 12:12
All Times are DateTimes, the locations and Numbers as Nvarchar
Many Thanks
Ian|||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