MYSQL Join

[moondran]

Hi Guys

 

I'm having a little problem with fetching data from 2 or more tables from the same db. I'm using 2 methodes:

 

1.

$sql_emp = "SELECT a.*, b.points FROM `table_users` AS a, `table_points` AS b WHERE b.user_id=a.id ORDER BY a.surname ";

2.

$sql_emp = "SELECT a.id,a.name,a.surname,b.points FROM a LEFT JOIN b ON b.user_id = a.id ";

 

 

Methode 1 is working but my problem is when table b has no entry it's not returning anything. In other words say there is 10 employees stored in table a and only 2 employees have points entries in table b my query only return 2. Is there a way to join tables that I can get all the entries from table a even if there is no entries in table b?

 

[xtremey_ytinasni]

$sql_emp = 'SELECT a.`id`,a.`name`,a.`surname`,b.`points` '.
                    'FROM `table_name` a '.
                    'LEFT JOIN `table_name` b '.
                    'ON a.`id`=b.`id`';

 

That should work. Honestly you don't have to break it by lines, but I find that this makes it much easier to edit the query, and much easier to read. Just a preference, but looks much cleaner to me. So anyway, you should now be able to use that sql for query and grab the data with thier respective column names.

[xtremey_ytinasni]

Is there a way to join tables that I can get all the entries from table a even if there is no entries in table b?

 

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2). ( from w3schools.org). So basically, when left joining, you would have the LEFT table's (in this instance "id") column JOIN with with the right table's column, thus LEFT JOIN Well, im just talkin from what it sounds like, and thats always the way i've used it and it works fine so dont see a problem.

[gizmola]

Yes, that is called doing an "outer" join, or in this case, a "LEFT OUTER JOIN".  The syntax in your example will perform an outer join in the way you have described.

[gizmola]

Is there a way to join tables that I can get all the entries from table a even if there is no entries in table b?

 

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2). ( from w3schools.org). So basically, when left joining, you would have the LEFT table's (in this instance "id") column JOIN with with the right table's column, thus LEFT JOIN Well, im just talkin from what it sounds like, and thats always the way i've used it and it works fine so dont see a problem.

$sql_emp = 'SELECT a.`id`,a.`name`,a.`surname`,b.`points` '.
                    'FROM `table_name` a '.
                    'LEFT JOIN `table_name` b '.
                    'ON a.`id`=b.`id`';

 

That should work. Honestly you don't have to break it by lines, but I find that this makes it much easier to edit the query, and much easier to read. Just a preference, but looks much cleaner to me. So anyway, you should now be able to use that sql for query and grab the data with thier respective column names.

 

I think it might help you to know that you do not need to concatenate pieces of a string together like that just because you want to break the SQL statement up into seperate lines. 

 

$sql_emp = 'SELECT a.`id`,a.`name`,a.`surname`,b.`points` 
                    FROM `table_name` a 
                    LEFT JOIN `table_name` b 
                    ON a.`id`=b.`id`';

[xtremey_ytinasni]

Yeh... like I said I'm still learning as well. I knew i didnt have to concatenate. Don't know why i was though :L Well I learned

[xtremey_ytinasni]

Is there a way to join tables that I can get all the entries from table a even if there is no entries in table b?

 

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2). ( from w3schools.org). So basically, when left joining, you would have the LEFT table's (in this instance "id") column JOIN with with the right table's column, thus LEFT JOIN Well, im just talkin from what it sounds like, and thats always the way i've used it and it works fine so dont see a problem.

$sql_emp = 'SELECT a.`id`,a.`name`,a.`surname`,b.`points` '.
                    'FROM `table_name` a '.
                    'LEFT JOIN `table_name` b '.
                    'ON a.`id`=b.`id`';

 

That should work. Honestly you don't have to break it by lines, but I find that this makes it much easier to edit the query, and much easier to read. Just a preference, but looks much cleaner to me. So anyway, you should now be able to use that sql for query and grab the data with thier respective column names.

 

I think it might help you to know that you do not need to concatenate pieces of a string together like that just because you want to break the SQL statement up into seperate lines. 

 

$sql_emp = 'SELECT a.`id`,a.`name`,a.`surname`,b.`points` 
                    FROM `table_name` a 
                    LEFT JOIN `table_name` b 
                    ON a.`id`=b.`id`';

 

However, am i correct in assuming that syntax would work? I didn't test it, just used knowledge of writing SQL.

[gizmola]

Yes that looks fine to me.  I personally dislike putting `...` around every name.  That exists to allow you to use mysql keywords as names of tables and columns, but is not necessary 99.9% of the time.  It's more typing, and is harder to read, so I never use it.  Point in fact, for the SELECT list you only need to include the alias if there's a column name conflict between the two tables as well.  let's say that you get name and surname from a "names" table, and the points table would not have those columns, then you can just do :

 

SELECT a.id, name, surname, points....

 

With that said, the syntax looks like it would work fine.