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

Who's Online

211 user(s) are online (110 user(s) are browsing Support Forums)


Members: 0


Guests: 211


more...

Donat-O-Meter

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

Latest GitHub Commits