Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Output from multiple tables

Status
Not open for further replies.

monoone

Programmer
May 24, 2002
219
US
I think I am missing something:

Here is my code for outputting from multiple tables:

<CFQUERY NAME="GetCandy" DATASOURCE="JamDB" DBTYPE="ODBC">

SELECT Band.*,
Venue.*,
EarCat.*,
EarCandy.*

FROM Band,
Venue,
EarCat,
EarCandy


WHERE Band.BandID = EarCandy.BandID AND
Venue.VenueID = EarCandy.VenueID AND
EarCat.EarCatID = EarCandy.EarCatID

ORDER BY VenueState, VenueCity

</CFQUERY>

-------------------------------------------

Here are the tables:

-------------------------------------------

Band
------

BandID
BandName
BandURL
Style

-------
-------


------
Venue
------

VenueID
VenueName
VenueCity
VenueState
VenueURL
VenuePhone

----------
----------

--------
EarCandy
--------

EarcandyID
VenueID
GigDate
BandID
EarCatID
-----------
-----------


-----------
EarCAt
----------

EarCatID
EarCatName

--------------------------------------

Here is the error:

--------------------------------------

Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Band.BandID = EarCandy.BandID AND Venue.VenueID = EarCandy.VenueID AND EarCat.EarCatID = EarCandy.EarCatID'.



The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (1:1) to (1:58).



-----------------------------------------

Can someone tell me what I am doing wrong?

Thanks,

Eric
 
Given the fact that they all end in ID.. I'm assuming all are numeric? A join must be of compatible data types.. If not, you must use the Convert() Function.. But I'm not sure if that works in Access.. Its been a long time for Access and me..

Lets assume

Venue.VenueID is an numeric/int/autonumber/etc field and EarCandy.VenueID is a text field... you've got to say..

Code:
Venue.VenueID = Convert(EarCandy.VenueID,Number)

Being access, I'm not sure if that will work but these forums don't see much activity on the weekends (most of us, are smart enough to leave work, at work [not me :)]).. And I'd suggest you at least toy with that.

The good news, if that doesn't work, is there are a few much better at sql than I, and one fella in particular (r937) dominates in all sorts of SQL Languages.

ALFII.com
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.
 
Thanks, this is an Access DB - I have never had a problem before. I never had to convert anything before like this. The ID's are a Number field.

-Eric
 
well, my compliment was directed to webmigit, but you're doing okay too, eric :)

your query syntax looks okay, and if all those id fields really are numeric, then i don't know where the error is coming from

try rewriting the SELECT list to specify only the columns that you absolutely need to return to coldfusion

rudy
SQL Consulting
 
oh, I was saying thanks to the reply to the initial question, my coding pretty much sucks really but practice makes perfect (or more confusion).

I will try and re-write the SELECT and not be lazy.

Thanks,

-Eric
 
Since its access.. try running your query IN Access...

That will generally isolate the problem better..

Might help ya a bit..

And Rudy, no problem.. I get SQL enough to do the things I want to do but when I do have an sql question, I come here.

ALFII.com
---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.
 
well, if you guys need an sql answer, you can also try contacting me directly (through the contact form or email on my site)

i don't hang out on forums as much as i used to, and the question may just fly on by without me seeing it...

rudy
SQL Consulting
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top