table join query

[mpc123]

Hi

I am having an issue with a table join query whhich later I will echo results into a table.

Example
I have 2 x tables and Table1field1 has a number say 555 but table2field1 which I need to join to has 555 342 642 313 etc etc

This is what I have but it does not join.

SELECT * FROM Table1, Table2 Where `Table1.field2` = 'blar' and `Table1.field3` = 'blarblar' and 'table1.field1' LIKE "'%table2.field1%'" LIMIT  1

Hope someone can help with this

[AleMonteiro]

It's not joining because you didn't specify the link column.

You should have something like this: WHERE Table1.Field1 = Table2.Field1 AND...

You need to teel by which columns the tables are linked.

[mpc123]

Thanks for the reply.

If I understand you correctly, which maybe not, I have actually done that in my query

SELECT * FROM Table1, Table2 Where Table1.field2 = 'blar' and Table1.field3 = 'blarblar' and 'table1.field1' LIKE "'%table2.field1%'" LIMIT 1

[AleMonteiro]

Oh, I didn't saw that.

Check this out: http://stackoverflow.com/questions/1930809/mysql-join-query-using-like

[veedeoo]

Here is a simple example of joins. I could have written this in shorthand form, but I will do it in long haul to make my points a lot clearer.

Say, we have a table called students, and on this table we have id, name, username, password, email, course_num, and status columns.

+-----+------+----------+----------+-------+------------+----------+
+ id  + name + username + password + email + course_num + status   +
+-----+------+----------+----------+-------+------------+----------+
+ 100 + name + user     + pass     + @home + 300        + enrolled +
+-----+------+----------+----------+-------+------------+----------+

Another table called courses is also in the same database, and it also have the following columns course_id, course_name, instructor,term, year. course_id is not auto incremented.

+------------+-------------+-----------+------+-------+---------------------------------------------+
+ course_id  + course_name + instuctor + term + year  +               bio                           +
+------------+-------------+-----------+------+-------+---------------------------------------------+
+ 300        + physics     + veedeoo   + fall + 2012  + The youngest professor ever walked on earth +
+------------+-------------+-----------+------+-------+---------------------------------------------+

!NOTICE? the only columns that have common value between these two tables are the course_num and course_id.

Scenario 1: The student log in ( of course another page does the validation), we will show this student of the classes he or she is enrolled. Including the course name, course id, the term and the year of enrollment.

The mysql database query: For even more clarification, I will write the main Key of the query in uppercase. I hope other readers will not take that against me.

   $this_query = "id, name, course_num, status, course_id, course_name, instructor, term, year";
   ## Notice the above query?, the column names of both tables students and courses are included.
   ## Let's go ahead and move on to the actual query
   $get_student = ("SELECT ". $this_query ."  FROM `students` LEFT JOIN `courses` ON (`students`.`course_num` = `courses`.`course_id`) WHERE `status` ='enrolled'") ;

If we execute the query above, we can easily retrieve the following information.

    ## say the query was a success and I placed them in variable $row, then the info. from this query can be as precise as these.

    $student_name = $row['name'];

    $student_class = $row['course_name'];// Notice? this is from the courses table

    $stuendt_section = $row['course_num']; //Again, this is coming from the students table

    $instructor = $row['instructor']; //another item from courses table

    $term_year = $row['term'] .' / '. $row['year']; //this should give fall / 2012 if defined in courses table.