link does not match record using Cross Join

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)

link does not match record using Cross Join

Author

Topic

erico
Starting Member

34 Posts

Posted - 2007-05-18 : 12:11:11

I've been told my problem is with the cross joins but was not given specifics. My query returns a name and a link but the link does not match with the name.

SELECT *, T_Programs.ID AS Expr1, Guests.ProgramID AS Expr2, Guests.GuestName AS Expr3, Guests.GuestDescription AS Expr4, Links.URL AS Expr5, Links.Description AS Expr6 FROM Guests CROSS JOIN T_Programs CROSS JOIN Links WHERE (Guests.ProgramID = '" & id & "')

Below is how it displays in the browser. Notice the link does not reflect anything about the name above.

Mark Crispin Miller author of The Bush Dyslexicon. His new book is Fooled Again: How the Right Stole the 2004 Election and Why They'll Steal the Next One Too (Unless We Stop Them)

Related Links:
# Joan Didion, 'Fresh Air

The link should say...

Mark Crispin Miller author of The Bush Dyslexicon. His new book is Fooled Again: How the Right Stole the 2004 Election and Why They'll Steal the Next One Too (Unless We Stop Them)

Related Links:
# 'Democrats Say 2004 election failed in Ohio'

What exactly is not right with the cross joins?

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-18 : 12:24:06

Why would you expect there to be a connection between the link and the author in a cross join? It gives every possible combination.

I suspect you need a left outer join and there is probably something in the Links table to join on.
You also probably need a join between Guests and Programs

Doesn't this give an awful lot of rows in the result?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

erico
Starting Member

34 Posts

Posted - 2007-05-18 : 12:35:49

Not really. The returns are simply one name and one link but the link doesn't match with the name. The Links table has 4 columns.

LinkID ProgramID URL Description

The Guests table has 4 columns.

GuestID ProgramID GuestName GuestDescription

So maybe I need a left outer join on ProgramID from the links table. What are your thoughts on that?

erico
Starting Member

34 Posts

Posted - 2007-05-18 : 12:45:42

Ok I looked up information about left outer join at http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join

So I tried this in the query editor

SELECT *, T_Programs.ID AS Expr1, Guests.ProgramID AS Expr2, Guests.GuestName AS Expr3, Guests.GuestDescription AS Expr4, Links.URL AS Expr5, Links.Description AS Expr6 FROM Guests
LEFT OUTER JOIN Links ON Guests.ProgramID = Guests.ProgramID

and got this message:

The column prefix 'T_Programs' does not match with a table name or alias name used in the query

erico
Starting Member

34 Posts

Posted - 2007-05-18 : 17:09:35

I realized that I was already querying for the T_Programs table based on the code at the top of my page. So I was being redundant. I have since removed any reference to it in my section of the page. Now I can request one record, one guest and one link. However if there are multiple links for one name then why can I only see one link below the name?

SQL Code:
strSQL = "SELECT *, ProgramID AS Expr1, GuestName AS Expr2, GuestDescription AS Expr3 FROM Guests WHERE (ProgramID = 9734)"

HTML/ASP Display Code:
 
<% Response.Write RS ("GuestName") %> <% Response.Write RS("GuestDescription") %> 
Related Links: 
<li class='basic'><A HREF="<%= RS("URL") %>"><%= RS("Description") %></A></li>

Actual Display when tested:

Mark Crispin Miller author of The Bush Dyslexicon. His new book is Fooled Again: How the Right Stole the 2004 Election and Why They'll Steal the Next One Too (Unless We Stop Them)

Related Links:
* (this is blank)

Here is the structure of the Guests table...

GuestID ProgramID GuestName GuestDescription
1 9726 Joan Didion author of five novels and
8 9734 Mark Miller author of The Bush
9 9734 Andrew Gumbel correspondent for the
10 9734 Walter Mebane professor of government
12 9784 Doris Goodwin author of the Pulitzer Prize
13 9785 Greg Atkinson food writer, chef and regular
15 9785 Tom Douglas chef and owner of many
16 9785 Tamara Murphy executive chef and owner
19 9786 Jane Doe Test Guest Description dated

So bottom line here is...

When I (and yes I know about sql injections) test this page using this address http://IP Address/defaultprogramtest.asp?ID=9734

I see at the bottom of all the normal content:

Mark Crispin Miller author of The Bush Dyslexicon. His new book is Fooled Again: How the Right Stole the 2004 Election and Why They'll Steal the Next One Too (Unless We Stop Them)

Related Links:
* (this is blank)

Is the cause of this my display code or my SQL code?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources