JOIN? or UNION? or ?

[DeathRobot]

Hi. I'm using a global search field ("searchString") and SQL to populate a portal. For example, when searching a People table, I have:

 

SELECT

     id

 

FROM

     People

 

WHERE

     name LIKE %searchString%

          OR

     category LIKE %searchString%

 

This works great. However, I would like to extend the search so that a user could search on a company name and all related people from that company would be returned:

 

SELECT

     id

 

FROM

     People

 

WHERE

     name LIKE %searchString%

          OR

     category LIKE %searchString%

          OR

     [companyName from the related Company table] LIKE %searchString%

 

I've tried every iteration of JOIN, LEFT JOIN, UNION, etc. that I can think of, but cannot get the syntax correct. Any help is very much appreciated!

 

Michael

[Kris M]

try ExecuteSQL ( "SELECT id FROM People WHERE name="searchstring1" OR category='searchstring2' ; "" ; "" )

 

 

Hi. I'm using a global search field ("searchString") and SQL to populate a portal. For example, when searching a People table, I have:

 

SELECT

     id

 

FROM

     People

 

WHERE

     name LIKE %searchString%

          OR

     category LIKE %searchString%

 

This works great. However, I would like to extend the search so that a user could search on a company name and all related people from that company would be returned:

 

SELECT

     id

 

FROM

     People

 

WHERE

     name LIKE %searchString%

          OR

     category LIKE %searchString%

          OR

     [companyName from the related Company table] LIKE %searchString%

 

I've tried every iteration of JOIN, LEFT JOIN, UNION, etc. that I can think of, but cannot get the syntax correct. Any help is very much appreciated!

 

Michael

 

[DeathRobot]

Hi Kris,

 

Thanks for your reply. I may have been unclear in my question. The trouble I'm having is matching records against a related table using just one search string, like so:

 

ExecuteSQL ( "SELECT id FROM People WHERE name="searchstring" OR COMPANYTABLE.companyName='searchstring' ; "" ; "" )

 

The syntax is not correct (that's the issue I'm having!) but I want to pull all id's from the People table where the search string either matches a name in the People Table OR the search string matches a company name in the Company Table. So if I searched on "Bob Smith', Bob's record would be returned. If I searched on Xerox, all the people who were related to Xerox would be returned.

 

Michael

[mr_vodka]

INNER JOIN will give you all the related records that match your parent records. Then you should be able to put both conditions in the WHERE clause but keep in mind that it will only display records in which there is a parent with related child records.

[DeathRobot]

Thanks, John. I'm interpreting your answer to be something like this? If so, there still seems to be something wrong with my syntax. I get a '?' result with it:

 

ExecuteSQL (

 

"

SELECT

id

 

FROM

People

 

INNER JOIN

Companies

 

ON

People.id_Company=Companies.id

 

WHERE

LOWER(Companies.name) LIKE LOWER('%searchString%')

"

 

; "" ; ""

 

)

[mr_vodka]

Try putting it in the parameter. For example try something like:

 

 

ExecuteSQL (
 
"
SELECT
    company.ID,
    company.name,
    people.ID,
    people.name
 
FROM
    people
 
LEFT JOIN
    company ON people.companyID = company.ID
 
WHERE
    LOWER(company.name) LIKE ?
"
 
; "" ; ""; "%" & Lower(company::gSearch) & "%"
 
)

[David Jondreau]

Is LOWER() an SQL function? I'm suspect you can't use it this way.

 

DJ

[DeathRobot]

Ah, thanks so much, John. That works perfectly. And David, the LOWER function appears to work just fine.

 

Michael