SQL query hellp needed please

[Fangbeast]

Address is the parent table. Contacts is the referred table

In the query below, I join 2 tables so that I can query both tables for matches.

Searching for a specific string (gary), I get the identical record from the Addresses table returned 5 times because there are 5 matches in the Contacts table on Recordid.

'gary' only exists in record 2 in the Addresses table so only that parent record should be returned.

If the search text is found in the Contacts table as well, I only want the parent record returned from the Addresses table, not all records linked to the parent.

Any idea how to do this folks?

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

[Gadget]

Hi Fangbeast,

Been quite a while since I did any (basic) SQL programming but I do recall SELECT DISTINCT being used to return unique records.

Hope this helps,

Gadget

[infratec]

Hi,

without the exact structure of the tables it's difficult.
Contain both tables a field named Firstname?
If so, you need to restrict the where clause to 'a':

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

But are you sure that the Recordid is identical?
Normally a Recordid is unique and has nothing todo with a relation.
For that it should look like this:
WHERE a.Recordid = c.address_id

Bernd

[Shield]

First, as being said, you should really provide the complete table structures
and even better some sample data with expected results.
Use this: http://sqlfiddle.com/

Possible answer: when you join, you need to use the ON keyword, not WHERE.
WHERE is executed after the tables have already being joined,
so since you don't use ON, everything gets joined with everything,
resulting in duplicate rows.

Code: Select all

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

Possibly another join strategy is needed as well.

[Gadget]

Seems I was way off with my thoughts
Thank goodness there's some experts around to help

[Fangbeast]

Don't know if posting these tables will work but here goes: Addresses table. Recordid is an autoincremented integer field.

Code: Select all

Titlename	Firstname	Middlename	Lastname	Business	Nickname	Customname	Pobox	Street	Suburb	City	State	Postcode	Country	Displayformat	Displayas	Ownername	Category	Comment	Picture	Label	Favourite	Locked	Deleted	Updated	Recordid
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
Mr	Gary	Rastus	Emblonkment	Genius Aardvark Services	smeggie	plonker	P.O box 32	12 Canine Way	Mount Gerbilly Goat	Sydney	Ferretville	981273	Australia	Title First Middle Last	Mr Gary Rastus Emblonkment	Harrison Wells	Driver Education	just another useless dog botherer.	[BLOB_DATA]	Book.label	0	0	0	2015-05-19	2
Madam	Rhonda	Fizzgiz	Dogwobble										Australia	Title First Middle Last	Madam Rhonda Fizzgiz Dogwobble	Chairman Of The Bored	Doctor	what can I say about this one that hasn't blackened the history books?	[BLOB_DATA]	Book.label	0	0	0	2015-05-19	3
Doctor	Roger	All	Blondes	Organ Removal	weirdo	hamster face							Australia	NickName		Harrison Wells	Gardening Services	this one just loves to roger (and George!) all blondes not nailed down	[BLOB_DATA]	Book.label	0	0	0	2015-05-19	4

Contacts table. Contactid is an autoincremented integer field. Recordid is the parent record id in the Addresses table.

Code: Select all

Contactid	Contacttype	Contactdetails	Recordid
1	Home email	rastus@does.it.for.me	1
2	Home email	rastus@does.it.for.me	4
3	Home email	rastus@does.it.for.me	4
4	Work mobile	83765-1206752-0=672-=75-1	4
5	Home email	rastus@does.it.for.me	4
6	Home email	rastus@does.it.for.me	2
7	Home email	rastus@does.it.for.me	2
8	Home email	rastus@does.it.for.me	2
9	Home email	rastus@does.it.for.me	2
10	Home email	rastus@does.it.for.me	2
11	Home email	rastus@does.it.for.me	1

When running my query or the examples here, below is what I get: (Not that I knew what I was doing)

Code: Select all

