Databases
StackOverflow2013
Postsdbo
POfetch record from multi tables with FK constrains
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POfetch record from multi tables with FK constrains
    primarykey
    data
    text
    <p>i am trying to fetch data from multiple tables (3 levels). the scanario is that customer send request to the agent and agent handle it. for customer i have table customer</p> <pre><code> ----------------------------------------- customerid | customername | fk_personid 1 | abc | 1 2 | xyz | 4 ------------------------------------------ </code></pre> <p>customer send request to the agent who would handle it</p> <pre><code> ---------------------------------------- agnetid | agentname | fk_personid 6 | asd | 1 7 | fgh | 4 --------------------------------------- </code></pre> <p>both above tables are connected through their parent table person</p> <pre><code> ------------------------------------------ personid | personname | personemail 1 | abc | as@m.com 2 | xyz | vb@df.com ----------------------------------------- </code></pre> <p>now there is another table name Request like in which both forign keys are composite PK for request table</p> <pre><code> ------------------------------------------ FK_custmid | FK_agentid | requestype 1 | 1 | type one 2 | 2 | type two ----------------------------------------- </code></pre> <p>now the final thing is there are six different types of requests that are connected to request table(parent) throug FK. below is the one table for single type rest all are like the same. in the below table the above composite PK are FK's </p> <pre><code> ----------------------------------------------------------- req_id | req_FK_custmid | req_FK_agentid | requestype 1 | 1 | 1 | type one 2 | 2 | 2 | type two ---------------------------------------------------------- </code></pre> <p>ahhhhhhh now keeping all these tables in mind i have to show details about a request generated customer i.e i need </p> <pre><code> ------------------------------------------------- req_id | requestype | customer_id | agent_id 1 | type one | 1 | 1 ------------------------------------------------- </code></pre> <p>i hope you guys cod get my point and could help me in finding solution for it. i also tried it my self like </p> <pre><code> SELECT * FROM Agent,Request,Person,customer where Request.customer_idcutomer = customer.idcustomer and Request.Agent_idAgent = Agent.idAgent and Person.idPerson = customer.Person_idPerson and Person.idPerson = Agent.Person_idPerson </code></pre> <p>another approach this is incomplete as because i didnt know how to complete it further. 1 in the end part of query is hard coded which shuld be be but i dont know how get is dynamicall</p> <pre><code> SELECT request.SPOC_id, request.order_created, request.order_updated FROM request WHERE Agent_idAgent = ( SELECT agent.idAgent FROM agent WHERE agent.idAgent =1 ) LIMIT 0 , 30 </code></pre> <p>there are so many other approches i tried but every one didn't give me what i want i am basiclly stuck on a point to get the order/request type the customer has placed. </p> <p>NOTE: there are some fields that you will see in query but i didnt mention in my tables above just to save time and all the data i put is dummy and may not synchronized to each other.</p> <p>Note: if you suggest me to change the tables and theirs connections structure let me know and guid me if am doing wrong in it.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USkwk.stack
    1. USkwk.stack
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POfetch record from multi tables with FK constrains
      1. USkwk.stack
      1. VTFavorite
    1. COFrom where _"order_id"_ and _"order_type"_ came?
      singulars
      1. POfetch record from multi tables with FK constrains
      1. USGurminder Singh
    2. COthese wod be alias for requestid and request type
      singulars
      1. POfetch record from multi tables with FK constrains
      1. USkwk.stack
    3. COnow i edit my question
      singulars
      1. POfetch record from multi tables with FK constrains
      1. USkwk.stack
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload