Problem with Join statement?

[kessa]

Hi All,

I've just updated my search facility to include some additional levels (it used to just have country & region, and I've recently added subregion and town) but the search no longer seem to work.

I've written out the SQL for both and it looks OK so I'm not sure if I've done something wrong within my JOIN statements.

Here's the output which worked OK:


SELECT * FROM (ads INNER JOIN countrylist ON ads.countryidx = countrylist.countryidx) INNER JOIN regionlist ON regionlist.regionidx = ads.regionidx WHERE ( ads.countryidx LIKE '%') AND (ads.regionidx LIKE '%') AND ( cansleep >=1) AND ( cansleep <=500) AND ( ads.adtype LIKE '%') AND expires>#20 June 2006# AND active='Y' ORDER BY pricesfrom ASC, countryname ASC, regionname ASC


...and here's the new output which doesn't seem to work...


SELECT * FROM ((((ads INNER JOIN countrylist ON countrylist.countryidx = ads.countryidx) INNER JOIN regionlist ON regionlist.regionidx = ads.regionidx) INNER JOIN subregionlist ON subregionlist.subregionidx = ads.subregionidx) INNER JOIN townlist ON townlist.townidx = ads.townidx) INNER JOIN prices ON prices.adidx = ads.adidx WHERE ( ads.countryidx LIKE '%') AND (ads.regionidx LIKE '%') AND (ads.subregionidx LIKE '%') AND (ads.townidx LIKE '%') AND ( cansleep >=1) AND ( cansleep <=500) AND ( ads.adtype LIKE '%') AND expires>#20 June 2006# AND active='Y' ORDER BY pricesfrom ASC, countryname ASC, regionname ASC


(you may find it easier to copy to textpad or something to compare them site by side)

It's my first time using so many JOINS and so I'm not sure if I've done something wrong?

Any ideas?

Cheers
Kessa

[lmf232s]

Give this one a shot.
You had your joins backworks

SELECT *
FROM ads
INNER JOIN countrylist ON ads.countryidx = countrylist.countryidx
INNER JOIN regionlist ON ads.regionidx = regionlist.regionidx
INNER JOIN subregionlist ON ads.subregionidx = subregionlist.subregionidx
INNER JOIN townlist ON ads.townidx = townlist.townidx
INNER JOIN prices ON ads.adidx = prices.adidx
WHERE ads.countryidx LIKE '%'
AND ads.regionidx LIKE '%'
AND ads.subregionidx LIKE '%'
AND ads.townidx LIKE '%'
AND cansleep >=1
AND cansleep <=500
AND ads.adtype LIKE '%'
AND expires>#20 June 2006#
AND active='Y'
ORDER BY pricesfrom ASC, countryname ASC, regionname ASC

[kessa]

Hi lmf232s,

Thanks for that (I didn't know that the order in which each was declared could have an impact so that's handy to know).

I gave it a shot but unfortunately I am still getting the same problem.
What is really confusing is that I'm not getting an error message as such... it's just that the search is returning zero results..... even though I know that there are results which should be returned.

Any other ideas?

Cheers
Kessa

[lmf232s]

Well i would start by taking out the new where statments that you added on this new query. See if that returns anythings. Look at the data and then see if its visible why those other where clauses filtered that data.

It could also be a bad join.

[russell]

what db?

[kessa]

I've taken each of the JOINS off one by one and it only works when I get back to the original code. I guess the good news is that indicates it's something wrong with the JOIN?

The following is the original join statement (minus the stuff after the WHERE statement, and reorganised as suggested in your earlier post)


SELECT * FROM
(ads INNER JOIN countrylist ON ads.countryidx = countrylist.countryidx)
INNER JOIN regionlist ON ads.regionidx = regionlist.regionidx


and here's the new statement with all of the JOINS which doesn't work:


SELECT * FROM
((((ads INNER JOIN countrylist ON ads.countryidx = countrylist.countryidx)
INNER JOIN regionlist ON ads.regionidx = regionlist.regionidx)
INNER JOIN subregionlist ON ads.subregionidx = subregionlist.subregionidx)
INNER JOIN townlist ON ads.townidx = townlist.townidx)
INNER JOIN prices ON ads.adidx = prices.adidx


I notice in your code that you haven't used the brackets () - is that something I can do away with or is that just to make the code more readable?

Any other ideas?
(For once I would actually be pleased to see an error message! :rolleyes: )

Kessa

