Match values from database

Complicated matching

[dwalls32]

I've got a problem that I can't seem to wrap my head around.

Currently I'm building an app where admins can post entries to the database describing available shipments. The data looks like this:

ShipID ¦ StartState ¦ StartCity ¦ EndState ¦ EndCity ¦ Equip
------------------------------------------------------------
1 ¦ FL ¦ Tampa ¦ CA ¦ Los Ang.¦ Truck
2 ¦ AL ¦ OZARK ¦ OH ¦ Akron ¦ Truck

Then users who might want to move the load can search by any variable and save their searches as alerts based on any item they want. So they might want to search all loads coming from florida, regardless of any other criteria. That data might look like this

AlertID ¦ StartState ¦ StartCity ¦ EndState ¦ EndCity ¦Equip
-------------------------------------------------------------
1 ¦ ¦ ¦ CA ¦ ¦Truck
2 ¦ FL ¦ ¦ ¦ ¦

What I am having a hard time with is when an admin posts a new shipment into the first table, I want it to show all corresponding alerts. Basically, if a value is present in the alert table, it must match the value being posted in the shipment table. It would be easy if the Alerts had every field filled in, then matching would be easy, but matching only on the present values in Alerts is making it difficult.

For some reason I am stuck on this and would appreciate any help.

Thanks!

[optik]

I'm not sure what the problem is, why would empty fields matter, this would just be the same as an unmatched result.

[d40sithui]

If I were you dwalls, instead of having text and values inside your first and second table, I would use IDs. Basically all states would have an ID, and all cities would have an ID. Equipment will have an ID as well. So you will have 3 more tables. In this way it would be easier to compare field values in all tables and likely make all your queries simpler rather than comparing with uncertain text. However, I'm still not sure what your previous question is asking.