Displaying multitable data in angularjs

Question

I am having two tables.College_Infra_items and College_infrastructure.The College_Infra_items has following structure

    col_infraId  requiredSeat   available      collegeInfrastructureId
       1         100              150                   1
       2         200              180                   2

College_infrastructure has following structure

    collegeInfrastructureId  type    name
         1                   UGC     Land Requirement(In acres)
         2                   UGC     Class rooms
         3                   AICTE   Total Area
         4                   AICTE   Loans

Now in my html on loading i am repeating College_infrastructure details . I am writing condition for types.That is if it is of UGC type then 1st two row details of College_infrastructure will load.If AICTE next two. Here is my html

In table format i am displaying details.In tbody section i am repeating College_infrastructure table for only "name" column.But i am unable to get details of requiredSeat and available because in query i am not performing join operation with College_infraItem table.

    <tbody>
    <tr role="row" class="even" data-ng-repeat="infraitem in 
    allInfraitemsByType">
    <td><strong>{{infraitem.name}}</strong></td>
    <td><input type="text"  
    name="requiredseat" data-ng-model="infraitem.requiredseat"></td>
    <td>
    <input type="text" name="available" data-ng-
    model="infraitem.available"></td>
    <td class="text-center"><button type="submit" class="btn GreenBtn" 
    data-ng-click="saveInfrastructureDetails(infraitem)"><span 
    class="glyphicon glyphicon-ok"></span>Save</button></td>
    </tr>
    </tbody>

In Query I am writing like this

    SELECT collegeInfrastructureId as collegeInfrastructureId,type as 
    type,name as name FROM college_infrastructure where type=:type
    order by collegeInfrastructureId asc";

This query only gives me College_Infrastructure table.But i want details of College_Infra_items also in same query. Can anyone tell how can i join College_Infra_items with this query so that in html repeat i can refer to College_Infra_items columns also.

StanislavL · Accepted Answer · 2017-08-24 11:05:46Z

1

SELECT ci.collegeInfrastructureId as collegeInfrastructureId,ci.type as 
type,ci.name as name ,
   cli.requiredSeat   
   ... and some more fields you need
FROM college_infrastructure ci
   LEFT JOIN College_Infra_items cli ON cli.collegeInfrastructureId=ci.collegeInfrastructureId
where type=:type
order by ci.collegeInfrastructureId asc";

Just use INNER JOIN for the second table in your query.

UPDATE. Change INNER JOIN to LEFT JOIN

edited Aug 24, 2017 at 11:05

answered Aug 24, 2017 at 8:40

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sachin HR Over a year ago

If there are 4 rows in College_infrastructure table all belonging to UGC then in result it is displaying only the names of columns which has values.I want to display all names under UGC type that is in College_infrastructure table.If value is there then that value should be present in table otherwise it should display empty.Right now only fields which has value are displayed

Sachin HR Over a year ago

Yup..It is coming now.Thank you

Nikita · Accepted Answer · 2017-08-24 10:51:04Z

1

Try this:

SELECT college_infrastructure.collegeInfrastructureId as collegeInfrastructureId,college_infrastructure.type as type,college_infrastructure.name as name ,College_Infra_items.requiredSeat 
FROM college_infrastructure left join College_Infra_items  on College_Infra_items.collegeInfrastructureId = college_infrastructure.collegeInfrastructureId
where type=:type
order by collegeInfrastructureId asc";

answered Aug 24, 2017 at 10:51

Nikita

4372 silver badges12 bronze badges

3 Comments

Sachin HR Over a year ago

I have used the same query but i need to check condition in where clause like where college_infrastructure.type =:type AND College_Infra_items.collegeId=:collegeId.Here i am trying to display data satisfying both conditions.But now it is displaying only filled data.Can u tell how to display all rows from college_infrastructure table and whatever data it is there in College_Infra_items table?

Nikita Over a year ago

Did you mean, to display records of both table whether they are common or not?

Sachin HR Over a year ago

Yeah i got solution.I was using AND in where clause instead of ON.I needed to display all rows from college_infrastructure and filled rows from college_infraitems based on collegeId.I was specifying condition in where clause

Collectives™ on Stack Overflow

Displaying multitable data in angularjs

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related