Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 31-03-2009
Member
 
Join Date: Nov 2008
Posts: 1,192
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
mail
...

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>"; 
}
}
My problem:
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?
Reply With Quote
  #2  
Old 31-03-2009
Member
 
Join Date: May 2008
Posts: 945
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
Reply With Quote
  #3  
Old 31-03-2009
Member
 
Join Date: Nov 2008
Posts: 1,192
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 ";
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Bdd with LEFT JOIN, sorting on two fields"
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


All times are GMT +5.5. The time now is 04:32 AM.