CODEWITHSUNDEEP
sql
Sachin HR
Sachin HR

Reputation: 460

SQL Multiple table Join Query

I have two tables, College_Infra_items and College_infrastructure.

The College_Infra_items table has the following structure and sample data:

col_infraId  requiredSeat  available collegeInfrastructureId collegeId
-----------------------------------------------------------------------
   1         100              150           1                   2
   2         200              180           2                   2
   3         200              160           3                   3
   4         300              190           4                   3

College_infrastructure has the following structure and sample data:

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

Right now I am writing query like this

SELECT 
    ci.collegeInfrastructureId as collegeInfrastructureId,
    ci.type as type, ci.name as name,
    cli.requiredSeat as requiredSeat,
    cli.available as available   
FROM 
    college_infrastructure ci
LEFT JOIN 
    College_Infra_items cli ON cli.collegeInfrastructureId = ci.collegeInfrastructureId
WHERE 
    ci.type=:type AND cli.collegeId=:collegeId
ORDER BY 
    ci.collegeInfrastructureId ASC";

I am trying to display all rows from college_infrastructure table that is names column and whichever rows that are filled in required seat and available columns from college_infraitems table. But now it is returning only filled details from both tables. Irrespective of collegeId, I need to get all rows that is all names by type from the college_infrastructure table. But only two rows are returned because of the condition in where clause where I mentioned collegeId.

If college id=2 then it returns only two rows. I need to display all names from college_infrastructure table and filled data from college_infraitems table. Can anyone tell how to achieve this?

Upvotes: 1

Views: 214

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

When using LEFT JOIN, conditions on the first table go into the WHERE clause. Conditions on the second table go in the ON clause; otherwise, the outer join is turned into an inner join:

SELECT ci.collegeInfrastructureId, ci.type, ci.name,
       cli.requiredSeat, cli.available   
FROM college_infrastructure ci LEFT JOIN
     college_Infra_items cli
     ON cli.collegeInfrastructureId = ci.collegeInfrastructureId AND
        cli.collegeId = :collegeId
WHERE ci.type = :type 
ORDER BY ci.collegeInfrastructureId asc;

In addition, you don't need to use as to rename a column back to the same name it has. So, ci.type is going to be called type by default. No need to clutter the query with ci.type as type.

Upvotes: 4

Related Questions