If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Sql query

Hi, sorry im not skilled at SQL i just know the basics so when it comes to inner joining and joining commands i suck lol. can you please provide me with query i can just copy and paste in sql query analyser:P

Hi, sorry im not skilled at SQL i just know the basics so when it comes to inner joining and joining commands i suck lol. can you please provide me with query i can just copy and paste in sql query analyser:P

You did not gave enough information for anyone to do what you ask. We need to know the columns in both tables that define the relation between those tables, like "Contact1.callerId = Cal.id and Contact1.year = Cal.startYear" or whatever.

With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Those date criteria do not make any sense. If something is greater than or equal to November 11, 2011 AANNDD that same thing is also less than or equal to November 11, 2011, then those two conditions can only exist if that thing IS November 11, 2011.

Sql query

Hi Ken,

Thank you very much for your reply. I tried copying that query into sql and here are the errors i got back.

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'CAL' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'CAL' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'CAL' does not match with a table name or alias name used in the query.

In regards to conthist this is a totally seperate table within the Goldmine_MIOA database. From the Goldmine_MIOA database we are looking at 2 tables (contact1 and conthist) there is no CONTACT2 (SORRY) from the Goldmine_GMBase database we are looking at 1 (CAL)

SELECT C1.*
FROM Goldmine_MIOA.dbo.CONTACT1 AS C1 WITH (NOLOCK)
INNER JOIN (SELECT DISTINCT ACCOUNTNO
FROM GoldMine_GMBase.dbo.CAL as CAL
WHERE CAL.ONDATE BETWEEN '20111111' AND '20111111' AND
CAL.RECTYPE in ('S')
) CL ON
CL.AccountNo = C1.AccountNo
INNER JOIN (SELECT DISTINCT ACCOUNTNO
FROM Goldmine_MIOA.dbo.CONTHIST as CONTHIST
WHERE CONTHIST.ONDATE BETWEEN '20111111' AND '20111111' AND
CONTHIST.REF LIKE '%Pharmacy Worksheet%'
) CH ON
CH.AccountNo = C1.AccountNo
ORDER BY C1.CONTACT

You may have to change "dbo" by the real schema names of your tables.

With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

I have just confirmed and the database owner is "dbo" if this helps. I HATE SQL lol, Its very urgent i get this code running the next few hours i have been working on it with minimal knowlege and some research since yesterday. I wish your awesome knowledge of SQL saves me from this misery!

Sql query

Hi Sorry mate i figured out where the problem is

i need to query to look for records that does not contain '%Pharmacy%',

SELECT C1.*

FROM Goldmine_MIOA.dbo.CONTACT1 AS C1 WITH (NOLOCK)
INNER JOIN (SELECT DISTINCT ACCOUNTNO
FROM GoldMine_GMBase.dbo.CAL as CAL
WHERE CAL.ONDATE BETWEEN '20111111' AND '20111111' AND
CAL.RECTYPE in ('S')
) CL ON
CL.AccountNo = C1.AccountNo

INNER JOIN (SELECT DISTINCT ACCOUNTNO
FROM Goldmine_MIOA.dbo.CONTHIST as CONTHIST
WHERE CONTHIST.ONDATE BETWEEN '20111111' AND '20111111' AND
CONTHIST.REF LIKE '%Pharmacy%'
) CH ON
CH.AccountNo = C1.AccountNo

i need to query to look for records that does not contain '%Pharmacy%',

Then your code should be :

Code:

SELECT C1.*
FROM Goldmine_MIOA.dbo.CONTACT1 AS C1 WITH (NOLOCK)
INNER JOIN (SELECT DISTINCT ACCOUNTNO
FROM GoldMine_GMBase.dbo.CAL as CAL
WHERE CAL.ONDATE BETWEEN '20111111' AND '20111111' AND
CAL.RECTYPE in ('S')
) AS CL ON
CL.AccountNo = C1.AccountNo
INNER JOIN (SELECT DISTINCT ACCOUNTNO
FROM Goldmine_MIOA.dbo.CONTHIST as CONTHIST
WHERE CONTHIST.ONDATE BETWEEN '20111111' AND '20111111' AND
CONTHIST.REF NOT LIKE '%Pharmacy%'
) AS CH ON
CH.AccountNo = C1.AccountNo
ORDER BY C1.CONTACT

With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages