Tricky SQL

[codejunkie]

Hey all, I'm new here and am impressed so far. I have a problem I can't resolve. I'm developing a site using a mix of VBscript, html, php, and javascript. I have a form with three fields: Name, State, Catagory. I have Two tables that I have to check. Table One houses the Name and Catagories and Table Two has the State info. I've tried using a nested SQL string, but unless I use "In" it just returns a yes/no answer instead of a list of the records containing the pertinent info. Even using "In" I can't seem to get it to work right. Using VBScript, I've used if/then statements to "build" a query string. I can get separate acurate SQL strings that return appropriate records from each of the tables but then I have duplicate records. I tried using a recordset but I don't know how to apply another sql string on an existing recordset. HELP!!?? I'm lost. Any ideas?

An example of a string that works for Table One is:

Select * From Vendor_Accounts Where (Account_Active = 'Yes') And (Public_First_Name Like '%Name%' Or Public_Last_Name Like '%Name%' Or Public_Nick_Name Like '%Name%' Or Public_Business_Name Like '%Name%') And (Product_Catagory Like '%Catagory%')

An example of a string that works for Table Two is:

Select * From Vendor_Itinerary Where (Account_Active = 'Yes') And (Show_State = 'CA')

[demausdauth]

If you haven't tried this already, in which case I apologize ahead of time, you could try a SQL statement like this, I probably don't have everything right. Also, special attention to the List of common fields between the tables, that's where you'll compare the common fields between the tables to each other.

Select VA.*, VI.* FROM Vendor_Accounts AS VA INNER JOIN Vendor_Itinerary AS VI ON (List of common PK fields between the tables exp: VA.VendorNum = VI.VendorNum AND VA.Account_Active = VI.Account_Active) ) WHERE (VA.Account_Active = 'Yes') AND (Public_First_Name Like '%Name%' Or Public_Last_Name Like '%Name%' Or Public_Nick_Name Like '%Name%' Or Public_Business_Name Like '%Name%') And (Product_Catagory Like '%Catagory%')

[wiss.dev]

can you post the rest of the code

[szlamany]

@wiss.dev - this thread is several years old.

If you have a question - ask it - this thread is about "elementary" sql techniques.

You should have started a new thread with details of your own issue.