Joins

Hi,

i search in one table for a certain keyword. Each record can be connected (can be!) with data in another table, done by joins. So, now somebody searchs in my table and finds a records. He gets the information via a dynamic website but one piece of information is from the second table, but it must not be always a second piece of information. And here is my problem: If I find a record by searching the table with a keyword i dont get any output if the second information in the second table is not there, there is just no entry. How can i get the query (joins) not to care for the second information in the second table?? Please help...

$query = "SELECT links.*, counter.* FROM links, counter WHERE (links.name LIKE '%" . $suchwort_search . "%' OR links.link LIKE '%" . $suchwort_search . "%' OR links.besch LIKE '%" . $suchwort_search . "%' OR links.suchworte LIKE '%" . $suchwort_search . "%') AND (links.frei = 1) OR (links.id = counter.id AND counter.date = '2000-05-26') ORDER BY counter.hits, links.average, links.clicks DESC LIMIT $pos, $count";
Its a long query i know URL) Everything works fine if there are two identical IDs in both tables, but if not the query doesnot select a record.

Joins can be a little hairy, but consider your SQL statement:

SELECT <fields> FROM <table1>, <table2> WHERE <joincondition> AND <other where>

You should convert it to look like this:

select <fields> from <table1>,<table2> LEFT JOIN <table2> ON <table1.id=table2.id> WHERE <your other where conditions>;


------------------
PHP, Perl, SQL Programming at http://www.mentalobjects.com