Need help with mysql on joining multiple tables on common column

[emrys404]

Hello,

Okay here is what i have so far (i simplified it down to its essence in order not to complicate things):

foreach(array("tablename1","tablename2","tablename3") as $table)
{   

     $result = mysql_query("SELECT orderid FROM $table ORDER BY orderid ") or die(mysql_error());

 while($tmp = mysql_fetch_assoc($result))
 {   
      $resultarray[$i] = $tmp;
      $resultarray[$i]['table'] = $table;
      $i++;
 }
}

return $resultsarray;

The goal with the above code is to fetch all the orders from the different tables and make one big list out of them. The orderid here is an incremental number that is given out by another table so that all orders (regardless of the table/service) have sequential order numbers.
The problem with the above code is that the resulting orderids are not in order throughout the whole list but only in order as they come out of the tables. ie) 1,2,5,6,3,4

My question is: Can this be done in one mysql query rather than one query for every table? In other words, how do i join multiple tables together when there is no common data point only a common column name?

Thanks in advance,

Emrys

[laserlight]

I suspect that you should place all the orders into one big table. How are the tables related (i.e., what is your current database schema)?

[emrys404]

Hello,

Thanks for your reply. Okay the database is made up of tables containing the records of different types of services, each table has upwards of 20+ unique columns with the only column in common being orderid which they receive from another table called orders. Like so:

orders {
id
service
}

service1 {
id
orderid
etc...
}

service2 {
id 
orderid
etc... 
}

etc...

It might be feasible to put all the columns in one big table but i'd rather not 🙂

Thanks,

Emrys

[laserlight]

I see. Why not just select from the orders table then?

[emrys404]

Hmm, that might be a solution. Okay the orders table is made up of two columns, one is the auto_increment id that it dishes out to the other tables when a new entry is submitted and then the second column is the table name of the service that was assigned that id.
Okay so using that info, how would i go about constructing a query? I'm assuming i'll join the tables on the orderid but how do i go about that with multiple tables?

Thanks,

Emrys

[emrys404]

Okay i've been experimenting a bit and have come up with:

$result = mysql_query("SELECT * FROM orders LEFT JOIN service1 ON orders.id = service1.orderid LEFT JOIN service2 ON orders.id = service2.orderid ORDER BY orders.id ") or die(mysql_error());

Now the problem with this is when tables service1 and service2 have a few column names in common (such as name and date) values for these fields only show up in records from service2. I suppose this has something to do with it being a LEFT JOIN.

How can i fix this statement so that these values show up in all records?

Thanks in advance,

Emrys

[Houdini]

Maybe you are looking for this page from the MySQL Manual where you will see the below as well as more

#

The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:

a LEFT JOIN b USING (c1,c2,c3)

#

The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables

[emrys404]

Hello!

Thanks that sounds more like what i need. However I seem unable to make it work. If i structure the query like this:

$result = mysql_query("SELECT * FROM orders NATURAL LEFT JOIN service1  NATURAL LEFT JOIN service2  ORDER BY orders.id ") or die(mysql_error());

there are no values for the fields from service2. If i take out the LEFT then it just returns nothing.

I've done some research but come up empty. How do i go about structuring this query?

Thanks in advance,

Emrys

[Roger_Ramjet]

A LEFT JOIN returns records from the left table regardless of whether there is a matching record in the right table. When ther is one it returns the data, and when there is not one it returns NULL values for the columns in the right table.

Now, is there is only one record across all tables for each order id?

[emrys404]

Yes, there is only one record with that id across all tables, not in every table, only one table (plus an instance of it in the orders table).

[Roger_Ramjet]

OK. The main problem you've got is that each table has a different structure with different names and numbers of columns. Otherwise you could just have done it with a UNION QUERY.

Using left joins to the 3 tables (it is only 3 isn't it?) should return this

id t1.c1 t1.c2 t1.c3 t2.c1 t2.c2 t3.c1 t3.c2 t3.c3 t3.c4
1..null....null...null....xyz...abc...null....null...null...null
2..def....hij....poi.....null...null...null....null...null...null

etc

At least that is what most dbs would return but mysql can be a bit strange sometimes.

Since id is unique to one table then the columns for the other tables will be NULL - which is what you want anyway. That is why you got the results you said. You just have to itterate through the results and find which cols are not null - which will also tell you which table the order is in. You will probably want to name and alias all selected columns so that they are unique and easily identifiable.

[emrys404]

Every thing you said is consistent with my experiments. Also the service column gets merged in with the other data when I join the tables to the orders table. It has id, and service as columns so knowing which service the record is from doesnt seem to be a problem. Also some of the tables can have up to 30+ columns and some have the same name as columns in other tables (this is where the problems arise i guess) so its not really feasible to alias 90+ columns for the query. Also It wont be just 3 tables in the future, i plan to add more....

I guess what i really want to do is join both tables to the orders table, but not necessarily to each other, in the same query... Is this even possible? I don't know why this is so obtuse, it would seem to me to be a common database schema...

Thanks in advance,

Emrys

[Roger_Ramjet]

Absolutely NOT a common db schema. In fact it flies in the face of relational database theory and 3rd normal form. The fact that you are struggling so much to do something that sounds so simple is because of this.

Yes, you can join multiple tables to the orders table, no problem there. Just be explicit in your join syntax - you have to use LEFT JOINS or you will get no rows at all. If you do that then you will not be joining them to each other, only to the orders table.
You will then have to deal with the hundreds of columns in each row - most of which contain null values.

[emrys404]

Well the premise behind the design is simple:
1.) Need consecutive order numbers for sales on the different services
2.) Need to have N number of services
3.) Need each service to have X number of unique columns available to it

So give that, how would you design the schema?

But more importantly i guess it wouldn't be so much of a problem to have lots of null fields in each row as the alternative (as far as i can see) is to sort a big array of all the records to put them in order... Which would be faster in the end?

The problem i was having with the LEFT JOINS, as stated above, was that the non unique column names for the tables on the left of the join would be returned null.. So i'm just not structuring the query right i guess. How would you do it? give each non unique column name a alias?

Anyway thanks for your help, i appreciate it very much,

Emrys