Titlename	Firstname	Middlename	Lastname	Business	Nickname	Customname	Pobox	Street	Suburb	City	State	Postcode	Country	Displayformat	Displayas	Ownername	Category	Comment	Picture	Label	Favourite	Locked	Deleted	Updated	Recordid	Contactdetails
Mr	Gary	Rastus	Emblonkment	Genius Aardvark Services	smeggie	plonker	P.O box 32	12 Canine Way	Mount Gerbilly Goat	Sydney	Ferretville	981273	Australia	Title First Middle Last	Mr Gary Rastus Emblonkment	Harrison Wells	Driver Education	just another useless dog botherer.	[BLOB_DATA]	Book.label	0	0	0	2015-05-19	2	rastus@does.it.for.me
Mr	Gary	Rastus	Emblonkment	Genius Aardvark Services	smeggie	plonker	P.O box 32	12 Canine Way	Mount Gerbilly Goat	Sydney	Ferretville	981273	Australia	Title First Middle Last	Mr Gary Rastus Emblonkment	Harrison Wells	Driver Education	just another useless dog botherer.	[BLOB_DATA]	Book.label	0	0	0	2015-05-19	2	rastus@does.it.for.me
Mr	Gary	Rastus	Emblonkment	Genius Aardvark Services	smeggie	plonker	P.O box 32	12 Canine Way	Mount Gerbilly Goat	Sydney	Ferretville	981273	Australia	Title First Middle Last	Mr Gary Rastus Emblonkment	Harrison Wells	Driver Education	just another useless dog botherer.	[BLOB_DATA]	Book.label	0	0	0	2015-05-19	2	rastus@does.it.for.me
Mr	Gary	Rastus	Emblonkment	Genius Aardvark Services	smeggie	plonker	P.O box 32	12 Canine Way	Mount Gerbilly Goat	Sydney	Ferretville	981273	Australia	Title First Middle Last	Mr Gary Rastus Emblonkment	Harrison Wells	Driver Education	just another useless dog botherer.	[BLOB_DATA]	Book.label	0	0	0	2015-05-19	2	rastus@does.it.for.me
Mr	Gary	Rastus	Emblonkment	Genius Aardvark Services	smeggie	plonker	P.O box 32	12 Canine Way	Mount Gerbilly Goat	Sydney	Ferretville	981273	Australia	Title First Middle Last	Mr Gary Rastus Emblonkment	Harrison Wells	Driver Education	just another useless dog botherer.	[BLOB_DATA]	Book.label	0	0	0	2015-05-19	2	rastus@does.it.for.me

I just copied and pasted from my sql tool so some stuff might have made it into the tables.

DISTINCT is certainly a possibility, seems to work but is that always going to work or might it accidentally lose some entries in some circummstances?

[Fangbeast]

First, as being said, you should really provide the complete table structures
and even better some sample data with expected results.
Use this: http://sqlfiddle.com/

Thanks Shield. I don't understand it yet but will try it.

[infratec]

One stupid question:

Why you don't only use a select over address when you only want the record which is in address
The informaion in contacts are useless for this result, because you can never know which contact entry is returned.

[infratec]

Example:

Code: Select all

UseSQLiteDatabase()

