Jump to content

Fairly simple query


jimg
 Share

Recommended Posts

I'm trying to generate a report that needs to access three tables for all the information. I've gotten it sort of working, but the fromatting and third table access has me baffled. I have no control of the SQL talbes, so don't tell me the database design is stupid :)Here's a simplified version of the problem:PersonsPID FName Lname1 Bill Smith2 Jim Jones3 George WilliamsLangIDLID PID LanguageID1 1 12 1 23 2 24 2 35 3 16 3 3LanguagesLanguageID Language1 English2 French3 GermanDesired result:PID Name Speaks--------------------------------1 Bill Smith English French2 Jim Jones French German3 George Williams English GermanWhat I've gotten so farSELECT Persons.PId, FName, Lname, LangID.LanguageIDFROM PersonsINNER JOIN LangIDON Persons.PId = LangID.PId1 Bill Smith 11 Bill Smith 22 Jim Jones 22 Jim Jones 33 George Williams 13 George Willaims 3Can't seem to find the syntax to lookup the languages.Language nor the formatting of the data onto single lines.

Link to comment
Share on other sites

This is the query you're looking for:SELECT Persons.PId, FName, Lname, Languages.LanguageFROM PersonsINNER JOIN LangIDON Persons.PId = LangID.PIdINNER JOIN LanguagesON LangID.LanguageID = Languages.LanguageIDor this:SELECT p.PId, p.FName, p.Lname, l.LanguageFROM Persons AS p, Language AS l, LangID AS liWHERE p.PId = li.PId AND li.LanguageID = l.LanguageIDI'm not sure what you're asking about the formatting.

Link to comment
Share on other sites

Thanks! That solved the third table lookup, but I'd like the result to list the languages on one line (per person) as shown in the "desired results". What I'd REALLY like is a table with checks under the languages, more like this (the Forum doesn't preserve tabs and spaces very well)Desired result:PID Name English French German--------------------------------1 Bill Smith x x -2 Jim Jones - x x3 George Williams x - x

This is the query you're looking for:SELECT Persons.PId, FName, Lname, Languages.LanguageFROM PersonsINNER JOIN LangIDON Persons.PId = LangID.PIdINNER JOIN LanguagesON LangID.LanguageID = Languages.LanguageIDor this:SELECT p.PId, p.FName, p.Lname, l.LanguageFROM Persons AS p, Language AS l, LangID AS liWHERE p.PId = li.PId AND li.LanguageID = l.LanguageIDI'm not sure what you're asking about the formatting.
Link to comment
Share on other sites

That type of data processing might be better done with a scripting language, do you have a scripting language available on the server to use? The general idea would be to get the set of records from the database and loop through them to build a data structure that would contain 1 entry for each person with whatever languages they speak. If you have PHP and MySQL as your database, for example, that would look like this:

<?php$sql = <<<EOTSELECT Persons.PId, FName, Lname, Languages.LanguageFROM PersonsINNER JOIN LangIDON Persons.PId = LangID.PIdINNER JOIN LanguagesON LangID.LanguageID = Languages.LanguageIDEOT;mysql_connect('server', 'user', 'pass');mysql_select_db('db');$result = mysql_select($sql);$people = array();  //initialize the arraywhile ($row = mysql_fetch_assoc($result)){  if (isset ($people[$row['PId']]))	$person = $people[$row['PId']]; // get the existing person  else  {	$person = array ( // new person	  'PId' => $row['PId'],	  'name' => $row['FName'] . ' ' . $row['Lname'],	  'English' => '-',	  'French' => '-',	  'German' => '-'	);  }  // set the language  $person[$row['Language']] = 'x';  $people[$row['PId']] = $person;}echo '<table>';echo '<tr>';echo '<td>ID</td>';echo '<td>Name</td>';echo '<td>English</td>';echo '<td>French</td>';echo '<td>German</td>';echo '</tr>';foreach ($people as $person){  echo '<tr>';  echo '<td>' . $person['PId'] . '</td>';  echo '<td>' . $person['name'] . '</td>';  echo '<td>' . $person['English'] . '</td>';  echo '<td>' . $person['French'] . '</td>';  echo '<td>' . $person['German'] . '</td>';  echo '</tr>';}echo '</table>';?>

Link to comment
Share on other sites

I'm actually working with DotNetNuke, and have no access to the server except through an SQL query as part of the Reports module.

That type of data processing might be better done with a scripting language, do you have a scripting language available on the server to use? The general idea would be to get the set of records from the database and loop through them to build a data structure that would contain 1 entry for each person with whatever languages they speak. If you have PHP and MySQL as your database, for example, that would look like this:
<?php$sql = <<<EOTSELECT Persons.PId, FName, Lname, Languages.LanguageFROM PersonsINNER JOIN LangIDON Persons.PId = LangID.PIdINNER JOIN LanguagesON LangID.LanguageID = Languages.LanguageIDEOT;mysql_connect('server', 'user', 'pass');mysql_select_db('db');$result = mysql_select($sql);$people = array();  //initialize the arraywhile ($row = mysql_fetch_assoc($result)){  if (isset ($people[$row['PId']]))	$person = $people[$row['PId']]; // get the existing person  else  {	$person = array ( // new person	  'PId' => $row['PId'],	  'name' => $row['FName'] . ' ' . $row['Lname'],	  'English' => '-',	  'French' => '-',	  'German' => '-'	);  }  // set the language  $person[$row['Language']] = 'x';  $people[$row['PId']] = $person;}echo '<table>';echo '<tr>';echo '<td>ID</td>';echo '<td>Name</td>';echo '<td>English</td>';echo '<td>French</td>';echo '<td>German</td>';echo '</tr>';foreach ($people as $person){  echo '<tr>';  echo '<td>' . $person['PId'] . '</td>';  echo '<td>' . $person['name'] . '</td>';  echo '<td>' . $person['English'] . '</td>';  echo '<td>' . $person['French'] . '</td>';  echo '<td>' . $person['German'] . '</td>';  echo '</tr>';}echo '</table>';?>

Link to comment
Share on other sites

I don't know of a way to get data in a format like that with only SQL. SQL is pretty complex and there might be a way, but I don't know what it is. I'm sure that you can write a stored procedure to do that, but I'm not sure if you have access to create the stored procedure.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...