I think you want to use LEFT JOIN, similar to the following.
If you have multiple rows in table two for a row in table 1,
then you will get multiple rows on output. If you have no
rows in table 2 for a row in table 1, then you will get one
row of output where sh_dwg_no IS NULL.
SELECT
one.dwg_no, title, plant, date, sh_dwg_no
FROM
one LEFT JOIN two USING (dwg_no)
ORDER BY
date, plant, title, sh_dwg_no
Tim
On Sat, May 15, 1999 at 12:08:20AM +0100, Richard Skelton wrote:
> Hi,
> I have 2 tables:-
> 1
> +--------+-----------------+-------+--------+
> | dwg_no | title | plant | date |
> +--------+-----------------+-------+--------+
> 2
> +--------+-----------+
> | dwg_no | sh_dwg_no |
> +--------+-----------+
>
> Table 1 has 18000 row, each dwg_no is unique . Table 2 has 600 rows
> Each dwg_no in table 2 has a dwg_no in table 1.
> I want to search table 1 for matches in dwg_no,title,plant then display :-
> +--------+-----------+-----------------+-------+--------+
> | dwg_no | sh_dwg_no | title | plant | date |
> +--------+-----------+-----------------+-------+--------+
>
> All searches need return dwg_no,title,plant,date but if dwg_no is in both table
> I want the sh_dwg_no as well.
>
> Can I use a SQL statement to achieve the above?
