Noob Needs help JOINing Tables

[SpiderAxiom]

Hi Everyone,
I need some sql help. I am currently using this query:

<%
'***********************************************************************
p = Replace(Request("p"), "'", "''")
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "DSN=testpub; UID=wa"
Set RS = Server.CreateObject("ADODB.Recordset")
SQLS = "SELECT DISTINCT C.c, A.cart "
SQLS = SQLS & "FROM A, B, C "
SQLS = SQLS & "WHERE A.cart like '" & p & "' "
SQLS = SQLS & "AND A.cart = B.Xp "
SQLS = SQLS & "AND B.rel_ID = C.rel_ID "
SQLS = SQLS & "ORDER BY cart "
RS.CursorLocation = 3
RS.CursorType = 0
RS.ActiveConnection=Connection
RS.Open SQLS
'***********************************************************************
%>

It works great but I am trying to join two tables since the data is redundant. A.cart is the same data as C.c (after some relationships - see below). The strange thing is this query works sometimes, so I know I'm close. Does anyone know where i went wrong?

<%
'***********************************************************************
p = Replace(Request("p"), "'", "''")
Set Connection = Server.CreateObject("ADODB.Connection")
Connection.Open "DSN=testpub; UID=wa"
Set RS2 = Server.CreateObject("ADODB.Recordset")
SQLS2 = "SELECT DISTINCT C.c, A.cart "
SQLS2 = SQLS2 & "FROM A "
SQLS2 = SQLS2 & "INNER JOIN B "
SQLS2 = SQLS2 & "ON A.cart = B.Xp "
SQLS2 = SQLS2 & "INNER JOIN C "
SQLS2 = SQLS2 & "ON C.rel_ID = B.rel_ID "
SQLS2 = SQLS2 & "WHERE C.c like '" & p & "' "
SQLS2 = SQLS2 & "ORDER BY cart "
RS2.CursorLocation = 3
RS2.CursorType = 0
RS2.ActiveConnection=Connection
RS2.Open SQLS2
'***********************************************************************
%>

Classic ASP:

<%
CurrentRecord = 0
Do While CheckRS(RS) AND CheckRS(RS2)
%>
<% Response.Write ("RS Cart:") %>
<%= RS("c") %>
<% Response.Write ("<BR><BR>") %>

<% Response.Write ("RS2 Cart:") %>
<%= RS2("cart") %>
<% Response.Write ("<BR><BR>") %>
<%
RS.MoveNext
RS2.MoveNext
CurrentRecord = CurrentRecord + 1
Loop
%>

Any suggestions would be appreciated...Thanks

[chrismartz]

To make things less complex, I have written just the query belowSELECT DISTINCT
C.c
, A.cart
FROM
A
INNER JOIN B
ON A.cart = B.Xp
INNER JOIN C
ON B.rel_ID = C.rel_ID
WHERE
C.c like '" & p & "'
ORDER BY
cart

[SpiderAxiom]

Hey thanks for the clean up that is easier to read. This problem is so bizarre. It consistently works on half the variables (p). How can it work half the time? I mean, it's like you can't change anything because the working half will stop working:)

You can see that the second query is the same as the first but uses joins. So in theory it should work...

This may do more harm than good but I believe what is happening when it doesn't work (doesn't work means rs1 & rs2 don't match), is B.rel_ID = C.rel_ID somehow goes out the window. For example, for one variable of "p" I get rs1 recordcount = 0 and rs2 recordcount = 23. It's almost like the one to many relationship goes to many to one on some recordsets. But don't harp on this; i'm not really sure what's happening...

So, I believe i need some crazy query nesting to solve this. I must, i've tried everything else:)

Help! (Thanks)

[russell]

can u post a few sample records for each table and an example of what the result set should look like. that will make it a lot easier to help get what u want

[SpiderAxiom]

As I was typing this out I realized I should clarify something. I am trying to get a.cart to match C.c in order to use A.description instead of C.Cdesc.

Table: A | Table: B | Table: C
Field: cart ---> | Field: Xp '(one) | Field: c '(many)
Field: description | Field: rel_id ---> | Field: rel_id
| | Field: Cdesc

SQLS2 = "SELECT DISTINCT C.c, A.cart "
SQLS2 = SQLS2 & "FROM A "
SQLS2 = SQLS2 & "INNER JOIN B "
SQLS2 = SQLS2 & "ON A.cart = B.Xp "
SQLS2 = SQLS2 & "INNER JOIN C "
SQLS2 = SQLS2 & "ON C.rel_ID = B.rel_ID "
SQLS2 = SQLS2 & "WHERE C.c like '" & p & "' "
SQLS2 = SQLS2 & "ORDER BY cart "


'(SQLS2 = WHERE C.c like '10000')

RS Recordcount: 24
RS2 Recordcount: 24

CheckRS(RS): True
CheckRS(RS2)True

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

CurrentRecord: 0
RS Cart: 72990
RS2 Cart: 72990

CurrentRecord: 1
RS Cart: 80347
RS2 Cart: 80347

CurrentRecord: 2
RS Cart: 80348
RS2 Cart: 80348

CurrentRecord: 3
RS Cart: 80351
RS2 Cart: 80351

CurrentRecord: 4
RS Cart: 80356
RS2 Cart: 80356

...

CurrentRecord: 23
RS Cart: 80356
RS2 Cart: 80356

'Perfecto. It grabs the duplicate data from RS2, RS fields no longer needed.

'##########################################################

'Now, changed variable RS query removed to allow debug script to loop:

'(SQLS2 = WHERE C.c like '20000')
'Here is the clearest case where the query breaks down because RS Recordcount = 0. That means that there should be no data!
'(...though '20000' does exist in the A table.)

RS Recordcount: 0
RS2 Recordcount: 16

CheckRS(RS2)False
CheckRS(RS2)True
-------------------

CurrentRecord: 0
RS2 Cart: 65925

CurrentRecord: 1
RS2 Cart: 80044

CurrentRecord: 2
RS2 Cart: 80063

CurrentRecord: 3
RS2 Cart: 80128

CurrentRecord: 4
RS2 Cart: 80306

...

CurrentRecord: 15
RS2 Cart: 81100

'Not cool. Arg.

[SpiderAxiom]

Yuk that didn't work, this is a bit cleaner:


Table: A
>Field: cart
Field: description



Table: B
Field: Xp '(one)
>Field: rel_id



Table: C
Field: c '(many)
>Field: rel_id
Field: Cdesc

[russell]

sorry, i'm still having trouble figuring out what u r after...but i'm guessing that u need to LEFT JOIN rather than INNER JOIN...of course, i may be missing the point altogether...

also, what dbms are u using?

[SpiderAxiom]

I tried a couple left joins, still encountered this phenom...

I am using Microsoft SQL Server 2000.