Multiple Left JOIN in One SQL Query

Two Tables
1. w6h8a_community_favloc
2. w6h8a_csearchm_rides

SELECT CONCAT( (
SELECT statecode
FROM w6h8a_states
WHERE w6h8a_states.state = f1.states
), '|', f1.city, '|', f1.zip, '|', r.event_type, '|', f1.address1, '|', f2.address1, '|', '', '|', (
SELECT statecode
FROM w6h8a_states
WHERE w6h8a_states.state = f2.states
), '|', f2.city, '|', f2.zip, '|', '', '|', f1.name, '|', f2.name, '|', r.ride_name ) AS value, r.ride_name AS text
FROM w6h8a_csearchm_rides AS r
LEFT JOIN w6h8a_community_favloc AS f1 ON f1.name = r.pickup_fav_loc_name
AND f1.userid = r.user_id
LEFT JOIN w6h8a_community_favloc AS f2 ON f2.name = r.dropoff_fav_loc_name
AND f2.userid = r.user_id
WHERE r.user_id =82
GROUP BY r.id

It will not getting right output to generating value string.

Solution : 

SELECT CONCAT( (
SELECT statecode
FROM w6h8a_states
WHERE w6h8a_states.state = f1.states
), '|', f1.city, '|', f1.zip, '|', r.event_type, '|', f1.address1, '|', f2.address1, '|', '', '|', (
SELECT statecode
FROM w6h8a_states
WHERE w6h8a_states.state = f2.states
), '|', f2.city, '|', f2.zip, '|', '', '|', f1.name, '|', f2.name, '|', r.ride_name ) AS value, r.ride_name AS text
FROM (
(
w6h8a_csearchm_rides AS r
LEFT JOIN w6h8a_community_favloc AS f1 ON f1.name = r.pickup_fav_loc_name
AND f1.userid = r.user_id
)
LEFT JOIN w6h8a_community_favloc AS f2 ON f2.name = r.dropoff_fav_loc_name
AND f2.userid = r.user_id
)
WHERE r.user_id =82
GROUP BY r.id

In this you have to put two braces after FROM and complete first braces after first join and second braces after second join like this it will working perfectly.

Sorting strings and numbers(varchar type column) in SQL query

Problem :
                  I have a problem with sorting by a string-column when i use SQL query on MySQLServer. There are both numbers, strings and mixture of both in this column and when i sort it numbers are not looks in sorting. ex. 7.5 6 14 8.5 9. I would like to sort numbers numerically and both strings and mixed alphabetically, 14 6 7.5 8.5 9.


Soluation :

Set your query in sorting by using "order by '0000000000'+rtrim(FEILDNAME)" 

Where FEILDNAME is your varchar type column name which you want to sort. This will build up a string for the numbers with leading zeros, so they will get sorted correctly.

Second Highest Value SQL query

select max(FIELDNAME)
from TABLENAME
where FIELDNAME not in
(select max(FIELDNAME)
from TABLENAME);

SQL Server - An identity feild can have negative seed value and negative increments also

Always everyone will start identity value from 1 and increment by 1. However, an identity column can have negative seed value and can be negatively also incremented. Suppose below table will have 4 rows with id values -100,-101,-102,-103

declare @t table

(

Id int identity(-100,-1),

Col1 int

)

insert into @t values (1),(2),(3),(4)

select * from @t

Insert, Update and Delete Query in Joomla

Now its Insert, Update and Delete Query in Joomla,

For INSERT query in Joomla structure
$db = &JFactory::getDBO();
             $insert_query = "INSERT into #__TABLENAME(feild1,feild2) values('value1','value2') ";
             $db->setQuery( $insert_query );
             $db->query();

For UPDATE query in Joomla structure
             $update_query = "UPDATE #__TABLENAME SET feild1 = 'value1' ";
             $db->setQuery( $update_query );
             $db->query();

For DELETE query in Joomla structure
             $delete_query = "DELETE FROM #__TABLENAME WHERE feild1 = 'value1' ";
             $db->setQuery( $delete_query );
             $db->query();

SQL Query in Joomla

Hi to everyone,

In Joomla you create sql query easy way. Only you have to get DBO connection to start database connection only one time in a page.

$db = &JFactory::getDBO();
        $query = "select * from #__TABLENAME";
        $db->setQuery($query);
        $rows = $db->loadObjectList();

in which "TABLENAME" is your joomla table name and "#__" is behalf of "jos_". "jos_" is predefined value in every jomla table. $rows is an array in which all records has been fetched from database.