[kessa]

yippee - may have just found out what's causing the problem (now just need to find howto fix it) :D

It seems that the problem is as follows:

At the moment none of the ads contain data for the new destinations (subregionidx / townidx)... which is logical as they have just been added, and so at the moment they just have a default value of "0".

Prior to adding the new destinations, if a country or region had a value of "0" then the LIKE command still worked and so I had expected the new bits to do the same.

Anyway, I've just manually added a value for the subregionidx and townidx of one of the ads and it's now pulling through so I guess I need to know how to handle any "0" values (which there will be plenty of)

My understanding is that the LIKE '%' bit would return all results, but perhaps this is not the case?

Russell - I'm using an access db

Kessa

[russell]

msaccess requires the parentheses the way u did it. lmf232s demonstrated mssql syntax -- no parentheses.

any place you have LIKE '%'
just remove that part of the where clause

WHERE cansleep >=1
AND cansleep <=500
AND expires>#20 June 2006#
AND active='Y'

If you are joining on anything that is empty, you will get no results. may need to do LEFT OUTER JOIN, instead of INNER JOIN. all depends on your data and what you are trying to get from the db

[kessa]

If you are joining on anything that is empty, you will get no results. may need to do LEFT OUTER JOIN, instead of INNER JOIN. all depends on your data and what you are trying to get from the db


What do I need to look out for so that I know whether LEFT OUTER JOIN is required?

Cheers
Kessa

[kessa]

Yipee!!! - sorted... LEFT OUTER JOIN did the job a treat.

Thanks to you both SO much :D
Kessa

[kessa]

...... spoke too soon...... :rolleyes:

Although the results are now being displayed, the countryidx, regionidx, subregionidx and townidx values from my breadcrumb trails no longer seem to be pulled through from the db - however, all of the rest of the advert info (text, images, etc) is pulling through fine.

I've tried changing LEFT OUTER JOIN to LEFT JOIN to see if that makes any difference but I still get no values in the breadcrumbs.

I'm a little confused as I can't figure out why it would get some info for each ad, but not these specific values?

Any ideas?

UPDATE:
I've just striped the Statement back to only include the countryidx and regionidx (but keeping the LEFT OUTER JOIN) and it worked fine, so it looks like it's still an issue with joining the new destinations with zero values.

I'm not sure why this would stop the countryidx and regionidx from being displayed though?


Thanks
Kessa

[lmf232s]

Well if i understand you correclty and like russell commented on earlier
If you are joining on anything that is empty, you will get no results

This is because there is nothing to join on.

At least this is what i think. With out actually seeing the tables and the data im just taking a guess at the problem. I would assume that if you add some records to those tables that have no records, you should then see the data being pulled.

[kessa]

Ther is data in the tables that I am joining, it's just that in the main table for the ads, some of the levels have/will have a value of zero (and so don't/won't)

Here's an example of the ad table:

adidx countryidx regionidx subregionidx townidx
1 23 67 0 0
2 23 67 12 0
3 23 67 12 17


In the case of ad1 - there is a country and region but nothing else (this represents how the system is at the moment)

Ad2 represents an ad which does have a subregion, but not a town

Ad3 represents an ad which has all destinations completed.

At the moment, none of the breadcrumbs work (even though the search does) so using the example above it no longer pulls the countryidx to generate the "countryidx=23" link/part of the breadcrumb, and no longer pulls the regionidx to generate the "regionidx=67" link when using the "region" part of the breadcrumb (both of which have values and worked before the new JOINS were added)

Sorry if I'm being a bit slow on the uptake on this. I'm still fairly new to .asp and can't get my head around why values which have worked previously, no longer work :o .

If it would help, I can put up some examples of the structure of the country / region / subregion and town tables?

Cheers
Kessa

[lmf232s]

Not sure, the LEFT OUTER JOIN should pull the data the way you want even though the records have a 0 as its id.

Can you post your sql statement again?

[kessa]

Sure,

strsql= "SELECT * FROM ((((ads
LEFT OUTER JOIN countrylist ON ads.countryidx = countrylist.countryidx)
LEFT OUTER JOIN regionlist ON ads.regionidx = regionlist.regionidx)
LEFT OUTER JOIN subregionlist ON ads.subregionidx = subregionlist.subregionidx)
LEFT OUTER JOIN townlist ON ads.townidx = townlist.townidx)
LEFT OUTER JOIN prices ON ads.adidx = prices.adidx"


