Query 1 SELECT *

[01310564]

Query 1
SELECT *

FROM table table_1 FULL OUTER JOIN
table table_2 ON table_1.var1 = table_2.var1 AND table_1.var2 = table_2.var2 AND
table_1.var3= table_2.var3
WHERE (table_1.type = 17) AND (table_1.type = 16)



Query 2

SELECT *
FROM table_1
WHERE (table_1.rowid = 17)



The problem with this is that i lose half the records between query 2 and query 1. What i want is all records where the type is 16 or 17 and link them via var1, var2 and var3 if there is a match but display the records even if there isn't.



I've been stuck on this problem for a while now so any help would been much apprecated.

Cheers Hugh

 

[swampBoogie]

select table1.*
 from table1 left join table2
 on table_1.var1 = table_2.var1 
AND table_1.var2 = table_2.var2 
AND table_1.var3= table_2.var3 
AND table_1.type in (16,17)

 

[01310564]

I obviously didn't explain it properly. The important thing is that if var1 2 and 3 match then it has to display it as one record.

I.e. if they match it would produce

table1.* and table2.*

if they didn't match it would display

table1.* and null

null and table2.*

 

[JamesLean]

Try this:

SELECT *
FROM t1 FULL JOIN t2
  ON t1.var1 = t2.var1
    AND t1.var2 = t2.var2
    AND t1.var3 = t2.var3
    AND (t1.type IN (16, 17) OR t2.type IN (16, 17))

--James

 

[01310564]

Thank you James,

Although there is one problem that still remains.

Every record is duplicated the other way around.

i.e.

tbl1.rowid tbl2.rowid
1 2
2 1

any idea how to solve this one?

Cheers Hugh

 

[JamesLean]

Try this:

SELECT *
FROM t1 FULL JOIN t2
  ON t1.var1 = t2.var1
    AND t1.var2 = t2.var2
    AND t1.var3 = t2.var3
WHERE t1.type IN (16, 17)
  OR t2.type IN (16, 17)

--James

 

[01310564]

This doesn't seem to solve the problem any further sugestions?

Cheers

Hugh

 

[JamesLean]

I'm not sure of what the problem is now? I've tried this with some dummy data of my own and it works perfectly. Maybe it would help if you post some sample data of your own and what result you would want to see.

--James

 

[01310564]

SELECT TOP 100 PERCENT *
FROM dbo.rec_data FULL OUTER JOIN
dbo.rec_data rec_data_1 ON dbo.rec_data.Diwor <> rec_data_1.Diwor AND dbo.rec_data.agt_ID = rec_data_1.agt_ID AND
dbo.rec_data.date1 = rec_data_1.date1 AND dbo.rec_data.time1 = rec_data_1.time1
WHERE (dbo.rec_data.Diwor = 239883 OR
rec_data_1.Diwor = 239883) AND (dbo.rec_data.re_info_diwor IN (16, 17) OR
rec_data_1.re_info_diwor IN (16, 17))
ORDER BY dbo.rec_data.agt_ID


Here is the query diwor is the PK and i've limited it to one diwor to make it easier to explain what is happening.

Table 1 Table 2
229205 239883
239883 229205


This displays exactly the same data but switched around therefore doubling the number of records so what this should produce is:

229205 239883
Or
239883 229205

not both.

I hope i have explained this a bit better.

Cheers

Hugh

 

[JamesLean]

I've only just realised you're actually linking one table to itself rather than two separate tables! That explains why you're getting &quot;duplicate&quot; rows.

This works for me:

SELECT TOP 100 PERCENT *
FROM dbo.rec_data r1 LEFT JOIN dbo.rec_data r2
  ON r1.Diwor <> r2.Diwor
    AND r1.agt_ID = r2.agt_ID
    AND r1.date1 = r2.date1
    AND r1.time1 = r2.time1
WHERE r1.re_info_diwor IN (16, 17)
  AND (r1.diwor < r2.diwor OR r2.diwor IS NULL)
ORDER BY r1.agt_ID

--James

 

[01310564]

I think we are getting there but it is still returning about 8000 more records than are in the table when it should be producing less because some of the original records will be combined.

Also I see that in your query it has LEFT JOIN when I type that in it automatically changes it to LEFT OUTER JOIN. Does this mean the same thing?

Sorry I forgot to mention that I’m linking one table to itself!

Cheers,

Hugh

 

[JamesLean]

Can you tell me what the purpose of this query is? I still don't quite understand the logic behind the query and the exact resultset you would want to see?

Can you explain exactly what you're trying to achieve - it may be that there's an easier way to do it than what you're tyring here. eg if you're just trying to find duplicates then there are better methods of doing that.

--James

 

[01310564]

This query is part of an Management information system. Record types 16 and 17 contain diffrent information from diffrent sources broken down by agent (agt_id), date (date1) and half hour slots (time1). What happens is that the a user selects the fields that they wish to view using check boxes on an asp page. If they select fields that contain both types then it will display the fields that they have selected broken for each half hour by agent.

The problem that I have been having is that it works fine if they only select 16 or 17 but if they select both then it only shows the records where agt_id, date1 and time1 match.

We have progresed beyond that stage and the problem now is that the returned record set is larger than than the total number of records in the table and if i put a where caluse in that selects all record types 17 it returns 67000 records when i know that there are only 50905 records of the type 17.

I hope i have explained this a bit better if not we'll try again.

Cheers,

Hugh

 

[JamesLean]

OK, but I don't understand why you are joining the table to itself? That is the reason you are returning multiple rows. What is your logic behind doing that?

--James

 

[01310564]

There are two tables one that stores what the types 16 and 17 mean i.e one might be pay the other the number of calls made for example.

The data is stored in another table which is the one that i'm joining to itself. In a perfect world i would have a diffrent table storing 16 and 17 but this is going to expand to perhaps hundreds of diffrent types.

Because the user has the power to select the types they use and the fields within these types it makes it much eaiser to program the frount end if all the records are stored in one table and then you let the query produce the combined values for 16 and 17 (once i have loaded all the data it could be 16,17,23,30).

I hope this explains the reasoning behind the self join.

cheers

hugh