[RESOLVED] Left Join provoking strangle desires

sergiox

Hello,

I must fix a bug (it's not the only one) that some "developers" have left in my client site. There is a query that uses left join for getting the info:

$sql_li=executeQuery("select tbl_company.* from  tbl_company 
left join tbl_time_off on(tbl_company.id=tbl_time_off.user_id) 
left join tbl_per_timeoff on(tbl_company.id=tbl_per_timeoff.user_id) 
where (tbl_per_timeoff.day='$day_name' or tbl_time_off.schedule_date='$da')
group by tbl_company.id");

As you can see there are 3 tables: tbl_company, tbl_per_timeoff, tbl_time_off.

The result of the query is that all companies are getting info of the time-off related to other companies employees.

In the tbl_company, there is the ID of every company and the ID's of the employees related to each company.

In the tbl_time_off there is an ID, that is not more than the ID of the time-off request, an user_id, and nothing else to share in a left join as I understand that.

In the tbl_per_timeoff we have the same thing, the ID of the per timeoff, and the user_id.

So, I think I must look for the employees related to the ID of the company, and after that, look in the other tables for those employees IDs.

I'm looking for help because I'm really near a collapse :queasy: My eyes are burning, and I need to finish this thing to rest a bit before to start another project.

Is there a simple solution for that? (I know the answer is yes, but I'm too tired to see it) Possibly avoiding the left join thing?

Well, any help will be very wellcome and appreciated.

Thanks,

Sergio

laserlight

So, is tbl_company.id the id of a company of the id of an employee?

I think there is a bug is the database design that you inherited: most likely there should have been a separate table for employees.

sergiox

Hello Laserlight. Yep, there doen't exist a table "users". If you see the database structure, is clear the it was poorly designed. And not only the database: some forms are really a mess. The contact us form is sending the "name" of the sender, what is always [username], hahaha. And it's only one example. The PHP code is overwriten the CSS, where isn't needed, and so on...

laserlight;10913378 wrote:
So, is tbl_company.id the id of a company of the id of an employee?.

There is a mixture. The tbl_company.id is the company id (?), and at the same time, is the manager ID. Yep, there is a bug. So, when you create a company, will be created a user (????) who will be the manager, and will be related to the company ID, that is the same of his own ID as user :eek:
Every employee has an ID, and is related to a company ID. In other words, to the company manager. Uf!!!

Cheers

laserlight

sergiox wrote:
Each user, manager or employee, has an id, and the related company_id in the table.

Ah, then the problem is that a tbl_company.company_id=:company_id clause to restrict the results to only those employees from the given company is missing.

sergiox

It means that I must add the restriction. Nice. I'll try without breaking the rest of the clauses 😛

I'm new to left join, so i'll read a bit more before inserting the clause.

Thanks a lot! I'll check "resolved" when resolved.

PD: Did you read the Edit of my other post?