|
|
Posted by noone on 03/22/06 23:48
Bob Sanderson wrote:
> I am using the following query to generate a web page. Searchterm is
> derived from a search form.
> $query="select * from jobs, items where jobs.JobNumber like '$Searchterm'
> and items.JobNumber like '$Searchterm'";
> The data from the jobs table goes in a general form describing a specific
> job. The data from the items table goes in a separate form which lists all
> of the items associated with that job. This works fine if there is data for
> the selected job number in both tables but in some cases, the data only
> exists in the jobs table - there is no corresponding data in the items
> table. What I would like in that case is to output the jobs table data and
> simply leave the items output blank, but since there are no records meeting
> the criteria of the query, nothing is selected.
> Is there a way to create a query so that it will do what I want. If not,
> can it be done with an either/or statement?
> Any help will be greatly appreciated.
A general rule is to explicitly specify each column in each field. One
method is a Left Outer Join.
select a.id, a.data1,a.data2,b.data1,b.data2 from
tablea a left outer join tableb b on a.id=b.id where a.id like
('$searchterm')
b.data1 and b.data2 will be NULL if there is no data from items table.
Example:
mysql> select * from c;
+------+
| a |
+------+
| 2 |
+------+
1 row in set (0.01 sec)
mysql> select * from d;
+------+----------------+
| a | b |
+------+----------------+
| 1 | 20060313165232 |
| 1 | 20060313155236 |
| 1 | 20060314215241 |
| 1 | 20060313145251 |
| 2 | 20060321060235 |
| 2 | 20060322020243 |
| 3 | 20060322020254 |
| 3 | 20060322080300 |
| 3 | 20060322100305 |
+------+----------------+
9 rows in set (0.01 sec)
mysql> select d.a,d.b,c.a from d left outer join c on c.a=d.a;
+------+----------------+------+
| a | b | a |
+------+----------------+------+
| 1 | 20060313165232 | NULL |
| 1 | 20060313155236 | NULL |
| 1 | 20060314215241 | NULL |
| 1 | 20060313145251 | NULL |
| 2 | 20060321060235 | 2 |
| 2 | 20060322020243 | 2 |
| 3 | 20060322020254 | NULL |
| 3 | 20060322080300 | NULL |
| 3 | 20060322100305 | NULL |
+------+----------------+------+
9 rows in set (0.01 sec)
Navigation:
[Reply to this message]
|