SQL query hellp needed please

[Fangbeast]

I still miss a thump up smilie.

This SQL is not complex.
Only 2 different simple SELECTs which returns the same fields and the results of both are combined
via the keyword UNION.

Btw. you can also combine more then 2 SELECT results with UNION.

Bernd

I don't have you skill or experience Bernd and when I passed a certain age, I was going downhill:):)

Still, I get there in the end with the help of my friends. Have 'massaged' your example into my Address book program, you should see the mess!! But, it works like a charm. Now I can send the program to a friend while I work on the non-essential parts of it (help files, export etc) and a few other things.

Thanks once again

[Num3]

Try:

Code: Select all

SELECT a.*, c.Contactdetails FROM Addresses AS a LEFT JOIN Contacts AS c ON a.Recordid = c.Recordid
WHERE a.Firstname LIKE '%gary%' ORDER BY Displayas

or

Code: Select all

SELECT DISTINCT a.*, c.Contactdetails FROM Addresses AS a LEFT JOIN Contacts AS c ON a.Recordid = c.Recordid
WHERE a.Firstname LIKE '%gary%' ORDER BY Displayas

Explanation:

SQL is creating a "virtual" table by joining both tables using left join (left table = check all records, right table = only add records where commom field is the same), and to do so you must define a common field on both tables (Recordid in your case), after this 'union' you apply your filters ( "'%gary%'" in this case).
The result is an output with the fields you selected from both tables, and filtered by your options.

The Distinct option is sometimes usefull to remove duplicate "equal" records, so the output only shows one distinct result of each record (this usually happens when you group records with the GROUP BY option).

-- EDIT --

Code: Select all

SELECT Firstname, Middlename, Lastname, Business, Nickname, Customname, Street, Suburb, City, State, Postcode, Country, Displayas, Ownername, Category, Comment FROM Addresses
WHERE
       Firstname LIKE '%gary%'       
       OR Middlename LIKE '%gary%'
       OR Lastname LIKE '%gary%'
       OR Business LIKE '%gary%'
       OR Nickname LIKE '%gary%'
       OR Customname LIKE '%gary%'
       OR Street LIKE '%gary%'
       OR Suburb LIKE '%gary%'
       OR City LIKE '%gary%'
       OR State LIKE '%gary%'
       OR Postcode LIKE '%gary%'
       OR Country LIKE '%gary%'
       OR Displayas LIKE '%gary%'
       OR Ownername LIKE '%gary%'
       OR Category LIKE '%gary%'
UNION
SELECT a.Firstname, a.Middlename, a.Lastname, a.Business, a.Nickname, a.Customname, a.Street, a.Suburb, a.City, a.State, a.Postcode, a.Country, a.Displayas, a.Ownername, a.Category, a.Comment
FROM Addresses AS a, Contacts AS c
WHERE c.Contactdetails LIKE '%gary%' AND a.Recordid = c.Recordid

OMG
Well it works, but it would crash or slow down a mysql server if you ever did that query on a few millions records
It's like killing a flye with a bazooka !!!

Here's my version

Code: Select all

SELECT * FROM Addresses AS a LEFT JOIN Contacts AS c ON a.Recordid = c.Recordid
WHERE a.Firstname OR a.Middlename OR a.Lastname OR a.Business OR a.Nickname OR a.Customname OR a.Street  OR a.Suburb
       OR a.City OR a.State OR a.Postcode OR a.Country OR a.Displayas OR a.Ownername OR a.Category or c.Contactdetails LIKE '%gary%' ORDER BY Displayas

[Fangbeast]

I'll try anything once, twice, thrice. Just have trouble understanding it. Still, got mine to work with Bernd's help.

I'm amazed how many good people help me here and all of my code is public.

Well it works, but it would crash or slow down a mysql server if you ever did that query on a few millions records It's like killing a flye with a bazooka !!!

Oi, no dissing the bozo!!! It works for me and I didn't know any better:):) LOL!!

SELECT * FROM Addresses AS a LEFT JOIN Contacts AS c ON a.Recordid = c.Recordid
WHERE a.Firstname OR a.Middlename OR a.Lastname OR a.Business OR a.Nickname OR a.Customname OR a.Street OR a.Suburb
OR a.City OR a.State OR a.Postcode OR a.Country OR a.Displayas OR a.Ownername OR a.Category or c.Contactdetails LIKE '%gary%' ORDER BY Displayas

Will try this too. So glad I'm not too old to learn new stuff. It;s exciting that I am still around to play:):)

[Fangbeast]

Okay, just tried it in my sqlite tool.

That query bought up two copies of a record that DIDN'T have gary in it and 6 copies of the record that did???

Then I tried different search terms and same result.

Trying hard to find 'gary' anywhere in the first two copies of a match and cannot see it anywhere (Must be going blind"

This is one of the matches below and I defy anyone to find 'gary' anywhere in it!!

Mr Robert Joubert Christiane Danger Mice R Us fred haddock face p.o box 72 123 Spanky Way Chhookville Tenessee Florida 47485938 Bahamas Company Name Danger Mice R Us Miklos Bolvary Lawyer one very confused individuial [BLOB_DATA] Book.label 0 0 0 2015-05-19 1 1 Home email rastus@does.it.for.me 1

[normeus]

Also if you are going to search all fields you might want to open your database using fts4:

Code: Select all

DatabaseUpdate(Database, "CREATE VIRTUAL TABLE oldfolksrecords using fts4  (id,name,etc)")

then you could search all with

Code: Select all

SELECT * FROM oldfolksrecords WHERE oldfolksrecords MATCH 'Gary';

Norm.

[Num3]

Okay, just tried it in my sqlite tool.

That query bought up two copies of a record that DIDN'T have gary in it and 6 copies of the record that did???

Without looking at the database, it could be a number of things.

Try SELECT UNIQUE or SELECT DISTINCT instead of just SELECT *