List:General Discussion« Previous MessageNext Message »
From:Thimble Smith Date:May 14 1999 11:23pm
Subject:Re: How create a lookup from one table to another?
View as plain text  
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?
Thread
How create a lookup from one table to another?Richard Skelton15 May
Re: How create a lookup from one table to another?Thimble Smith15 May
Re: How create a lookup from one table to another?Richard Skelton15 May