What's wrong with this SQL code?

[Frank123]

I'm using 2 tables main and sites.

Select system from main where main.site_number=sites.site_number and sites.site_name=?

I'm putting this code into the sql statement box of a recordset property. I'm a beginner at SQL so my answer might be obvious but I'm pretty sure the problem is with the main.site_number=sites.site_number.

Both are spelled right.. I'd appreciate any info..

 

[bRycie]

Select system from main
where main.site_number=sites.site_number
and sites.site_name=?

Two things are missing from the script.

1. You need to include the second table in the script as well i.e. site table.

2. The second where clause statment for this sql is incomplete. Therefore it does not produce the result you required. You can either try the script without the second clause or just try to complete it. In your case if there are only two tables, the second clause is not necessary. Your sql should be:

SELECT system, site.site_number, site_name
FROM main, site
WHERE main.site_number = site.site_number

 

[MerlinB]

Just the point 1 is enough. The ? is a parameter, which the page code will supply as
rsRecordset.setParameter 0, 'Fred'
or whatever.

If you use SQLServer or Access, you could try the improved join syntax

Select system
from main
inner join sites
on main.site_number=sites.site_number
where sites.site_name=?

The syntax is vastly superior when you have outer joins:
Select system
from main
left outer join sites
on main.site_number=sites.site_number

(left hand table (main) is always included, even when no related record exists)

or when you want to do something clever:
Select system
from main
left outer join sites
on main.site_number=sites.site_number
and sites.site_name=?

ie: if there is a match, then only select the site record with that name. Almost impossible via the classic Where clause.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)