Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Join in sql


My join query is below

select Scheduled_Date,CE.Date
 from dbo.maintenanceOrders o  inner join tenantRentals tr on tr.primaryKey = o.rentalUnitID
                                            inner join tenants T on t.primaryKey = tr.tenantID
                                           Inner JOIN CalenderEvent CE on CE.tenantID = T.primaryKey

I do have maintanceorders  table contain rentalUnitID and scheduledate.
tenantRentals contrain rentalunitID,and primarykey
Tenant table contain primarykey
and calcnder event table also contain TenantID ,Date.

I am trying to join this tbale but at the end it is giving me 0 result if I eliminate calnder event table than I am getting schedule date perfect but with calnder event I am not able to get schedule_date and Date.

Please help me out with this.I already wasted so much time.
           
0
Itgirl16
Asked:
Itgirl16
  • 2
  • 2
  • 2
  • +1
2 Solutions
 
Chris MangusDatabase AdministratorCommented:
I would verify that you have valid data.

Try:  Select * From CalendarEvent Where tenantID In (Select primaryKey From Tenants)

if you don't get back any rows then you haven't selected the correct joining columns.
0
 
Daniel WilsonCommented:
It sounds like there are no CalenderEvent records that match any tenants.

I would take a careful look at the data in CalenderEvent to see what's failing to match up.

Now ... if you want to data regardless of whether there's a CE record for it, a LEFT join would do:

select Scheduled_Date,CE.Date
 from dbo.maintenanceOrders o  inner join tenantRentals tr on tr.primaryKey = o.rentalUnitID
                                            inner join tenants T on t.primaryKey = tr.tenantID
                                           LEFT JOIN CalenderEvent CE on CE.tenantID = T.primaryKey
0
 
mimran18Commented:
Hi
    The ID (primaryKey) of the tenants table doesnot exits in the Calendar events, that why when you eliminate calendar event you got the data.

Check the IDs between these 2 table.

or  make it like this

select Scheduled_Date,CE.Date
 from dbo.maintenanceOrders o  inner join tenantRentals tr on tr.primaryKey = o.rentalUnitID
                                            inner join tenants T on t.primaryKey = tr.tenantID
                                           Left JOIN CalenderEvent CE on CE.tenantID = T.primaryKey
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Itgirl16Author Commented:
no I am getting result for this query so what should be the problem.....
0
 
Itgirl16Author Commented:
@DanielWilson: I tried same query with left otuer join and it's giving me proper shcedule date result but not giving me Date of calender table.....
0
 
Daniel WilsonCommented:
Then it would appear that none of the tenants who have MaintenanceOrders have CalenderEvents.
0
 
Chris MangusDatabase AdministratorCommented:
Back to my original suggestion.  I believe you might not have the joining columns correct.  Are you absolutely certain that the joining columns are CalendarEvent.tenantID and tenants.primaryKey?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now