Multiple Tables

rakennedy75

I am attempting to use a multiple table query
ex:

$query = "SELECT * FROM table1, table2";
$result = mysql_query($query, $conn) ;
$num_results = mysql_num_rows($result);

table1 has 153 entries
table2 has 10 entries

they both have common fields but aren't identical

the problem is that the result is pulling duplicates. The same entry is being pulled like 1 or 8 times.

the $num_results is pulling like 1377.

Does anyone know why this might be happening.
Any help would be greatly appreciated...

devinemke

you are not relating the tables in any way in your query, you have no WHERE clause. mySQL is simply pulling every record from both tables. you need to tell mySQL how these tables are related. for example (just guessing at field names here):

SELECT * FROM table1, table2 WHERE table1.customer_id = table2.buyer_id

rakennedy75

ok, maybe I am not approaching this the best way. What I want to do is pull the data from both tables and display them as if they were one. I don't want to physically merge them, but as far as the display is concearned I want them to look as if they are coming from one database.

So all the fields from table1 will be pulled and displayed (total is 153) and all the fields for table2 (total is 10) will be displayed. There is no direct relation between data. So I should get a result of a total of 163.

They do have common field names such as clientId, and aType, and date. I am using mysql_fetch_array to pull these so the field names should be fine.

I could probably just loop these into an array but I actually have close to 30 fields and I was hoping this might be the most effecient.

Let me know if I am doing this all wrong, any recommendation would be great. Thanks for your time.

devinemke

if there is no relation between the 2 tables then just do 2 separate queries. if you do 1 query with your orginal syntax then essentially what mySQL is doing is giving you a result set with every record from table1 with the first record from table 2, then every record again from table 1 with the second record from table 2 and so on... that's why you see all the duplicates.

rakennedy75

I see, stupid me. Still trying to get the hang of all these multiple query options. I guess I was trying to be lazy. Thanks for the help.