MySQL Multi Join

Question

I had a look at the other answers in relation to mysql join and am still a little confused as to whether i'm using it right for the right instance.

I have a query that I need to perform over multiple tables at the same time and keep getting told that my statement is ambiguous.

I have 4 tables... trecord, thours, torders and tphotos

The trecord table has it's own autonumber called bid, and all the other tables have their own autonumber as well as a bid column. The trecord table will only ever have one record that needs to be retrieved at a time as does the thours table, but the torders and tphotos table will more often than not have multiple records in them that will tie back to the trecord to always show the company info.

How can I bring together all the information from trecord and thours as well as all of the information from torders and tphotos that match the bid?

$id = isset($_POST['bid']) ? $_POST['bid'] : isset($_GET['bid']) ? $_GET['bid'] : null;

$sql = "SELECT * FROM torders
INNER JOIN trecord ON torders.bid=trecord.bid
INNER JOIN thours ON thours.bid=trecord.bid
INNER JOIN tphotos ON tphotos.bid=trecord.bid
FROM  WHERE bid='" . mysql_real_escape_string($id) . "'";

Does your query work? I don't see the query as being ambiguous if you actually want to get the information from each of those tables. Perhaps they thought the design of the table structure was ambiguous. — Spencer Wieczorek
– Spencer Wieczorek, Commented Feb 8, 2015 at 5:15
What's wrong with your code? (Other that you have an extra FROM)? — PM 77-1
– PM 77-1, Commented Feb 8, 2015 at 5:15
@AlienWebguy Why? " tables have their own autonumber as well as a bid column". I don't see any mention of some items having null or missing data. — Spencer Wieczorek
– Spencer Wieczorek, Commented Feb 8, 2015 at 5:17
Hi Alien, tables have their own autonumber for the example of orders having their own order number and photos having their own photo number :-) — cloudseeker
– cloudseeker, Commented Feb 8, 2015 at 5:24

StephenCollins · Accepted Answer · 2015-02-08 05:48:37Z

1

If every table has a 'bid' column, you need to be more specific as to which one you are running your where clause against.

answered Feb 8, 2015 at 5:48

StephenCollins

7955 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cloudseeker Over a year ago

HAHAHA... You're awesome! All it took was to add the trecord.bid to the where clause!

StephenCollins Over a year ago

Thanks. Good luck with the rest.

Collectives™ on Stack Overflow

MySQL Multi Join

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related