|
|
![]() |
| Thread Tools | Search this Thread |
#1
| |||
| |||
Bdd with LEFT JOIN, sorting on two fields I have a problem with one of my BDD. I have 4 tables linked together: 1: Artists id name first_name pseudo ... 2: works id Artist (value Artists.id) title format 3: styles id name (example 1: paint; 2: Photo 3: Textile ,...) 4: categories (table connecting to works - styles) work style (a work which may belong to different styles like painting + photo) List of different styles with names of artists with at least one work in this style: Pottery Smith, John Painting Smith, John Chappuis, Martin Photography Dubois, Claude Niepce, Nicephore Code: // create a SQL query for the list of categories $query = "SELECT * FROM categories ORDER BY name"; $result = mysql_query ($query) or die ("Problem of the request. Error:".mysql_error()); while ($line = mysql_fetch_array ($ result)) { echo "\n<p>$line[name]</p>"; // create a SQL query for the list of artists $ query2 = "SELECT DISTINCT a.id, a.name, a.pseudo FROM ((works o LEFT JOIN l_works_cat l ON o.id=l.loc_work) LEFT JOIN artists ON o.artist = a.id) WHERE l.loc_cat = $line [id] "; $ result2 = mysql_query ($ query2) or die ( "Problem of the request. Error:". mysql_error ()); while ($ line2 = mysql_fetch_array ($ result2)){ echo "\n <a <p> href='artists.php?id=$line2[id]'> $ line2 [name], $ line2 [firstname] </ a> </ p>"; } } I would like for the names of artists, if a username is defined, the name displayed and not the name. That's easy. The concern is to sort alphabetically by artist name or pseudo. I guess going through a UNION query but I do not see how to integrate. Help me? |
#2
| |||
| |||
Re: Bdd with LEFT JOIN, sorting on two fields If you arrive to select at your request the name or pseudo with a SQL style decode, case, iif, if ... level then it becomes sort quite simple => Order by <replace (name/pseudo) in select> eg select CHP1, CHP2, decode (MyReferrals = 1, name, pseudo) from MyTable order by 3 or select CHP1, CHP2, chp3, CHP4, decode (MyReferrals = 1, name, pseudo) from MyTable order by 5 |
#3
| |||
| |||
Re: Bdd with LEFT JOIN, sorting on two fields OK, I managed it using a condition IF: $query2 = "SELECT DISTINCT a.id, IF (a.pseudo <>'', a.pseudo, CONCAT (a.name, ',' a.pseudo)) FROM my_name ((works o LEFT JOIN l_works_cat l ON o.id = l.loc_works) LEFT JOIN artists ON o.artists = a.id) WHERE l.loc_cat = $line[id] ORDER BY my_name "; |
![]() |
|
Tags: bdd, left join, sql |
Thread Tools | Search this Thread |
|
![]() | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can?t join friends in Max Payne 3, error "Join Failed Failure: Blacklisted. | G.Kahli | Video Games | 3 | 02-06-2012 12:44 PM |
sorting linkedlist | duper | Software Development | 1 | 05-06-2011 03:30 AM |
Sorting an Array in PHP | Gomeler | Software Development | 3 | 31-10-2009 12:10 PM |
XSL Sorting Question | pbrstreetgang | Software Development | 1 | 08-09-2009 08:39 PM |
Equi Join vs Natural Join in SQL | Demetrius | Software Development | 3 | 14-05-2009 12:55 PM |