1
voltan
INNER JOIN
  • 2011/2/3 19:53

  • voltan

  • Theme Designer

  • Posts: 724

  • Since: 2006/12/5


Hello

what is best method for use "INNER JOIN" in XOOPS modules ? I think "criteria" don't have option for it ?

If I use a function for "INNER JOIN" it's good method?

2
iunderwood
Re: INNER JOIN

I almost never use joins. My DB instructor didn't get too much into them, and one DBA I used to work with found their behavior less than optimal on some systems. In my case, I found it very difficult to follow JOIN statements when dealing with more than two tables.

I've always done an extended SELECT statement whenever I've needed to return one row with values from two:

// Look for and list intros for each mount point.
$query2 "SELECT x.sequence as sequence, x.intronum as intronum, y.filename as filename ";
$query2 .= " FROM ".$xoopsDB->prefix("uhqiceauth_intromap")." x,";
$query2 .= $xoopsDB->prefix("uhqiceauth_intros")." y ";
$query2 .= " WHERE x.server='".$row['server']."'";
$query2 .= " AND x.port='".$row['port']."'";
$query2 .= " AND x.mount='".$row['mount']."'";
$query2 .= " AND x.intronum=y.intronum";
$query2 .= " ORDER BY sequence";


What makes the join is the second to last line: "AND x.intronum=y.intronum", which joins the tables together.

Unless of course, I'm misunderstanding the problem.
++I;
Resized Image

3
ghia
Re: INNER JOIN
  • 2011/2/4 9:14

  • ghia

  • Community Support Member

  • Posts: 4953

  • Since: 2008/7/3 1


Quote:
What makes the join is the second to last line: "AND x.intronum=y.intronum", which joins the tables together.
Altough basicly equivalent, I prefer the join syntax, because you see immediate that x.intronum=y.intronum is used for the join, while in the previous WHERE list you have to search for it.

// Look for and list intros for each mount point.
$query2 "SELECT x.sequence as sequence, x.intronum as intronum, y.filename as filename ";
$query2 .= " FROM ".$xoopsDB->prefix("uhqiceauth_intromap")." x";
$query2 .= " LEFT JOIN ".$xoopsDB->prefix("uhqiceauth_intros")." y ON x.intronum=y.intronum";
$query2 .= " WHERE x.server='".$row['server']."'";
$query2 .= " AND x.port='".$row['port']."'";
$query2 .= " AND x.mount='".$row['mount']."'";
$query2 .= " ORDER BY sequence";

Login

Username:
Password:

Lost Password? Register now!

Who's Online

54 user(s) are online (27 user(s) are browsing Support Forums)


Members: 0


Guests: 54


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Jun 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits