sql - Select QUERY inner joining Fields to get text values instead of ID's -


i have table looks this...

tbl1 user           originalvalue   newvalue            fieldname mikes          11              12                  dept bobl           140             141                 position johns          11 south st     13 south st         address jenb           9               12                  amountpaid billys         133             132                 seller

here's table looks like. in case user edited record, saved original values , new values. here's i'm trying do. need able display text instead of id's (numbers). exception of (in example) amountpaid, , address - values in tbldefinition looks this.....

tbldefinition id              name             field 9               somevalue        somex 10              somevalue        somex 11              accounting       dept 12              finance          dept 132             microsoft        seller 133             apple            seller 140             manager          position 141             entry            position

so wanted include bunch of data illustrate tbldefinition looks like. i'm trying somehow able use field , id tbl1 , join tbldefinition text values. need use fieldname because of values might appear in original , new values such fieldname = amountpaid have corresponding values in tbldefinition, need display value - in case 9, , 12.

select originalvalue,  newvalue, field tbldefinitions left join tbldefinitions on (tbl2.originalvalue = tbldefinitions.id , tbl2.newvalue=tbldefinitions.id)

here's i've been trying not going anywhere - i'm having hard time figuring out if can in 1 query, or if need multiple unions each field has corresponding text value in tbldefinitions or what

edit. maybe like....

select t2.name originalvalue  tbl1 t1 inner join tbldefinitions t2 on t2.id = t1.originalvalue field in ('dept', 'position', 'seller')

maybe long specify fields for, won't effect other values such amount paid in tbl1

if understanding correctly, should join definition table twice, once original name , once new name. 

select tbl2.originalvalue     ,tdo.name originalname     ,tbl2.newvalue     ,tdn.name newname     ,tbl2.user tblbhh_hxfieldchanges tbl2 left join tbldefinitions tdo         on tbl2.originalvalue = tdo.id left join tbldefinitions tdn          on tbl2.newvalue = tdn.id

Comments

Post a Comment

Popular posts from this blog

sql server - Cannot query correctly (MSSQL - PHP - JSON) -

i have little problem on project. , turns out query result returns nothing when value of first name or last name has 'ñ' in it. here's code config.php: <?php ini_set('mssql.charset', 'utf-8'); header('content-type: text/html; charset=utf-8'); $servername = "192.168.1.21"; /* ip add of db server */ $connection = array("database" => "db", "uid" => "?", "pwd" => "***"); $conn = sqlsrv_connect($servername, $connection); if(!$conn){ echo "connection not established."; die(print_r(sqlsrv_errors(), true)); } ?> myquery: $idnumber = $_post["idnum"]; $response = array(); $query2 = "select clname, cfname, cmname, cqualifier student cidno = '$idnumber'"; try{ $stmt2 = sqlsrv_query($conn, $query2); } catch(pdoexeption $ex){ $response["success"] = 0; $response["message"] = "d...
Read more

php - trouble displaying mysqli database results in correct order -

i have table in database stores events , date expire. want display events on website display current events first expired events after? i have tried following sql $sql = "select noticeid, notice, noticedesc, noticeimg, noticedate, expirydate tbl_notices group noticeid ,expired order expirydate asc"; but returns expired results first due expirydate being ordered asc in query. my tbl_notice structure looks this noticeid => int(4) notice => varchar(100) noticedesc => text noticedate => timestamp noticeimg => varchar(40) expirydate => datetime expired => int(1) 0 current or 1 expired my php code is $sql = "select noticeid, notice, noticedesc, noticeimg, noticedate, expirydate tbl_notices group noticeid ,expired order expirydate asc"; $result = mysqli_query($conn,$sql); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { ...
Read more

C++ Linked List -

i trying create linked list download txt file , use linked list handle file line line. when handling downloaded linked list operations performed on such text editor would. encountering problems however. seems "node(string value)" section of code has wrong though original node() declaration no arguments passes. unable figure out quite is. node.h class node { public: node(); node(string value); void setnext(node *nextnode); // allows user set "next" pointer of node points friend class linkedlist; private: string data; // data box node* next; // pointer box }; node.cpp # include <string> # include "node.h" using namespace std; node::node() { data = ""; next = null; } node::node(string value) { data = value; next = null; } void node::setnext(node *nextnode) // allows user set "next" pointer of node points { this->next = nextnode; } your #include <string> ...
Read more