Chris Sansom wrote:
First I should say I'm using MySQL 3.23.x because that's what's
currently available on our host's server. An upgrade to 5.x is promised
any time now, but I'm not holding my breath! So, with that in mind...
I'm trying to do quite a sophisticated search across several tables and
am running into trouble, maybe because of trying to use 'or', maybe
because of records not present, I dunno. Whatever it is, there's
obviously a flaw in my logic that I can't find.
Simplifying it to the relevant bits, I'm searching six tables of data
relating to people. For the present purpose I'll call the tables T1..T6.
T1 contains a record for each person, including lots of basic
information and an ID field that the other tables refer to. Tables T2 to
T6 each have three fields: that same id, a field for a two-letter
language code (en, fr, de, etc.) and a lump of text. The important thing
is that in T2 to T6, any one person might have several rows if they've
provided information in several languages - or none at all (relatively
few have any data in T6, for example).
I want to be able to enter a bit of text in the search form and find
anyone in the db with the search term anywhere in T2..T6, in any language.
My first instinct was this:
select
[fields I want to display from t1]
from
t1, t2, t3, t4, t5, t6
where
(t2.text like '%search_term%' and t2.id = t1.id)
or
(t3.text like '%search_term%' and t3.id = t1.id)
or
(t4.text like '%search_term%' and t4.id = t1.id)
or
(t5.text like '%search_term%' and t5.id = t1.id)
or
(t6.text like '%search_term%' and t6.id = t1.id)
Err.. JOIN ... ON ?!
However, that sent the system into what was obviously some huge loop
which, if I waited long enough, would have produced thousands upon
thousands of results. It also sent my UPS into overload, so I
interrupted it! The logic looks right to me, but obviously there's
something wrong: as a friend agreed, 'or' often doesn't seem to do what
you expect in SQL!
Well depends on what "YOU" expect that OR does.
Well for me OR works fine. :D
So then I tried this:
select
[fields I want to display from t1]
from
t1, t2, t3, t4, t5, t6
where
t2.id = t1.id and t3.id = t1.id and t4.id = t1.id and t5.id = t1.id
and t6.id = t1.id and
(t2.text like '%search_term%' or t3.text like '%search_term%' or
t4.text like '%search_term%' or t5.text like '%search_term%' or t6.text
like '%search_term%')
Looks better than the first one but still missing JOIN ~~~
That produced no results at all, and I quickly saw why: if a person has
no rows at all in any of T2..T6 they'll be excluded. Once I removed T6
from the search, one person reappeared. She didn't have the search term
in all the other tables, but she does at least have entries there, but
none in T6.
Well at least no Error message :)
The first solution definitely seems more logical and elegant, but where
have I gone wrong, O MySQL gurus? I've investigated left joins (which
I've so far had no use for, strangely enough) but can't see how to apply
them in this case. Maybe subqueries, which I know we'll get in MySQL 5,
are the answer?
Well yeah kinda ^_^"
Ok ok.
I guess you want to have noone excluded.
AND (<- not OR) ID of t2-t6 is NOT auto_increment and is pointable onto
t1.id! X]
Try that:
select [what you want]
from t1
LEFT JOIN t2 ON t2.id = t1.id
LEFT JOIN t3 ON t3.id = t1.id
LEFT JOIN t4 ON t4.id = t1.id
LEFT JOIN t5 ON t5.id = t1.id
LEFT JOIN t6 ON t6.id = t1.id
where
t2.text like '%search_term%' OR t3.text like '%search_term%' OR
t4.text like '%search_term%' OR t5.text like '%search_term%' OR
t6.text like '%search_term%' ORDER BY t1.id ASC;
Another possibility would be to use several queries, but that would mean
some fairly hefty rewriting of the php that puts this stuff together, so
I'd prefer to avoid it if possible. Surely it /must/ be possible in one
query?
It's never that complicated =)
(Before we decided to make this thing multi-lingual it was simple of
course, because the five lumps of text were all in T1.)
That one should work though. V(~_^)
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
