Feb
09
09
So I’m working on some code that will allow me to search multiple databases in a single query, returning a resultset.
Note: this is MySQL
-
– Searching the "clipper" database
-
– for any image that has ‘dog%’ as a keyword
-
SELECT
-
S.Filename,
-
S.Record_ID,
-
S.Path,
-
S.File_Description
-
FROM
-
clipper.Item_Table S,
-
clipper.Keyword C1,
-
clipper.Item_Keyword O1
-
WHERE
-
S.Record_ID = O1.Item_ID
-
AND
-
O1.Keyword_ID = C1.Record_ID
-
AND
-
C1.Keyword LIKE ‘dog%’
-
GROUP BY
-
S.Record_ID
-
LIMIT 0,30
What I’m having a tough time with is the clipper.columnName part. Each search may have many, many C[n] and O[n] values (these are the keyword items as they relate to the joins). If I have clipper.THIS and clipper.THAT, then how do I add image_server.THIS and image_server.THAT*?
I’m thinkink I might just loop the queries and pool all the results together into one XML resultset.
There should be a more efficient way, however, right?
* “clipper” and “image_server” are two databases where images are stored.
Could this be the answer?
-
– Searching the "clipper" AND "image_server"
-
– databases for any image that has ‘dog%’ as a keyword
-
SELECT
-
S1.Filename,
-
S1.Record_ID,
-
S1.Path,
-
S1.File_Description,
-
S2.Filename,
-
S2.Record_ID,
-
S2.Path,
-
S2.File_Description
-
FROM
-
clipper.Item_Table S1,
-
clipper.Keyword C1,
-
clipper.Item_Keyword O1,
-
image_server.Item_Table S2,
-
image_server.Keyword C2,
-
image_server.Item_Keyword O2
-
WHERE
-
S1.Record_ID = O1.Item_ID
-
AND
-
S2.Record_ID = O2.Item_ID
-
AND
-
O1.Keyword_ID = C1.Record_ID
-
AND
-
O2.Keyword_ID = C2.Record_ID
-
AND
-
C1.Keyword LIKE ‘dog%’
-
AND
-
C2.Keyword LIKE ‘dog%’
-
GROUP BY
-
S1.Record_ID,
-
S2.Record_ID
Solved:
Thanks to Greg Miley, the problem has been solved:
-
SELECT
-
cit.Filename,
-
cit.Record_ID AS Record_ID,
-
cit.Path,
-
cit.File_Description,
-
"clipper" AS database_name
-
FROM
-
clipper.Item_Table cit
-
JOIN
-
clipper.Item_Keyword cik
-
ON cik.Item_ID = cit.Record_ID
-
JOIN
-
clipper.Keyword ck
-
ON ck.Record_ID = cik.Keyword_ID
-
WHERE
-
ck.Keyword LIKE ‘dog%’
-
OR
-
ck.Keyword LIKE ‘cat%’
-
-
UNION
-
SELECT
-
isit.Filename,
-
isit.Record_ID AS Record_ID,
-
isit.Path,
-
isit.File_Description,
-
"image_server" AS database_name
-
FROM
-
image_server.Item_Table isit
-
JOIN
-
image_server.Item_Keyword isik
-
ON isik.Item_ID = isit.Record_ID
-
JOIN
-
image_server.Keyword isk
-
ON isk.Record_ID = isik.Keyword_ID
-
WHERE
-
isk.Keyword LIKE ‘dog%’
-
OR
-
isk.Keyword LIKE ‘cat%’
Categories