I have 2 tables :A and B. the relation is 1:* , i.e. to one record in A may relate 0..n records in B. The primary key of A : 2 fields id, version.
B has foreing key in each record , it says to wich record in A this record in B related. I'm trying to implement search engine: user chooses any field of A or B and SQL query shoul find results: all apropriate A records and all apropriate B records to each A.
The following query doesn't work, cause it only finds A records that have 1 or more related B records, and doesn't find A records that have no B records:
SELECT DISTINCT A.* FROM A,B WHERE (A.id=B.id) AND (A.version = B.version) AND (...users constraints...)
The following query doesn't work, cause iit finds right:A records have 1 or more related B records wrong:all A records that have no B records, regardless to user constraints:
SELECT DISTINCT A.* FROM A,B WHERE (...users constraints...)
I'm really frustrated, unsuccessfully writing this query for 2 days
Thank you for help
You need to use a left outer join - also, do you have access to sql server books online ? If not, you need to download it from ms - it will help you with all aspects of sql server (including left outer join).