The trouble with joins..

[swatisonee]

or maybe my inability to understand them .

I'm in a mess trying to get joins and unions right. I wished to combine Tables B&C, both having identical structures and tried the foll. 3 options all of which returned errors. The Unions work correctly when used without the Join and the Join works correctly for a single table but put them all in the same sql and errors flow even if the ORDER BY clause is removed.

So where am I going wrong ?

CODE 1

$sql11a = "SELECT * FROM `TableB`

 UNION

 SELECT * FROM `TableC`

 INNER JOIN `TableA` 

 ON/ USING  // (tried both options)

(`TableB`.Sid = `TableA`.Sid) || (`TableC`.Sid = `TableA`.Sid) ) // line 9

 WHERE `TableA`.`Eid` = $group  AND `TableA`.`Month` = $month AND `TableA`.`Year` = $year 

 ORDER BY `Binname` asc  ";

  $result11a = mysql_query($sql11a) or die (mysql_error());
 if ($myrow11a = mysql_fetch_array($result11a))
 {
 do
 {

ERROR:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.Sid = TableA.Sid) || (TableC.Sid = TableA.Sid) line 9

CODE 2

$sql11a = "SELECT * FROM `TableB`

 UNION

 SELECT * FROM `TableC`

 INNER JOIN `TableA` 

 USING (Sid)  

 WHERE `TableA`.`Eid` = $group  AND `TableA`.`Month` = $month AND `TableA`.`Year` = $year 

 ORDER BY `Binname` asc  ";

$result11a = mysql_query($sql11a) or die (mysql_error());
 if ($myrow11a = mysql_fetch_array($result11a))
 {
 do
 {

ERROR :
The used SELECT statements have a different number of columns

CODE3

$sql11a = "SELECT * FROM `TableB`

 UNION

 SELECT * FROM `TableC`

 INNER JOIN `TableA` 

 ON Sid

 WHERE `TableA`.`Eid` = $group  AND `TableA`.`Month` = $month AND `TableA`.`Year` = $year 

 ORDER BY `Binname` asc  ";

$result11a = mysql_query($sql11a) or die (mysql_error());
 if ($myrow11a = mysql_fetch_array($result11a))
 {
 do
 {

ERROR:

Column 'Sid' in on clause is ambiguous

[bretticus]

CODE 1: your USING statement needs to be wrapped in parens most likely.
CODE 2: Probably right syntactically but with the inner join you have extra columns in the "bottom" query. (try SELECT TableC.* FROM TableC perhaps)
CODE 3: You are using an ON where a USING should be used instead (perhaps: USING(Sid)

[swatisonee]

Thanks for the suggestions but no go.

CODE 1 didnt work with or without parentheses.
CODE 2 duly modified, outputted all data from all 3 tables overriding the query .

I still dont get when I should be using ON and when USING. I also cannot understand why TableC shows a greater number of columns when they dont actually exist.

Anyway, I've come up with the foll for a working solution. Not an efficient way to code but since the more logical options werent yielding results, i wrote this up.

$sql11a = "SELECT * FROM `TableB` 
INNER JOIN `TableA` 
USING  (Sid)
WHERE `TableA`.`Eid` = $group  AND `TableA`.`Month` = $month AND `TableA`.`Year` 

UNION 

SELECT * FROM `TableC` 
INNER JOIN `TableA` 
USING (Sid)
WHERE `TableA`.`Eid` = $group  AND `TableA`.`Month` = $month AND `TableA`.`Year` 

ORDER BY `Binname` asc  "; 

[rm_phpbuilder]

$q = "select t2.track_date, t2.track_id from users as t1 inner join tracking as t2 on t1.track_id = t2.track_id where t2.track_id = '00001' order by [field] desc limit 1";

users = t1 database
tracking = t2 database

track_id = 00001 available on both tables.... :-)

[swatisonee]

I have 3 tables...