Query assistance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query assistance

Author

Topic

babray
Starting Member

16 Posts

Posted - 2004-07-06 : 17:23:52

Hi!

My SQL skills are basic at best and I'm stumped! I have 3 tables
1. L_Keywords with id number, statement and various yes/no qualifing categories
2. M_Keyword which has an id number, and connection to L_Keywords and to MineralDesc
3. MineralDesc with id number, name and description.

A mineral can have many entries in the M_Keyword table. What I'm trying to do (the CF output works except for picking up and repeating the name x times)is ONLY list the mineral name ONCE!

Here's the current SQL statement in the cf query:
<cfquery name="getAstro" datasource="mmdb">
SELECT *
FROM L_Keywords, M_Keyword, MineralDesc
WHERE (L_Keywords.Astrology = 1)
AND (L_Keywords.id = M_Keyword.Key_ID)
AND (M_keyword.Met_ID = MineralDesc.Met_ID)
</cfquery>

I have tried various combinations of DISTINCT, WHERE EXISTS and anything else that even sounds close!

Would anyone be kind enough to point me in the right direction, please? Like I said, everything works if you want repeating mineral names!

Thanks in advance!!!!

Betty

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 17:26:51

SELECT DISTINCT nd.name
FROM
L_Keywords lk
INNER JOIN M_Keyword mk ON lk.L_Keywords.id = mk.Key_ID
AND lk.Astrology = 1
INNER JOIN MineralDesc md ON mk.Met_ID = md.Met_ID

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

babray
Starting Member

16 Posts

Posted - 2004-07-06 : 17:42:06

Thanks for the quick reply and assist!!! Just to be clear... the lk, md, mk just refer to the tables correct?

I'll give it a try!
Betty

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2004-07-06 : 17:44:04

They are aliases so you don't have to type the entire thing over and over. Also usefull on self joins where the table name is the same but the joining columns are different, makes your t-sql easier to read.

babray
Starting Member

16 Posts

Posted - 2004-07-06 : 17:52:27

Hi!

Here's the code I fleshed out and it is throwing a syntax error somewhere.

SELECT DISTINCT MineralDesc.metName
FROM
L_Keywords
INNER JOIN M_Keyword ON L_Keywords.id = M_Keyword.Key_ID
AND L_Keywords.Astrology = 1
INNER JOIN MineralDesc ON M_Keyword.Met_ID = MineralDesc.Met_ID

Thanks again!!!
Betty

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 17:56:04

Can you tell us what the syntax error is?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

babray
Starting Member

16 Posts

Posted - 2004-07-06 : 18:09:47

Thanks for the alias info... I know just enough to ne real dangerous! That's too cool!
Betty

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 18:11:12

So did you get it to work?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

babray
Starting Member

16 Posts

Posted - 2004-07-06 : 18:14:01

I wish I could! The way the CF errors are done, I've figured that when they say line # that it is anywhere between there and the end of that query! In actuality it is saying an error in the cfquery start line, but it has been working for 3 days now... so go figure!
Betty

babray
Starting Member

16 Posts

Posted - 2004-07-06 : 18:18:01

Here's the cf error message:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''.

The error occurred in C:\CFusionMX\wwwroot\Mystic-Minerals\View\Secure\db_astro.cfm: line 6

4 : </cfif>
5 :
6 : <cfquery name="getAstro" datasource="mmdb">
7 : SELECT DISTINCT md.metName
8 : FROM

And below:
SQL SELECT DISTINCT md.metName FROM L_Keywords lk INNER JOIN M_Keyword mk ON lk.L_Keywords.id = mk.Key_ID AND lk.Astrology = 1 INNER JOIN MineralDesc md ON mk.Met_ID = md.Met_ID
DATASOURCE mmdb
VENDORERRORCODE -3100
SQLSTATE 42000

Maybe that will help.
B

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 18:46:52

You were close. :) When you alias a table, you just refer to it by the alias from that point on.



SELECT DISTINCT md.metName 
FROM 
	L_Keywords lk 
	INNER JOIN M_Keyword mk ON lk.id = mk.Key_ID 
		AND lk.Astrology = 1 
	INNER JOIN MineralDesc md ON mk.Met_ID = md.Met_ID

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

babray
Starting Member

16 Posts

Posted - 2004-07-06 : 18:58:32

Hi!

Okay here's the latest set:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''.

The error occurred in C:\CFusionMX\wwwroot\Mystic-Minerals\View\Secure\db_astro.cfm: line 6

