List:General Discussion« Previous MessageNext Message »
From:Richard Skelton Date:May 15 1999 10:14am
Subject:Re: How create a lookup from one table to another?
View as plain text  
Hi Tim,
Thanks for the help I have found :-
select drawing.dwg_no,title,plant,date,sh_dwg_no 
from drawing LEFT JOIN S_H_141 USING (dwg_no) 
where (title LIKE "%FUEL%") AND (plant LIKE "%141%");

gives me :-

+--------+---------------------------------------------------+-------+--------+-
----------+
| dwg_no | title                                             | plant | date   | 
sh_dwg_no |
+--------+---------------------------------------------------+-------+--------+-
----------+
| 192866 | FUEL DRAIN ADAPTOR                                | 141   | 980216 | 
NULL      |
| 192864 | FUEL FEED ELBOW                                   | 141   | 980216 | 
NULL      |
| 192860 | FUEL INLET ADAPTOR                                | 141   | 980316 | 
NULL      |
| 190823 | FUEL LINE PRE RIG ASSY (VIPER)                    | 141   | 941010 | 
538997    |
| 190822 | FUEL SPIKE SYSTEM ASSY                            | 141   | 941010 | 
538993    |
| 190658 | FUELSPIKE ASSY PRE RIG (ADOUR)                    | 141   | 941010 | 
538905    |
| 190651 | ADAPTOR PEGASUS FUEL                              | 141   | 941010 | 
538812    |
| 190650 | FUEL LINE PRE RIG ASSY ADOUR                      | 141   | 941010 | 
538801    |
| 190625 | PEGASUS FUEL SPINDLE ADAPTOR                      | 141   | 941010 | 
538475    |
| 190599 | PRE RIG FUEL ASSY (PEG)                           | 141   | 941010 | 
538330    |
| 190577 | 4.5KW FUEL SPIKING RIG SUPPLY                     | 141   | 941010 | 
538794    |
| 190366 | FUEL SYSTEM PIPE SUPPORT                          | 141   | 941010 | 
537882    |
| 188995 | FUEL SYSTEM LOWER PIPE SUPPORT                    | 141   | 941010 | 
537881    |
| 188994 | FUEL SYSTEM UPPER PIPE SUPPORT                    | 141   | 941010 | 
537880    |
| 188940 | FUEL LINE PRE-RIG (RB199)                         | 141   | 941010 | 
537496    |
| 188664 | ARRGT. OF FUEL SYSTEM IN TP141                    | 141   | 941010 | 
537879    |
| 181800 | DIAGRAMATIC LAYOUT OF FUEL SYSTEM                 | 141   | 900131 | 
NULL      |
| 176550 | ARRGT OF FUEL/WATER PUMP HOUSE TERMINAL BOX       | 141   | 880627 | 
NULL      |
| 176409 | FUEL INHIBITING REPLENISHMENT PUMP CUBICLE        | 141   | 880602 | 
NULL      |
| 176408 | 0.55KW FUEL INHIBITING SYSTEM PUMP (MAIN CUBICLE) | 141   | 880602 | 
NULL      |
| 175451 | ARRGT OF FUEL SYSTEM IN TEST CELL                 | 141   | 880302 | 
NULL      |
| 174778 | ARRGT OF FUEL SYSTEM IN FUEL CUBICLE 11/61        | 141   | 871126 | 
NULL      |
| 173829 | ARRGT OF THRUST LOADING/FUEL INHIBITING PANELS    | 141   | 871222 | 
NULL      |
+--------+---------------------------------------------------+-------+--------+-
----------+
23 rows in set (0.29 sec)

How can I change the order of the columns to :-
+--------+-----------+----------------------------------------+-------+--------+
| dwg_no | sh_dwg_no | title                                  | plant | date   |
+--------+-----------+----------------------------------------+-------+--------+
Again thanks for the help

> Date: Fri, 14 May 1999 16:23:15 -0700
> From: Thimble Smith <tim@stripped>
> To: Richard Skelton <Richard.Skelton@stripped>, mysql@stripped
> Subject: Re: How create a lookup from one table to another?
> Mime-Version: 1.0
> 
> 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?

--
Cheers
Richard.
=
Richard Skelton      |   e-mail : Richard.Skelton@stripped
			 WWW	: http://www.brake.demon.co.uk/
                         Fax    : +44 870 052 1029

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