Any way to re-write this query?

[dickbaker]

I have a site with a classifieds section which, up until now, I wanted to be available only to the store owners who are doing the trial subscription to the site. I'm thinking of opening up the classifieds to be free to anyone who wants to put stuff in there, just to get more classified ads (only two stores have anything at all listed in them now).

Anyway, I set up this query for the classifieds results page:

SELECT dbo.shopowners.shopname, dbo.shopowners.streetaddress, dbo.shopowners.city, dbo.shopowners.state, dbo.shopowners.phone, dbo.classifieds.ID, dbo.classifieds.username, dbo.classifieds.pass, dbo.classifieds.make, dbo.classifieds.model, dbo.classifieds.price, dbo.classifieds.description, dbo.classifieds.description2, dbo.classifieds.description3, dbo.classifieds.phonenumb, dbo.classifieds.emailaddy, dbo.classifieds.photo1, dbo.classifieds.photo2, dbo.classifieds.photo3
FROM dbo.classifieds INNER JOIN dbo.shopowners ON dbo.classifieds.username = dbo.shopowners.username
WHERE dbo.classifieds.ID='MMColParam'

MMColParam is the Request Query String ID of the item that the visitor clicks on.

The idea of joining the two tables was to eliminate the shop owners having to enter their shop name, city, state, phone, and address every time they placed an ad.

If I open the site up for anyone to place ads, I can have a page where people can create their own username and password, and then create their ad. However, the query as it's now written will return an error message because of the inclusion of the info from the shopowners table.

Is there any way to re-write this query so that such an error wouldn't occur? Or do you think I should just add text boxes in the insert record page for shopname, city, state, etc?

Thanks much for any replies.

[MozMan]

Simply change your INNER JOIN to a LEFT OUTER JOIN:

SELECT dbo.shopowners.shopname, dbo.shopowners.streetaddress, dbo.shopowners.city,
dbo.shopowners.state, dbo.shopowners.phone, dbo.classifieds.ID, dbo.classifieds.username,
dbo.classifieds.pass, dbo.classifieds.make, dbo.classifieds.model, dbo.classifieds.price,
dbo.classifieds.description, dbo.classifieds.description2, dbo.classifieds.description3,
dbo.classifieds.phonenumb, dbo.classifieds.emailaddy, dbo.classifieds.photo1,
dbo.classifieds.photo2, dbo.classifieds.photo3
FROM dbo.classifieds
LEFT OUTER JOIN dbo.shopowners ON dbo.classifieds.username = dbo.shopowners.username
WHERE dbo.classifieds.ID='MMColParam'

And that should do the trick. Using the LEFT OUTER allows you to continue to return results from the "left" table even if you can't match a record in the "right" table.

-Moz

[dickbaker]

Thanks, MozMan. It's been a couple of years since I took courses in ASP. Completely forgot about what outer joins are supposed to do. Same thing with the two years of German language I took. ;)

[MozMan]

Kewl! Glad it worked for you.

-Moz