KingleyMosso KingleyMosso - 4 months ago 8
SQL Question

How to search in several tables and show up only one of them

I have three mysql tables that are connected. I want to search with several keywords in all three but only show information from AREAS. For instance, if someone search for 'name1' I want to show the info found in the row for area nr 2 in the table AREAS.

+---------+--+-----------------+--+--------------------------+
| AREAS | | RINGS | | RINGSLOT |
+---------+--+-----------------+--+--------------------------+
| ID | nr | | areanr | ringnr | | ringnr | slot | slotname |
+----+----+--+--------+--------+--+--------+------+----------+
| 5 | 1 | | 2 | 14 | | 14 | 0 | name1 |
+----+----+--+--------+--------+--+--------+------+----------+
| 6 | 2 | | | | | 14 | 1 | name2 |
+----+----+--+--------+--------+--+--------+------+----------+
| 7 | 3 | | | | | 14 | 3 | name 3 |
+----+----+--+--------+--------+--+--------+------+----------+


So far I've tried with all kinds of FOR-loops without any success but think I got the query right, I just don't know how to process the info from here?

mysql_query("(SELECT * 'area' as type FROM areas WHERE nr LIKE '%" . $keyword . "%')
UNION
(SELECT * 'ring' as type FROM rings WHERE ringnr LIKE '%" . $keyword . "%')
UNION
(SELECT ring, namn 'slot' as type FROM ringslot WHERE slotname LIKE '%" . $keyword . "%')";

Answer

You can do it with joins, by showing up only the columns from AREAS table:

"SELECT DISTINCT A.* 
FROM areas A
LEFT JOIN RINGS r
 ON(A.nr = r.areanr)
LEFT JOIN RINGSLOT rs
 ON(r.ringnr = rs.ringnr)
WHERE r.slotname LIKE '%" . $keyword . "%'
   OR rs.ringnr LIKE '%" . $keyword . "%'
   OR A.nr LIKE '%" . $keyword . "%'"
Comments