When using an access database, I would instead of using inner join
syntax (which I normally prefer) put those join conditions in the where
clause...
FROM Constintuency c, PollinstStation s, VoteResults v
WHERE s.ConstituencyNo = c.ConstituencyNo
and v.AgentPhoneNo = v.AgentPhoneNo
and
the following query in ColdFusion but I am getting an error
> buy I can't understand what I am doing wrong. I will appreciate any
> help I can get:
>
> SELECT c.ConstituencyName, s.ConstituencyNo, c.RegionCode, SUM(v.
> Votes) AS TotalVotes
> FROM Constitu
I have the following query in ColdFusion but I am getting an error buy I can't
understand what I am doing wrong. I will appreciate any help I can get:
SELECT c.ConstituencyName, s.ConstituencyNo, c.RegionCode, SUM(v.Votes) AS
TotalVotes
FROM Constituency c
INNER
Replace your Where with an "AND/OR"
The where clause must be used after any inner joins
SELECT v.Party, SUM(v.Votes), s.ConstituencyNo ,c.ConstituencyName, c.
RegionCode
FROM
VoteResults v INNER JOIN PollingStation s
---
ON v.Ag
" and it should work.
>
> Although, in this case, there is no benefit to putting the additional
> criteria within the INNER JOIN. You may as well just specify a WHERE
> clause.
>
> It does make sense, however, to specify additional criteia in an OUTER
> JOIN.
>
> ht
Change your "WHERE" to an "AND" and it should work.
Although, in this case, there is no benefit to putting the additional
criteria within the INNER JOIN. You may as well just specify a WHERE
clause.
It does make sense, however, to specify additional criteia in
> I am trying to use INNER JOIN for my query and have
> additional conditions, what is the right way to add them?
> When I run the code below, I get the error shown. How do I
> get this to work?
>
> SELECT v.Party, SUM(v.Votes), s.ConstituencyNo
> ,c.ConstituencyNa
I am trying to use INNER JOIN for my query and have additional conditions, what
is the right way to add them? When I run the code below, I get the error shown.
How do I get this to work?
SELECT v.Party, SUM(v.Votes), s.ConstituencyNo ,c.ConstituencyName, c.RegionCode
FROM VoteResults v INNER
> I see where I can create the linked server, but the foxpro
> database and the SQL database are on the same server. When I
> enter the name of the server, it says I cannot create the
> local server as a linked server.
My MSSQL and MySQL servers are both on the same box and it works fine here.
Tim Claremont wrote:
> I see where I can create the linked server, but the foxpro database and the
> SQL database are on the same server. When I enter the name of the server, it
> says I cannot create the local server as a linked server.
>
> To clarify, the FoxPro database is in a directory on t
Maybe try by IP?
The linked server thing is neat, I'm going to have to look into it some
more.
> -Original Message-
> From: Tim Claremont [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 28, 2006 1:40 PM
> To: CF-Talk
> Subject: Re: Inner Join Across Two Data
I see where I can create the linked server, but the foxpro database and the SQL
database are on the same server. When I enter the name of the server, it says I
cannot create the local server as a linked server.
To clarify, the FoxPro database is in a directory on the same server as the SQL
Serv
large that they crash cf.
-Original Message-
From: Aaron Rouse [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 28, 2006 11:52 AM
To: CF-Talk
Subject: Re: Inner Join Across Two Data Sources?
How do you setup the link between MS SQL and FoxPro? Seems like that is
the
only way that cou
> Yup. I am aware of that. I was hoping to get away with one
> query, but apparently I am doing it as efficiently as
> possible already.
In enterprise manager go into "security > Linked Servers". I have never
tried with fox pro but I have my MySQL server and MSSQL servers working
together nicel
Yup. I am aware of that. I was hoping to get away with one query, but
apparently I am doing it as efficiently as possible already.
Thanks
Tim
>No, but you could do three queries and end up with the same result using
>query of queries.
>
>>
~~~
How do you setup the link between MS SQL and FoxPro? Seems like that is the
only way that could be done in a single query.
On 9/28/06, Andy Matthews <[EMAIL PROTECTED]> wrote:
>
> Should be able to. Reference the fields like so:
>
> SELECT dt.fieldname
> FROM database1.tab
Should be able to. Reference the fields like so:
SELECT dt.fieldname
FROM database1.table1 d1t1
INNER JOIN database2.table1 d2t1
ON d2t1.id = d2t1.id
-Original Message-
From: Tim Claremont [mailto:[EMAIL PROTECTED]
Sent: Thursday, September 28, 2006 11:15 AM
To: CF-Talk
Subject
No, but you could do three queries and end up with the same result using
query of queries.
> -Original Message-
> From: Tim Claremont [mailto:[EMAIL PROTECTED]
> Sent: Thursday, September 28, 2006 12:15 PM
> To: CF-Talk
> Subject: Inner Join Across Two Data Sources?
I have two databases. One is in SQL Server, and the other is in FoxPro.
Can I perform a single CFQUERY that includes one table in each data source and
joins the two based on say... ClientNum?
~|
Introducing the Fusion Authority
Stuart...
Just FYI...it'll REALLY help with readability, both for you and for us, if
you alias your tables. Like so:
You simply use the AS keyword after the table name in your FROM and INNER
JOIN statements.
FROM tablename AS alias
Or you can do it the easy way and leave off the AS ke
it.
>
> Ian
>
>
>> -Original Message-
>> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
>> Sent: 29 September 2005 12:37
>> To: CF-Talk
>> Subject: Re: INNER JOIN
>>
>> Hi,
>>
>> I've simplified it a bit:
>>
>>
>
You need to use something like ORDER BY authorID
if you're grouping on it.
Ian
> -Original Message-
> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
> Sent: 29 September 2005 12:37
> To: CF-Talk
> Subject: Re: INNER JOIN
>
> Hi,
>
cleID,
tbl_020articleDetails.articleTitle
FROM tbl_020articleDetails
INNER JOIN tbl_020authorDetails ON
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
WHERE tbl_020authorDetails.authorPhotograph <> '' AND
tbl_020articleDetails.articleSubTypeID <>
A quick suggestion, write a test page with the minimum of code in. It'll
make it easier for you to debug and easier for us to help.
Ade
-Original Message-
From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
Sent: 29 September 2005 10:50
To: CF-Talk
Subject: INNER JOIN
Hi,
I&#x
rname,
tbl_020authorDetails.authorPhotograph,
tbl_020authorDetails.authorAboutMe,
tbl_020articleDetails.articleID,
tbl_020articleDetails.articleTitle
FROM tbl_020articleDetails
INNER JOIN tbl_020authorDetails ON
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
> From: Daniel Farmer [mailto:[EMAIL PROTECTED]
> I am getting a 'missing operator error below'. I don't know what
> operator its looking for.
You and Protoculture working the same code or what?
~|
Find out how CFTicket can i
m_categories_sub4.id
INNER JOIN auction_item_categories_sub3 ON
auction_item_categories_sub4.categoryy_id =
auction_item_categories_sub3.id
INNER JOIN auction_item_categories_sub2 ON auction_i'.
Attach Code
SELECT id, category_id, name
FROM auction_item_categories_sub5,
auc
...that's not good...Re: Pagination (Next n records) fo r
an inner join and grouped output...
No you can't. When you use aggregate functions in an SQL statement you MUST
put all of the fields you are selecting into the GROUP BY clause. That
means you will need two queries since you want to s
l Message-
From: Jeff [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 12, 2003 2:03 PM
To: CF-Talk
Subject: Re: Arrrgh...that's not good...Re: Pagination (Next n records)
fo r an inner join and grouped output...
on 6/12/03 4:47 PM, Bryan Love at [EMAIL PROTECTED] wrote:
> Post the code y
't know how many total
> pages there are.
Can I somehow calculate that DURING the inner join query below? I tried to
add it to the SELECT statement with disastrous results...
SELECT *
FROM (tblRentalLocations INNER JOIN tblRentalProperties ON
tblRentalLocations.LocationID=tblRental
a second query with the same criteria, but just query for that one
> field and do a DISTINCT on it, then your recordcount for that query should
> be the number of distinct values for that field.
Man, I totally tried this, but my query just gave the most horrible
errors...
Watch...
SELECT *
FR
merican Crisis
"Let's Roll"
- Todd Beamer, Flight 93
-Original Message-
From: Jeff [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 12, 2003 12:08 PM
To: CF-Talk
Subject: Arrrgh...that's not good...Re: Pagination (Next n records) for
an inner join and grouped
>Is there some way to calculate #DistinctPropertyCount# some way from the
>results of the query? In this case, the RecordCount is correct (10 rows
>returned) but what I *really* need is the number of *properties* that are
>returned...
You could create a structure, with keys being the value of the
Okay, how about this...
Is there a function or a combination of functions that I can use on the
query results to find the distinct PropertyIDs from a join query?
Say my query returns 10 rows. 2 distinct properties, with 6 rates for one,
and 4 rates for another...so grouped output looks like this:
on 6/12/03 2:34 PM, Bryan Love at [EMAIL PROTECTED] wrote:
> I've run into this one before and it sucks. Your best bet, if you want the
> number of unique property ids is to run another query that does a
> SELECT COUNT DISTINCT propertyID AS pCount...
>
> But whether or not it will work to use t
Talk
Subject: Pagination (Next n records) for an inner join and grouped
output...
I've got an inner join that I'm trying to make work with paging the records
and I *think* I see what the problem is, but I don't know how to proceed...
The inner joins are working correctly. In fact,
: Thursday, June 12, 2003 11:22 AM
To: CF-Talk
Subject: Pagination (Next n records) for an inner join and grouped output...
I've got an inner join that I'm trying to make work with paging the records
and I *think* I see what the problem is, but I don't know how to proceed...
The
I've got an inner join that I'm trying to make work with paging the records
and I *think* I see what the problem is, but I don't know how to proceed...
The inner joins are working correctly. In fact, when I go to output, I
simply group them by property ID, and I get the right numbe
> What's the deal with ( tblEmployee.EmployeeID IS NOT NULL ) ?
> Shouldn't your ON clause be relating one row in one table
> to a row in another table?
It depends on what you're trying to accomplish with the query -- and it
turns out it doesn't work for him I _think_ because he's using Access .
ent: Sunday, June 30, 2002 9:57 AM
Subject: INNER JOIN
> can you see whats wrong with this query?
> error below
> thanks
> -paul
>
>
> SELECT tblRelations.Relation_ID,
> tblService.Service_ID, tblService.Service,
> tblEmployee.EmployeeID,
> LEFT(LTRIM(tblEmplo
LEFT(LTRIM(tblEmployee.FirstName),1) + '.' AS
employeeinitial
FROM tblService
INNER JOIN tblEmployee ON ( tblEmployee.EmployeeID IS NOT NULL )
LEFT JOIN tblRelations ON (
tblRelations.EmployeeID = tblEmp
SELECT acs.ownerid, sts.siteid, sts.sitename
FROM Accounts acc
INNER JOIN AccountSites acs ON (acc.AccountID = acs.AccountID)
INNER JOIN Sites sts ON (acs.OwnerID = sts.OwnerID)
WHERE acs.Accountid =
--
Andrew Ewings
Project
Does anyone know the SQL to retrieve the OwnerID, SiteID and SiteName given
a single AccountID value using the INNER JOIN syntax? (don't want to use =
in the WHERE clause) Thanks if you can help!
Accounts
AccountID PK
FullName
AccountSites
Acco
43 matches
Mail list logo
