Foreign table multiple search

[seavers]

First of all, sorry for the ambiguos title.

 

I have 2 tables in a database. The first is the main table and is called 'freight'. This records the details of a freight run, and has the following schema:

 

  `load_id` int(11) NOT NULL auto_increment,

  `user_id` int(11) default NULL,

  `date_from` int(11) default NULL,

  `date_to` int(11) default NULL,

  `start_destination` varchar(255) default NULL,

  `end_destination` varchar(255) default NULL,

  `full` varchar(4) default NULL,

  `additional_info` text,

  `journey_ref` varchar(255) default NULL,

  `length` int(11) default NULL,

  `width` int(11) default NULL,

  `height` int(11) default NULL,

  `weight` int(11) default NULL,

  `load_type` varchar(255) default NULL,

  `vehicle_type` varchar(255) default NULL,

  `vehicle_load_access` varchar(255) default NULL,

  `published` smallint(1) default NULL,

  PRIMARY KEY  (`load_id`)

 

Note that the start_destination and end_destination are stored here.

 

I then have another table to store possible stop points inbetween, here's the schema:

 

  `stops_id` int(11) NOT NULL auto_increment,

  `load_id` int(11) default NULL,

  `destination` varchar(255) default NULL,

  `destination_date` int(11) default NULL,

  `dest_length` int(11) default NULL,

  `dest_width` int(11) default NULL,

  `dest_height` int(11) default NULL,

  `dest_weight` int(11) default NULL,

  PRIMARY KEY  (`stops_id`)

 

The user will search a start destination and an end destination, however, these could include destinations in the 'stops' table.

 

For example, the user searches Birmingham as the start destination and Carlisle as the end destination. There is a record for load_id '1' that has 2 stops associated with it in the 'stops' tables, which happen to be Birmingham and Carlisle.

 

How would I construct that query so that I would get one result row returned instead of two or three?

 

Here's a query that I have tried, unfortunately it also returns other results if either one of the two search criteria apply to them:

 

SELECT DISTINCT freight.load_id, user_id, date_from, date_to, start_destination, end_destination, full, length, width, height, weight, load_type, vehicle_type, published FROM freight LEFT JOIN stops ON freight.load_id = stops.load_id WHERE (start_destination LIKE '%Birmingham%' OR end_destination LIKE '%Birmingham%' OR destination LIKE '%Birmingham%') OR (start_destination LIKE '%Carlisle%' OR end_destination LIKE '%Carlisle%' OR destination LIKE '%Carlisle%') AND published = '1' AND date_to >= '1263809728' ORDER BY date_from LIMIT 0, 25

 

In the short term I have programatically solved the problem by calling the results based upon just the start destination, and then calling another query on the reasult to see if the end destination is included in the results. If it is, the record is shown, if not, then I move onto the next row. However, this seems to be a haphazard way of doing it.

[fenway]

I'm not sure I follow.. you're looking for unique load_ids?

[seavers]

Thanks for replying.

 

Yes, I would like to return unique load_id's. Here's an example, I'll just cut down the freight table info for convenience:

 

I have a record in freight as follows:

 

load_id = 10

start_destination = Bristol

end_destination = Edinburgh

 

That freight record also has 2 records ion the stops table associated with it:

 

stop_id = 21

load_id = 10

destination = Birmingham

 

stop_id = 22

load_id = 10

destination = Carlisle

 

I would like to query the database, so that if a search is performed with Birmingham and Carlisle as the search parameters, load_id 10 is returned.

 

I've looked at joins and the union statement, but thety either return a null result or multiple rows.

 

[fenway]

Then it sounds like you need to query the stops table, not the loads table.

[seavers]

Ok, if I query the stops table with:

 

SELECT DISTINCT (load_id) FROM `stops` WHERE destination LIKE '%Birmingham%' AND destination LIKE '%Carlisle%'

 

I get no result because a single record only contains one of the destinations.

 

If I use:

 

SELECT DISTINCT (load_id) FROM `stops` WHERE destination LIKE '%Birmingham%' OR destination LIKE '%Carlisle%'

 

I get a result but I also get loads that contain just Carlisle, or just Birmingham.

 

I need to pull results that match both criteria only.

 

Is my logic wrong?

[fenway]

You have two choices:

 

1) get the results with OR, then filter using HAVING and COUNT().

 

2) INNER JOIN in the table to itself, once for each term, and then the only resulting records will have all the terms.

[seavers]

Thank you for your patience fenway.

 

After taking your advice, the following both seem to work:

 

SELECT DISTINCT ( load_id ), COUNT( destination )

FROM `stops`

WHERE destination LIKE '%Birmingham%'

OR destination LIKE '%Carlisle%'

GROUP BY load_id

HAVING COUNT( destination ) > 1

 

 

SELECT DISTINCT (s1.load_id)

FROM stops s1 INNER JOIN stops s2

ON s2.destination LIKE '%Birmingham%' AND s1.load_id = s2.load_id

INNER JOIN stops s3

ON s3.destination LIKE '%Carlisle%' AND s2.load_id = s3.load_id

ORDER BY s1.load_id

 

I feel happier that my problem can be solved with a single sql query rather than having to call results and not display them.

 

Thanks again.