query hanging

[janise]

I have 2 tables: table1 and table2 (simple enough).
Table1's structure is like this:

id identity,
scode text
sdate datetime
loc1 varchar,
loc2 varchar,
direction varchar,
distance

table2 has following structre:
id identity,
scode varchar -> relates to table1 by scode,
filename varchar

scode in table2 can have one or more files.

so when a record is inserted into table2, we could have a value like:

ID Scode filename
1 321 file1
2 321 file2
3 321 file3
4 321 file4
--------------------------------

ID Scode filename
5 322 file5
6 322 file6
--------------------------------
etc, etc

I wrote the following query:

SELECT fctrafic.sitecode,date,location,location2.direction,distance,trafficCountsFiles.filename FROM trafficCountsFiles Left Join fctrafic ON fctrafic.siteCode = trafficCountsFiles.SiteCode
where FileName <> 'z'

But it is taking forever.
Can someone please help me tune this.

Thanks in advance

 

[SQLSister]

The structure you gave us you are joining on two fields which do not have the same datatype. Scode is text in one and varchar in the other. Given what you put as the type of data in it, text is not the right datatype.

You would also need indexes on both tables on scode and on filename in table2.

Also I find it odd that you are returning the sitecode for the right side of the roin rather than the left side of the join. It will be null in some cases or you have your join turned around. Which is the table you want to return all the records from?

Questions about posting. See faq183-874

 

[janise]

sorry, the text data type was a mistake.
I have it correct as varchar on both tables; I just typed it incorrectly.

As far as join is concerned, I thought that was correct.

I want to return everything on the left and whatever matches on the right.

What I forgot to mention on my first post was that the reason I am using a left join is my query was returning a cartesian product.
For instance, I have 3 values for sitecode on table2.
The first sitecode is associated with 3 files;
the second with 2 files and the third with 1 file.
That gives me something like this:

ID Scode filename
1 321 file1
2 321 file2
3 321 file3
--------------------------------
4 322 file4
5 322 file5
-------------------------------
6 321 file6

When I tried to join this table (table2) with the first table (table1)(so I can display the rest of records from table1), I was getting "150 records found"
instead of 6 records found.
That is why I used left join.

I have corrected that and it still hangs.

SELECT table1.sitecode,date,location,location2.direction,distance,trafficCountsFiles.filename FROM table1 Left Join table1 ON table1.siteCode = table2.SiteCode
where FileName <> 'z'

 

[ESquared]

Merely switching to a left join would not fix the cartesian product problem. Something else had to have been going on.

I can barely read sql all jammed together like that.

SELECT
      table1.sitecode,
      date,
      location,
      location2,
      direction,
      distance,
      trafficCountsFiles.filename
   FROM table1
      JOIN table1 ON table1.siteCode = table2.SiteCode 
   WHERE
      FileName <> 'z'

This looks fine. What quantity of records do you have in these two tables? Can both have multiple records with the same siteCode? If so, there's something wrong with the logic you are applying to join them... this join only makes sense if either table1 or table2 will have only ONE record per sitecode.

If both can have multiple records per sitecode, then you need some additional join condition, or you need some aggregation before the join.