using a join

[Spring]

Could someone please show me how to properly use a join please? Here's the code I'm looking to make.

$sql = "SELECT * FROM phpbb_pets where id = phpbb_user_pets.petid AND phpbb_user_pets.username = '$username'";

[The Little Guy]

you can't use a join on one table, unless you want to join the table onto its self.

 

A join is a query that takes two tables, for our example we will call them TableA and TableB.

 

TableA and TableB should have 2 columns that have the same value its not required but you have to know how to do more advanced joins (I'll explain later). So now that your two tables have 2 columns that have the same value in it, you can join them to make one table result set.

 

Take these example queries:

select * from members where member_id = 123;
select * from member_info where member_id = 123;

 

These 2 queries return different results this requires MySQL to do two separate queries, and this could have been done in one query really easily. There are 3 common joins:

- Join

- Left Join

- Right Join

 

Join and Left Join are the two more common joins in my opinion (and maybe even statistically).

Any way, a Join selects from the first table and joins the second tables removing anything from the final table that contains a null value.

A Left Join selects from the first table and joins on the second table, leaving everything including the null values in the final table. (most nulls will show to the right)

A Right Join is the same as a Left Join, only the nulls show to the left

 

That wasn't too in depth, but it has a basic concept down. Now for the two queries above, you you should see that there are two columns in each of those queries that have the same value/name.

 

There are two ways you can write this join:

select * from members m left join member_info mi on(m.member_id = mi.member_id) where m.member_id = 123;

 

That is one way, the short had way (second way) would look something like this:

select * from members m left join member_info mi using(member_id) where m.member_id = 123;

 

its not much shorter, but it is another option. The only way to use "using()" is if the two columns you are joining on use the exact same name. If you are wondering what m and mi are after each table, if you are familiar with OOP, it is basically like making a instance of the table, so you can call it by m instead of members (as you can see in example 1).

 

If I were to use a join instead of a left join and the member didn't exist in table member_info, I would get no results, but using this left join all returned info from member_info would display as null, and I would get one result (assuming member_id 123 isn't in either table more than once).

 

I hope this helps some, if not just ask.