SQL search

Databases
1

I have a table with the following fields:

Developer references Chemicals table
Fixer references Chemicals table

The following statement works fine:

  sql = "SELECT SessionID, Identifier, SessionType.SessionTypeName, Film.*, Vendor.*, CameraID, Date FROM Session "
  sql = sql + "JOIN Chemicals ON DeveloperID = Chemicals.ChemicalsID "
  sql = sql + " WHERE Chemicals.ChemicalsName LIKE('%" + search + "%') "
  sql = sql + "ORDER BY Identifier,SessionType.SessionTypeName"

I can get a match for the Developer field, but not Fixer.

If I try the following code i get an error: ambiguous column name: Chemicals.ChemicalsName

  sql = "SELECT SessionID, Identifier, SessionType.SessionTypeName, Film.*, Vendor.*, CameraID, Date FROM Session "
  sql = sql + "JOIN Chemicals ON DeveloperID = Chemicals.ChemicalsID "
  sql = sql + "JOIN Chemicals ON FixerID = Chemicals.ChemicalsID "
  sql = sql + " WHERE Chemicals.ChemicalsName LIKE('%" + search + "%') "
  sql = sql + "ORDER BY Identifier,SessionType.SessionTypeName"

How can I get it to search the Chemicals table for multiple fields?

2

join chemicals on developerid=chemicals.chemicalsid and fixerid=chemicals.chemicalsid

3

First: don’t do this:

sql=sql+…just to concatenate lines. That’s slow. Use the concatenation character _ (as shown below).

Dave’s proposal will work only if both the developerID and the fixerID are not NULL. But if you have Session records where either of these fields might be NULL things get trickier. But UNION comes to the rescue.

sql = "SELECT SessionID, Identifier, SessionType.SessionTypeName, Film., Vendor., CameraID, Date FROM Session "_

  • "JOIN Chemicals ON DeveloperID = Chemicals.ChemicalsID "_
  • " WHERE Chemicals.ChemicalsName LIKE(’%" + search + "%’) " _
  • “ORDER BY Identifier,SessionType.SessionTypeName” _
  • "UNION "_
    +"SELECT SessionID, Identifier, SessionType.SessionTypeName, Film., Vendor., CameraID, Date FROM Session "_
  • "JOIN Chemicals ON FixerID = Chemicals.ChemicalsID "_
  • " WHERE Chemicals.ChemicalsName LIKE(’%" + search + "%’) " _
  • “ORDER BY Identifier,SessionType.SessionTypeName”

There might be other ways too.

4

Thank you Dave and Maximilian.

5

uhhh… I’m not getting an error, but the search is not returning any results. Here’s the complete code:

  sql = "SELECT SessionID, Identifier, SessionType.SessionTypeName, CameraID, Date FROM Session "_
  + "JOIN SessionType USING (SessionTypeID) "_
  + "JOIN Camera USING(CameraID) "_
  + "JOIN Chemicals ON DeveloperID = Chemicals.ChemicalsID AND FixerID = Chemicals.ChemicalsID "_
  + "JOIN Shop ON DevelopedByID = Shop.ShopID "_
  + "JOIN Film USING(FilmID) "_
  + "JOIN Vendor ON Film.VendorID = Vendor.VendorID "_
  + "WHERE Session.Identifier LIKE ('%" + search + "%') OR "_
  + "Session.Notes LIKE('" + search + "%') OR "_
  + "SessionType.SessionTypeName LIKE ('%" + search + "%') OR "_
  +"Camera.CameraName LIKE ('%" + search + "%') OR "_
  + "Chemicals.ChemicalsName LIKE('%" + search + "%') OR "_
  + "DeveloperTemp LIKE('%" + search + "%') OR "_
  + "DeveloperTime LIKE('%" + search + "%') OR "_
  + "FixerTemp LIKE('%" + search + "%') OR "_
  + "FixerTime LIKE('%" + search + "%') OR "_
  + "Shop.ShopName LIKE('%" + search + "%') OR "_
  + "StopBathTemp LIKE('%" + search + "%') OR "_
  + "StopBathTime LIKE('%" + search + "%') OR "_
  + "Vendor.VendorName LIKE ('%" + search + "%') OR "_
  + "WaterBathTemp LIKE('%" + search + "%') OR "_
  + "WaterBathTime LIKE('%" + search + "%') "_
  + "ORDER BY Identifier,SessionType.SessionTypeName"
6

[quote=38682:@Robert Kamarowski]+ "JOIN Chemicals ON DeveloperID = Chemicals.ChemicalsID AND FixerID = Chemicals.ChemicalsID "_
[/quote]

Is your query supposed to return only records where both developerID and fixerID refer to same chemicalID? If not, then change “AND” to “OR”…

7

Thank you Mikko.