Query help 1

[dday01]

Hi Folks,

I am a total novice. I am trying to write a query with two tables, a client table that has a one to many relationship to an encounter table. I want the query to return distinct client records. The client table has an extensive amount of fields in it and I would like to avoid having to enter each field under the Select statement. I tried the following but it does not seem to work:

SELECT Distinct "ai_clien".*

INTO dbo.ai_clien_prev

FROM "urs"."dbo"."ai_clien" "ai_clien"
INNER JOIN "urs"."dbo"."ai_enc_prev" "ai_enc_prev"
ON "ai_clien"."tc_id"="ai_enc_prev"."tc_id")

Any thoughts on an easy way to do this would be greatly appreciated.

Thanks,

D

 

[donutman]

The key Distinct often confuses even those who are not novices. When you place it in a Select clause, then the check for duplicates is made on each column in the Select clause. So if just 1 of the say 10 columns is different than the row is accepted as distinct. Consequently a SELECT DISTINCT * FROM Table will return all rows except those that are complete duplicates of one another.
BTW, don't use quotes.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[JRtoad]

you could try this...


SELECT *
INTO ai_clien_prev
FROM ai_clien
WHERE tc_id IN (
SELECT DISTINCT a1.tc_id
FROM ai_clien a1
INNER JOIN ai_enc_prev a2
ON ai_clien.tc_id = ai_enc_prev.tc_id)

 

[dday01]

Thanks for the response donutman, Actually I only want distinct intances of the full records from the ai_clien table to go into the new table based on the TC_ID field from the ai_clien table.

When I tried using DISTINCT* in the select statement, I received the following message:

Server: Msg 8163, Level 16, State 3, Line 1
The text, ntext, or image data type cannot be selected as DISTINCT.

I hope this makes sense, and again any help is appreciated.

Thanks,

D

 

[dday01]

Thanks for the response JRToad, I tried that select statement and got the following error:

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

Again, I have to footnote this by saying that I just started using SQL query analyzer yesterday, I am used to using the query grid in ACCESS.

Any help is appreciated.

Thanks,

D

 

[JRtoad]

in your original query you were joining on "urs"."dbo"."ai_enc_prev" . I removed all of the qualifiers in the example that i responded with. try putting them back in to the example i gave you and remove the quotes.

the message you are getting indicates it is not seeing the table. it does exist right?

 

[dday01]

Hi JRtoad, I tried putting the qualifiers back in. The following is the code I submitted, but received the same message:

SELECT *
INTO ai_clien_prev
FROM ai_clien
WHERE tc_id IN (
SELECT DISTINCT a1.tc_id
FROM ai_clien a1
INNER JOIN urs.dbo.ai_enc_prev a2
ON ai_clien.tc_id = ai_enc_prev.tc_id)

I checked and the table is there.

Thanks again for your help, did I do something wrong again?

Thanks,

D

 

[JRtoad]

i had a mistake in my code...

try this

--=======================================
SELECT *
INTO ai_clien_prev
FROM ai_clien
WHERE tc_id IN (
SELECT DISTINCT a1.tc_id
FROM ai_clien a1
INNER JOIN ai_enc_prev a2
ON a1.tc_id = a2.tc_id)

--========================================

 

[dday01]

JRToad,

That worked! Thanks alot for the help! That made my week alot easier.

Thanks,

D

 

[donutman]

I don't think you can use that construct. And certainly not if the table already exists.

[Blue]INSERT[/Blue] [Blue]INTO[/Blue] ai_clien_prev 
   [Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue]   ai_clien
      [Blue]WHERE[/Blue]  tc_id [Blue]IN[/Blue] [Gray]([/Gray]
         [Blue]SELECT[/Blue] [Blue]DISTINCT[/Blue] a1.tc_id
         [Blue]FROM[/Blue]   ai_clien a1 [Blue]INNER[/Blue] [Gray]JOIN[/Gray] ai_enc_prev a2
         [Blue]ON[/Blue] a1.tc_id [Gray]=[/Gray] a2.tc_id[Gray])[/Gray]

But I have serious doubts that this is what you want. This will add only rows whose tc_id is already in ai_clien_prev. What exactly do you want?
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]