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
