| Author |
Beginner seeks help with MySQL query
|
|
|
|
I have two tables, one containing the main database I want to search and
another table containing, amongst other things, a list of fields that I
want to use in my query.
So I want to be able to do a search that will select records from table
1 where various search criteria are met AND one of the fields is equal
to the one of the fields specified in table 2.
For example, lets say table 2 looks like this:
Key field1 field2
2343/6 text text
1432/5 text text
3243/4 text text
And table 1 is like this:
Key field1 field2 field3 etc.....
1 text text 1432/5
2 text text
So then I want to select records from table 1 where field1 = "whatever"
and field2 = "whatever" AND the value of field3 is equal to any one of
the keys in table 2.
Is there a way of doing this?
Thanks in advance for any help.
| |
|
|
|
|
| J.O. Aho 2005-01-18, 8:58 am |
| Ian wrote:
> Thanks for replying. Don't suppose you could be a bit more specific as
> I took a look at the links you provided but still couldn't see how to do
> it.
from http://dev.mysql.com/doc/mysql/en/JOIN.html:
SELECT * FROM table1 LEFT JOIN table2 ON table1.field3=table2.Key WHERE
table1.field1 = "whatever" AND table1.field2 = "whatever" AND table1.field3 IS
NOT NULL;
| |
|
| On Tue, 18 Jan 2005 12:15:31 +0100, "J.O. Aho" <user@example.net> wrote:
>Ian wrote:
>
>
>from http://dev.mysql.com/doc/mysql/en/JOIN.html:
>SELECT * FROM table1 LEFT JOIN table2 ON table1.field3=table2.Key WHERE
>table1.field1 = "whatever" AND table1.field2 = "whatever" AND table1.field3 IS
>NOT NULL;
OK, many thanks for providing the example. I tried it out and seem to
have got very close to making it work except what is happening is that I
am getting all the records returned irrespective of whether field3 is
one of the keys in table 2 except that field3 is only shown in the
search results where it was a key in table 2.
i.e.
Results:
field3 field_x field_y etc
1234/1 blah blah
1234/1 blah blah
blah blah
blah blah
1234/1 blah blah
A portion of the actual code I am now using is:
$sql ="SELECT * FROM census_recs LEFT JOIN public ON
census_recs.piece_no=public.piece_no WHERE 1=1";
if($_GET["piece_no"]) $sql .= " AND census_recs.piece_no =
'".$_GET["piece_no"]."'";
if($_GET["folio"]) $sql .= " AND census_recs.folio =
'".$_GET["folio"]."'";
if($_GET["surname"]) $sql .= " AND census_recs.surname LIKE
'%".$_GET["surname"]."%'";
if($_GET["forename"]) $sql .= " AND census_recs.forename LIKE
'%".$_GET["forename"]."%'";
$sql .= " AND census_recs.piece_no IS NOT NULL";
In the code above table1 is 'census_recs' and table2 is 'public'.
'piece_no' corresponds to field3 in my original example and is also the
name of the key in table2. The search values are passed into the
routine via a form.
I was wondering whether I had might have made a mistake in interpreting
your example ?
| |
| J.O. Aho 2005-01-18, 8:59 pm |
| Ian wrote:
> On Tue, 18 Jan 2005 12:15:31 +0100, "J.O. Aho" <user@example.net> wrote:
>
>
>
>
>
> OK, many thanks for providing the example. I tried it out and seem to
> have got very close to making it work except what is happening is that I
> am getting all the records returned irrespective of whether field3 is
> one of the keys in table 2 except that field3 is only shown in the
> search results where it was a key in table 2.
Do a right join then
SELECT * FROM table1 RIGHT JOIN table2 ON table1.field3=table2.Key WHERE
table1.field1 = "whatever" AND table1.field2 = "whatever" AND table1.field3 IS
NOT NULL;
//Aho
| |
|
| On Tue, 18 Jan 2005 14:00:37 +0100, "J.O. Aho" <user@example.net> wrote:
>Ian wrote:
>
>Do a right join then
>
>SELECT * FROM table1 RIGHT JOIN table2 ON table1.field3=table2.Key WHERE
>table1.field1 = "whatever" AND table1.field2 = "whatever" AND table1.field3 IS
>NOT NULL;
>
>
> //Aho
Brilliant ! That worked a treat, thanks for all your help, I really
appreciate it. I'm off to read up about table joins in general and try
and understand them a bit better.
|
|
|
|