[RESOLVED] Joining two mysql tables...

[wpearsall]

ok guys...

SELECT r_category.catid, r_category.title,
r_business.name, r_business.address, r_business.emailcontact 
FROM r_business 
FULL OUTER JOIN r_category ON r_business.catid = r_category.catid 
WHERE r_business.BusinessID=2

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 'OUTER JOIN r_category ON r_business.catid = r_category.catid WHERE r_business.Bu' at line 1.

ok gues... heres my problem, i have got two tables,, r_business, and r_category

r_category lists diff categorys of business (restaurant / hotel ETC) n links via catid in both tables.

HOW can i get the information in one query... i only wanna select a certain business?.... i thought that was correct but guess not... any help please?

[wpearsall]

OK think i sorted it, should of searched harder.... i needed INNER JOIN instead of FULL OUTER JOIN ??,,,

but... if i wanna link BusnessID to the users table which as got BusinessID as a foriegn key AS WELL... i can just add an additional row?

SELECT r_category.catid, r_category.title,r_business.name, r_business.address, r_business.emailcontact, r_user.username
FROM r_business 
INNER JOIN r_category ON r_business.catid = r_category.catid
INNER JOIN r_user ON r_business.BusinessID = r_user.BusinessID 
WHERE r_business.BusinessID=2

is that correct?

[si_the_geek]

An Inner Join is standard, and probably what you want (it only returns records when there is data in both tables).

You use some kind of Outer Join when you want to always get data from one of the tables, even if there is no matching data in the other. You would usually use a Left or Right, rather than a Full (which returns unmatching data from both tables).


To use extra tables in the query, just add an extra Join as you did.

[wpearsall]

OK I thought i had this but obviously not... i have built my database in access to try and get it to work, but nothing... im guessing maybe theres something wrong with how i want to link the tables, but i just cant see it?.

the image attached has the layout of wot i wish to accomplish...

but the code im using doesnt join the tables?

and if it does... it doesnt present any information... / records, can anyone help with this?

$qry = "SELECT  ".$prefix."r_category.catid, ".$prefix."r_category.title, "
                     .$prefix."r_state.name, "
                     .$prefix."r_highway.Highway, "
                     .$prefix."r_exit.exitno, "
                     .$prefix."r_business.BusinessID, ".$prefix."r_business.name "
                     ."FROM ".$prefix."r_business "
                     ."INNER JOIN ".$prefix."r_state ON ".$prefix."r_state.stateid = ".$prefix."r_highway.State "
                     ."INNER JOIN ".$prefix."r_exit ON ".$prefix."r_exit.highwayid = ".$prefix."r_highway.HiwayID "
                     ."INNER JOIN ".$prefix."l_exitbusiness ON ".$prefix."l_exitbusiness.exitid = ".$prefix."r_exit.ExitID "
                     ."INNER JOIN ".$prefix."r_business ON ".$prefix."l_exitbusiness.busid1 = ".$prefix."r_business.BusinessID "
                     ."INNER JOIN ".$prefix."r_category ON ".$prefix."r_category.catid = ".$prefix."r_business.catid ";

[si_the_geek]

Is that giving you an error? (it should be) If so, what?


After splitting it out, I can see that you have made two major mistakes.. you have not specified all of the tables that are needed (for example, r_highway is not in there), and you have not joined to/from apt tables (eg: you effectively join r_business to itself).

For simplicity, I have removed $prefix from the following.

These parts are valid:

FROM r_business 
INNER JOIN r_category ON r_category.catid = r_business.catid

..as each table is listed just once (after FROM or JOIN) and the ON clauses specify fields that are in the tables which are already listed.

This part would be valid if you had r_highway already joined:

INNER JOIN r_state ON r_state.stateid = r_highway.State

This is how it should be:

FROM r_business 
INNER JOIN r_category     ON r_category.catid = r_business.catid 
INNER JOIN l_exitbusiness ON l_exitbusiness.busid1 = r_business.BusinessID 
INNER JOIN r_exit         ON l_exitbusiness.exitid = r_exit.ExitID 
INNER JOIN r_highway      ON r_exit.highwayid = r_highway.HiwayID 
INNER JOIN r_state        ON r_state.stateid = r_highway.State