Cannot figure OR search right

[Voodoo Jai]

Hi all

I am wanting to search two fields for a LOCATION ("Leeds")  that hold the name of a location. But only get an AND search result.

 

Sample data

ID  Field 1          Field2

1    Leeds          Armley

2    Leeds          Leeds

3    Crossgates    Leeds

 

the search only returns ID 2 not all of them, I need to find all the fields that contain "Leeds".

 

Here is the statement I am using

 

SELECT t.`TakeawayID`, t.`Takeaway_Name`, t.`Line1`, `Location`, C.`City`, t.`Post_Code`
FROM takeaway t, location l, City C
WHERE (Location = "Leeds" OR t.City = "Leeds") AND t.`LocationID`= l.`Town_CityID` AND t.`City`= C.`CityID`
ORDER BY `Location`;

[fenway]

Simplify that for us, or post the full table structures... it's hard to guess.

[Voodoo Jai]

First table structure

Takeaway table

+-----------------+---------------------+------+-----+-------------------+----------------+
| Field                 | Type                       | Null   | Key | Default                 | Extra               |
+-----------------+---------------------+------+-----+-------------------+----------------+
| TakeawayID       | int(10) unsigned       | NO    | PRI  | NULL                    | auto_increment |
| Takeaway_Name | varchar(100)            | NO   |        | Takeaway name     |                |
| Line1                | varchar(50)              | NO   |        | Line 1                   |                |
| Line2                | varchar(50)              | YES  |        | NULL                   |                |
| LocationID         | int(10) unsigned        | NO   | MUL | 0                        |                |
| City                  | int(10) unsigned        | NO   | MUL | 0                        |                |
+-----------------+---------------------+------+-----+-------------------+----------------+

Second table

Location table

+-------------------+------------------+------+-----+-------------------+----------------+
| Field                    | Type             | Null | Key | Default           | Extra          |
+-------------------+------------------+------+-----+-------------------+----------------+
| Town_CityID         | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| Location               | varchar(45)      | NO   | UNI |                   |                |
| CountyID              | int(10) unsigned | YES  | MUL | NULL              |                |
| ts                       | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
+-------------------+------------------+------+-----+-------------------+----------------+

 

Third table

City table

+--------+------------------+------+-----+---------------------+----------------+
| Field      | Type                 | Null    | Key | Default                    | Extra          |
+--------+------------------+------+-----+---------------------+----------------+
| CityID   | int(10) unsigned   | NO    | PRI  | NULL                       | auto_increment |
| City      | varchar(45)         | NO    | UNI |                                |                |
| ts         | timestamp          | NO     |       | 0000-00-00 00:00:00 |                |
+--------+------------------+------+-----+---------------------+----------------+

 

I am trying to search for a location that could be in either the

 

takeaway table

locationID or city field.

 

using the details of the location to search for from the

city table and Location table.

I have missed out some of the field in the takeaway table for clarity.

[fenway]

I would UNION each of the individual searches.

 

[Voodoo Jai]

I would UNION each of the individual searches.

 

 

Would i have to put the search term into a variable to then use in another field value search the usese a UNION join.

 

Many thanks

VoodooJai

[fenway]

I'm not sure I understand... it sounds like you're searching two different tables...

[Voodoo Jai]

I am serching one table but I want to find all rows with either a specific value in:

 

FIELD-A or FIELD-B

or in:

FIELD-A and FIELD-B

 

So Sample data that searches for "LEEDS"

 

ID  Field 1          Field2

1    Leeds          Armley

2    Leeds          Leeds

3    Crossgates    Leeds

 

Much appreciate your time to help me

 

VoodooJai

[fenway]

Well, "A or B" will cover "A and B"... try writing this for the single table, and then deal with the joins.