If OpenDatabase(0, ":memory:", "", "")
  
  SQL$ = "CREATE TABLE address (recordid integer, firstname text, lastname text)"
  DatabaseUpdate(0, SQL$)
  
  SQL$ = "INSERT INTO address VALUES "
  SQL$ + "(1, 'Robert', 'Rob'),"
  SQL$ + "(2, 'Gary', 'Gar'),"
  SQL$ + "(3, 'Rhonda', 'Rhon'),"
  SQL$ + "(4, 'Roger', 'Roge')"
  DatabaseUpdate(0, SQL$)
  
  
  SQL$ = "CREATE TABLE contacts (contactid integer, details text, addressid integer)"
  DatabaseUpdate(0, SQL$)
  
  SQL$ = "INSERT INTO contacts VALUES "
  SQL$ + "(1, 'rob@test1.com', 1),"
  SQL$ + "(2, 'roger@test1.com', 4),"
  SQL$ + "(3, 'roger@test2.com', 4),"
  SQL$ + "(4, 'roger@test3.com', 4),"
  SQL$ + "(5, 'roger@test4.com', 4),"
  SQL$ + "(6, 'gary@test1.com', 2),"
  SQL$ + "(7, 'gary@test2.com', 2),"
  SQL$ + "(8, 'gary@test3.com', 2),"
  SQL$ + "(9, 'gary@test4.com', 2),"
  SQL$ + "(10, 'gary@test5.com', 2),"
  SQL$ + "(11, 'rob@gary.com', 1)"
  DatabaseUpdate(0, SQL$)
  
  
  SQL$ = "SELECT a.firstname, a.lastname, c.details FROM address as a, contacts as c WHERE a.recordid = c.addressid AND a.firstname LIKE '%gary%'"
  If DatabaseQuery(0, SQL$)
    While NextDatabaseRow(0)
      Debug GetDatabaseString(0, 0) + " " + GetDatabaseString(0, 1) + " " + GetDatabaseString(0, 2)
    Wend  
    FinishDatabaseQuery(0)
  EndIf
  
  Debug ""
  Debug "Find and return number of contacts:"
  
  
  SQL$ = "SELECT a.firstname, a.lastname, (SELECT count(*) FROM contacts AS c WHERE c.addressid = a.recordid) FROM address as a WHERE a.firstname LIKE '%gary%'"
  If DatabaseQuery(0, SQL$)
    While NextDatabaseRow(0)
      Debug GetDatabaseString(0, 0) + " " + GetDatabaseString(0, 1) + " " + GetDatabaseString(0, 2)
    Wend  
    FinishDatabaseQuery(0)
  EndIf
  
  Debug ""
  Debug "Results for different searches:"
  
  SQL$ = "SELECT firstname, lastname FROM address WHERE firstname LIKE '%gary%' "
  SQL$ + "UNION "
  SQL$ + "SELECT a.firstname, a.lastname FROM address AS a, contacts AS c WHERE c.details LIKE '%gary%' AND a.recordid = c.addressid"
  If DatabaseQuery(0, SQL$)
    While NextDatabaseRow(0)
      Debug GetDatabaseString(0, 0) + " " + GetDatabaseString(0, 1)
    Wend  
    FinishDatabaseQuery(0)
  EndIf
  
  CloseDatabase(0)
EndIf

What do you want ???

Bernd

[Fangbeast]

One stupid question:

Why you don't only use a select over address when you only want the record which is in address
The informaion in contacts are useless for this result, because you can never know which contact entry is returned.

I'll try to explain, although I am flying blind here.

The two tables are linked with a "FOREIGN KEY(Recordid) REFERENCES Addresses(Recordid))" in the contacts table.

Now, I want to be able to search each field in a table with one statement as I normally do with a single table. The below works for me with single table structure: This is my Appointments table query in another program for example

Code: Select all

"SELECT * FROM Appointments WHERE ("
" appName LIKE '%"            + SearchText.s + "%'"
" OR appDate LIKE '%"         + SearchText.s + "%'"
" OR appTime LIKE '%"         + SearchText.s + "%'"
" OR appAlarm LIKE '%"        + SearchText.s + "%'"
" OR appRepeat LIKE '%"       + SearchText.s + "%'"
" OR appProtect LIKE '%"      + SearchText.s + "%'"
" OR appCategory LIKE '%"     + SearchText.s + "%'"
" Or appStatus LIKE '%"       + SearchText.s + "%'"
" OR appComment LIKE '%"      + SearchText.s + "%'"
" OR appEmailed LIKE '%"      + SearchText.s + "%'"
" OR appArchived LIKE '%"     + SearchText.s + "%'"
" OR appSound LIKE '%"        + SearchText.s + "%'"
" OR appDeleted LIKE '%"      + SearchText.s + "%'"
" OR appRecord LIKE '%"       + SearchText.s + "%'"
")"
" ORDER BY appDate"