4 : </cfif>
5 :
6 : <cfquery name="getAstro" datasource="mmdb">
7 : SELECT DISTINCT md.metName
8 : FROM

And below:

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

SQL SELECT DISTINCT md.metName FROM L_Keywords lk INNER JOIN M_Keyword mk ON lk.id = mk.Key_ID AND lk.Astrology = 1 INNER JOIN MineralDesc md ON mk.Met_ID = md.Met_ID
DATASOURCE mmdb
VENDORERRORCODE -3100
SQLSTATE 42000

ANd that is directly copying Derrick's code as follows:
<cfquery name="getAstro" datasource="mmdb">
SELECT DISTINCT md.metName
FROM
L_Keywords lk
INNER JOIN M_Keyword mk ON lk.id = mk.Key_ID
AND lk.Astrology = 1
INNER JOIN MineralDesc md ON mk.Met_ID = md.Met_ID
</cfquery>

BOY!!! Do I appreciate this help! Learning curves are about to make me go BALD!
B

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-06 : 19:04:29

Are you using Access or SQL Server??

babray
Starting Member

16 Posts

Posted - 2004-07-06 : 19:20:41

Access.....

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-07-06 : 19:40:27

Well I am a bit rusty with Access but I do not believe the join expression

AND lk.Astrology = 1

is supported by Access. Give the following a try and post any error messages received.

SELECT DISTINCTROW nd.name
FROM L_Keywords lk
INNER JOIN M_Keyword mk ON lk.L_Keywords.id = mk.Key_ID
INNER JOIN MineralDesc md ON mk.Met_ID = md.Met_ID
WHERE lk.Astrology = 1

babray
Starting Member

16 Posts

Posted - 2004-07-06 : 21:25:41

Here's thelatest, and actually the =1 has been working fine. It has always returned the right elements. Just can't get it to quit displaying the multiple names.

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'lk.L_Keywords.id = mk.Key_ID INNER JOIN MineralDesc md ON mk.Met_ID = md.Met_ID'.

The error occurred in C:\CFusionMX\wwwroot\Mystic-Minerals\View\Secure\db_astro.cfm: line 6

4 : </cfif>
5 :
6 : <cfquery name="getAstro" datasource="mmdb">
7 : SELECT DISTINCTROW nd.name
8 : FROM L_Keywords lk

SQL SELECT DISTINCTROW nd.name FROM L_Keywords lk INNER JOIN M_Keyword mk ON lk.L_Keywords.id = mk.Key_ID INNER JOIN MineralDesc md ON mk.Met_ID = md.Met_ID WHERE lk.Astrology = 1
DATASOURCE mmdb
VENDORERRORCODE -3100
SQLSTATE 42000

Thanks again to EVERYONE!
Betty

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 23:22:07

??? I thought my last post solved this for you Betty. You need to change "lk.L_Keywords.id" to just "lk.".

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

babray
Starting Member

16 Posts

Posted - 2004-07-07 : 13:37:25

Well... here's the latest error message and going back to derrick's code:
<cfquery name="getAstro" datasource="mmdb">
SELECT DISTINCT md.metName
FROM
L_Keywords lk
INNER JOIN M_Keyword mk ON lk.id = mk.Key_ID
AND lk.Astrology = 1
INNER JOIN MineralDesc md ON mk.Met_ID = md.Met_ID

INNER JOIN md ON mk.Met_ID = md.Met_ID
</cfquery>

Here's the error message:
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''.

The error occurred in C:\CFusionMX\wwwroot\Mystic-Minerals\View\Secure\db_astro.cfm: line 6

4 : </cfif>
5 :
6 : <cfquery name="getAstro" datasource="mmdb">
7 : SELECT DISTINCT md.metName
8 : FROM

I've tried to chase down info on the message to no avail. I also have tried several variations to no avail. Any suggestions?

Again... MANY THANKS!
B

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-07 : 13:41:23

INNER JOIN md ON mk.Met_ID = md.Met_ID

???? Where in the world did that line come from?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-07 : 13:42:30

yeah...

why aren't you using the QBE Grid?

Brett

8-)

babray
Starting Member

16 Posts

Posted - 2004-07-07 : 13:55:15

To Derrick... that came from the code... both md & mk have a field called Met_ID that unite the 2 tables. And what the heck is "the QBE Grid"????? I might use it but don't have the newbie foggiest what it it.
B

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources