New to SQL

I have joined 2 tables but with one problem:

Both have column called 'ref' which is used for joining.

One has lots of records; the other has only 3.

When records display, I only get the records for the 3. Is there a way to get all the records from the larger table to show, even if not all of them have entries in the smaller table ?

many thanks for any suggestions

Edited by - nomadicweb on 12 May 2003 14:37:19

You need to create an outer loop. this will bring all the records from one table and the result (if they exist or not) from the other.

what database are you using?

Regards
Vince

Visit my home: www.chez-vince.com

VBScript | ASP | HTML | SQL | Oracle | Hosting

I think you need a UNION query.

Something like this would retrieve records with AND without references in the lookup table:

SELECT table1.column1, table1.column2, table1.ref, etc
FROM table1, table2
WHERE table1.ref = table2.ref
UNION
SELECT table1.column1, table1.column2, <b>REF</b>, etc
FROM table1
WHERE NOT EXISTS (Select ref FROM table2)

This probably looks very complicated, so I will try to explain what happens.

<i>First, make sure you replace the bold printed <b>REF</b> to a value you want returned if there is no related record in table2.</i>

Firstly this query selects columns 1 and 2 from table 1, it also selects the REF. The where clause is your bottleneck, it literally says only return records where there is a valid entry for REF in table1. To get the remaining records we do UNION, select which columns and the value we want in our REF location of the returned recordset. Then we tell the query to only add records (union previous query with these records) where there is no valid entry in table2.ref (where not exists). You should now get ALL records returned, with the value you specify in the bold <b>REF</b> to indicate that these are not in the lookup table (table2).

This example uses foreign keys, but should also work with JOINS.

HTH.

with kind regards,

Dennis van Galen
DMXzone Manager
FAQ, Tutorial and Extension Manager


Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5

Oops, offcourse you need to select the REF from table1 or you can never compare it with the one in table2, my mistake.

Edited by - djvgalen on 12 May 2003 17:10:44

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
You need to create an outer loop. this will bring all the records from one table and the result (if they exist or not) from the other.

what database are you using?<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Using MySQL - not sure which of the 2 methods suggested here to follow.

Many thanks for both - am studying 2nd one carefully !

cheers

Edited by - nomadicweb on 12 May 2003 19:00:42