Cheers
Kessa

[kessa]

Hi Russell / lmf232s,

Just wondered if you may have any other ideas as I'm stumped?

Cheers
Kessa

[lmf232s]

Kessa,
The query looks fine except for it looks like you dont have enough ( ) going around the whole thing. I dont write queries for access so im not familiar w/ putting () around the joint statements. Because of this im not sure if the brackets should start before the From Table or after the From Table which may or may not be causing the problem.

I see russell is reading this at the same time im writing this so he might have a solution for you.

[russell]

what data types for these fields? adidx ,countryidx, regionidx, subregionidx, townidx

if these are integers, then you should never be using like in your where clause, you should be using mathmatical comparisons < > = <= >= and so on.

other than that, i can't spot it. what are you expecting the query to return?

Can you post the fiull schema and maybe 1 to 3 lines of sample data for each table in the join?

[kessa]

Hi Russell,

what data types for these fields? adidx ,countryidx, regionidx, subregionidx, townidx

In the ads table, all of the items ending "idx" are numbers. In each of the destination relational tables the "idx" are autonumbers.

if these are integers, then you should never be using like in your where clause, you should be using mathmatical comparisons < > = <= >= and so on

I've been using "like" up until now for the countryidx & regionidx JOIN and it's worked a treat (although thanks for letting me know that it's not the right way of doing it) - the other reason I've use it is because it allows me to select all results if one has not been selected.

I've been doing a bit more playing around and it only seems to include the idx in each of the breadcrumbs if all levels have a value greater than zero.

The problem is that not all ads will need all destination levels and also means that all of the ads which previously only had just countryidx and regionidx no longer have functioning breadcrumbs (at any level - not just the new levels).

I've got an "if" statement which only displays the appropriate level in the breadcrumb, if a value greater than "0" - this seems to work fine. It only displays the breadcrumb if the value is greater than "0" but doesn't add the value to the link.

I think I need a beer (or three!)
Kessa

[lmf232s]

well in reading what you just said, could it be that the query is returning the correct data but your if statment that builds the breadcrumb is having the issues?

Can you post the if statment that creates this breadcrumb along w/ what russell asked for?

[kessa]

Sure, the code for the breadcrumb is:


<a href="results.asp?countryidx=<%= rs_ad_list("countryidx")%>"><%= rs_ad_list("countryname")%></a> &gt; <a href="results.asp?countryidx=<%= rs_ad_list("countryidx")%>&amp;regionidx=<%= rs_ad_list("regionidx")%>"><%= rs_ad_list("regionname")%></a>

<% if rs_ad_list("subregionidx")>"0" then %>
&gt; <a href="results.asp?countryidx=<%= rs_ad_list("countryidx")%>&amp;regionidx=<%= rs_ad_list("regionidx")%>&amp;subregionidx=<%= rs_ad_list("subregionidx")%>"><%= rs_ad_list("subregionname")%></a>
<% end if %>

<% if rs_ad_list("townidx")>"0" then %>
&gt; <a href="results.asp?countryidx=<%= rs_ad_list("countryidx")%>&amp;regionidx=<%= rs_ad_list("regionidx")%>&amp;subregionidx=<%= rs_ad_list("subregionidx")%>&amp;townidx=<%= rs_ad_list("townidx")%>"><%= rs_ad_list("townname")%></a>
<% end if %>

Just to recap, the above code works fine if:
a) a value great than"0" exists for an adfor all levels or
b) if I only join the countryidx and regionidx tables to the main ad table.