If I search for something in any field in my Addresses table using the setup above, this works fine. It returns the primary record (The parent if you will)

But what if I want to join the two tables so that I can search the records for a matching phone number which is in the Contacts table BUT return the PARENT record that it is linked to?

I am not wanting to return the data in Contacts, I just want to search Contacts (or any other field) and return the Parent. But also be able to search every field (As above) so I joined the two tables.

[Shield]

Well that's exactly what infratec's example does, isn't it?
In this case, of course you get multiple results because there are multiple matches for your constraint "LIKE %gary%".

If you want to limit your results to one address per match, simply do a GROUP BY:

Code: Select all

UseSQLiteDatabase()

If OpenDatabase(0, ":memory:", "", "")
	
	SQL$ = "CREATE TABLE address (recordid integer, firstname text, lastname text)"
	DatabaseUpdate(0, SQL$)
	
	SQL$ = "INSERT INTO address VALUES "
	SQL$ + "(1, 'Robert', 'Rob'),"
	SQL$ + "(2, 'Gary', 'Gar'),"
	SQL$ + "(3, 'Rhonda', 'Rhon'),"
	SQL$ + "(4, 'Roger', 'Roge')"
	DatabaseUpdate(0, SQL$)
	
	
	SQL$ = "CREATE TABLE contacts (contactid integer, details text, addressid integer)"
	DatabaseUpdate(0, SQL$)
	
	SQL$ = "INSERT INTO contacts VALUES "
	SQL$ + "(1, 'rob@test1.com', 1),"
	SQL$ + "(2, 'roger@test1.com', 4),"
	SQL$ + "(3, 'roger@test2.com', 4),"
	SQL$ + "(4, 'roger@test3.com', 4),"
	SQL$ + "(5, 'roger@test4.com', 4),"
	SQL$ + "(6, 'gary@test1.com', 2),"
	SQL$ + "(7, 'gary@test2.com', 2),"
	SQL$ + "(8, 'gary@test3.com', 2),"
	SQL$ + "(9, 'gary@test4.com', 2),"
	SQL$ + "(10, 'gary@test5.com', 2),"
	SQL$ + "(11, 'rob@test2.com', 1)"
	DatabaseUpdate(0, SQL$)
	
	
	SQL$ = "SELECT a.firstname, a.lastname, COUNT(a.recordid) FROM address AS a" +
	       " JOIN contacts AS c ON a.recordid = c.addressid" +
	       " WHERE a.firstname LIKE '%gary%'" +
	       " GROUP BY a.recordid"
	
	If DatabaseQuery(0, SQL$)
		While NextDatabaseRow(0)
			Debug GetDatabaseString(0, 0)
			Debug GetDatabaseString(0, 1)
			Debug GetDatabaseString(0, 2)
			Debug "-------------"
		Wend  
		FinishDatabaseQuery(0)
	EndIf
	
	CloseDatabase(0)
EndIf

[infratec]

Extended my listing above.

You have to make different SELECTs and have to combine the results using the UNION statement.
(Rob has now the e-mail rob@gary.com)

Bernd

[Fangbeast]

I will practice with your examples Shield and Infratec and I thank you. It hurts my brain, but science demands experimentation and pain!

[Fangbeast]

InfraTec, have been playing with your and Shield's examples using SQLite Expert and your example with UNION modified to use my fields works exactly how I wanted it.

You have to understand that I don't understand any of this advanced SQL and probably never will and am flying by the seat of my pants here but I keep trying examples until they start to work somehow.

A good SQL too like the one I use saves me from more grey hairs:):)

Thanks both of you, you are leading the blind:):)

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

[infratec]

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