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 will be greatly appreciated.

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 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)

noone <noone@nowhere.com> wrote in
news:c0be99a872d26915da7bafc7d45c7ae6$1@somehostou tintheEther.com:

> 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.

Works great, thanks.