Regarding the Schema, does this help at all (the tables are quite large so I've only included bits which are relevant to the JOIN)?


Ad table

countryidx regionidx subregionidx townidx
(number) (number) (number) (number)
26 79 30 30 breadcrumb works OK for all levels (With the new JOIN Statement)
17 56 0 0 breadcrumb doesn't work at any level (with the new JOIN statement / Works with the old JOIN Statement)


Country table

continentidx countryidx countryname
(number) (autonumber) (text)
2 26 USA
1 17 Spain


Region table

countryidx regionidx regionname
(number) (autonumber) (text)
26 79 Florida
17 56 Andalucia


SubRegion table

countryidx regionidx subregionidx subregionname
(number) (number) (autonumber) (text)
26 79 30 Orlando
17 56 N/A N/A (although there are subregions in this level, none are currently in use by any of the ads)


Town table

countryidx regionidx subregionidx townidx townname
(number) (number) (number) (autonumber) (text)
26 79 30 30 Davenport
17 56 N/A N/A N/A (although there are towns in this level, none are currently in use by any of the ads)


Thanks for all of you continued help, patience and persistance with this - I really appreciate it!

Kessa

PS - I went on the W3schools website last night to see if I could figure out how to use the CREATE VIEW command to return the results in a table format (I thought I remembered hearing that this coud be done somewhere a while ago, so thought it may help) but I couldn't get the statement to work... it wouldn't surprise me if I got the sytax all wrong!
If you think that may help then I'll have another go.

[kessa]

Hi Russell / lmf232s (or anyone else - feel free to join in! :D ),

Any new thoughts?

Cheers
Kessa

[lmf232s]

kessa,
I have not forgot about you. I was out of town this weekend so it was time to play, not work. Unfortuanitly i have a lot of work to do today and dont have the time i would like to look at what you posted. Although what you posted looks like it should help. I was actually going to bump your post this morning to let you know.

I will on the other hand attempt to look at it tonight unless someone else beats me to it.

[kessa]

play......play!!!! you lucky sod ;) :D

Sure, no worries - don't work too hard!

[lmf232s]

Kessa,

Lets try to limit the amount of fields you are returning. Your current query returns the same field 5 times.


SELECT ads.Countryidx,
ads.regionidx,
ads.subregionidx,
ads.townidx,
countrylist.countryname,
regionlist.regionname,
subregionlist.subregionname,
townlist.townname
FROM ads
LEFT OUTER JOIN countrylist ON ads.countryidx = countrylist.countryidx
LEFT OUTER JOIN regionlist ON ads.regionidx = regionlist.regionidx
LEFT OUTER JOIN subregionlist ON ads.subregionidx = subregionlist.subregionidx
LEFT OUTER JOIN townlist ON ads.townidx = townlist.townidx


Youll need to fix those joins with the proper ()'s. I dont know access syntax.
If you run this query based on the data you supplied you will get

26, 79, 30, 30, USA, FLORIA, ORLANDO, DAVENPORT
17, 56, 0, 0, Spain, Andalucia, NULL, NULL

Both of the IF Statements will only be executed for the first record. If you
want the second record to also drop in the if statment you need to say

if rd_ad_list("subregionidx") >= 0 then

If your also having problems you need to see what values are being returned.
Before your If Statments do something like this

Response.write "IF " & rd_ad_list("SubRegionIdx") & " >= 0 Then <BR>"

This will print out the result and help you to see whats going on.
You might also want to write the values of the other fields as well to see what they are.

[kessa]

:D :D :D

Sorted!!!! (hopefully)
- I just need to do a bit more testing to make sure, but the breadcrumbs are now working again - YIPEE!!!!

Thanks you SO much!

You were right - it seems that something was going wrong when I used the "*" to get the information (not sure why it didn't complain with the original country / region statement but as long as it's working now - who cares!)

For anyone who's been following this - Here's the new select statement which I'm using - not sure if the syntax of the new "ads.*" is correct, but it seems to work.


Oh, and I've highlighted the new code in red.


' updated code - get all info from the ads table, and only the names from the destination tables

strsql= "SELECT
ads.*,
countrylist.countryname,
regionlist.regionname,
subregionlist.subregionname,
townlist.townname
FROM ((((ads
LEFT OUTER JOIN countrylist ON ads.countryidx = countrylist.countryidx)
LEFT OUTER JOIN regionlist ON ads.regionidx = regionlist.regionidx)
LEFT OUTER JOIN subregionlist ON ads.subregionidx = subregionlist.subregionidx)
LEFT OUTER JOIN townlist ON ads.townidx = townlist.townidx)
LEFT OUTER JOIN prices ON ads.adidx = prices.adidx"

God, it seems such a simple thing in hindsight, but something I would never have expected.

Thanks lmf232s - you've made my week! (And thanks Russell for all of your help too - I really appreciate it!)

Cheers
Kessa

[lmf232s]

Kessa,
Im glad you got it working :).

For future reference, when ever possible avoid doing a "Select *" and only select the fields that you need. In your case the "ads.*" is fine because you really do want all the fields from that table (This holds true as long as you displayed all the columns that are in that table. If there are other columns in that table, then change your "ads.*" back to the indivdual names of the columns. Its just good practice, avoids problems, IMO easier to read, and i believe its less over head on the DB.)

Thats it,
Have a good one.