CODEWITHSUNDEEP
sqlsql-serverjoin
Harindra Singh
Harindra Singh

Reputation: 371

Join three tables based on a single common column and select max date value

I have SQL Server tables defined like this:

create table users
(
    user_id int NOT NULL IDENTITY(1,1),
    name varchar(200),
    username varchar(200),
    password varchar(50),
    encrypted_password varchar(100),
    email varchar(30),
    phone_no int,
    address varchar(200),
    PRIMARY KEY(user_id)    
)

create table electricity
(
    table_name VARCHAR(50),
    electricity_bill_id int NOT NULL IDENTITY(1,1),
    billing_month_year varchar(50),
    units_consumed int,
    user_id int,
    account_number varchar(100),
    amount varchar(100),
    due_date  varchar(100),  
    PRIMARY KEY(electricity_bill_id),
    FOREIGN KEY(user_id) REFERENCES users(user_id)     
);

insert into electricity (table_name, billing_month_year, units_consumed, user_id, account_number, amount, due_date) 
values ('Electricity','2015-05-22',13,1,'acc01',2500,'2015-06-22');

create table water
(
    table_name VARCHAR(10),
    water_bill_id int NOT NULL IDENTITY(1,1),
    billing_month_year_date varchar(50),
    units_consumed int,
    user_id int,
    account_number varchar(100),
    amount varchar(100),
    due_date varchar(100), 
    PRIMARY KEY(water_bill_id),
    FOREIGN KEY(user_id) REFERENCES users(user_id)    
)

insert into water(table_name, billing_month_year_date, units_consumed, user_id, account_number, amount, due_date)
values ('Water','2015-04-22',17,1,'acc01',2500,'2015-05-14');

create table telephone
(
    table_name VARCHAR(10),
    telecom_bill_id int NOT NULL IDENTITY(1,1),
    bill_Period varchar(100),
    user_id int,
    account_number varchar(100),
    amount varchar(100),
    issued_date varchar(50),
    due_date varchar(50), 
    PRIMARY KEY(telecom_bill_id),
    FOREIGN KEY(user_id) REFERENCES users(user_id)    
)

insert into telephone(table_name, bill_Period, user_id, account_number, amount, issued_date, due_date)
values ('Telephone', '2015-04-22', 1, 'acc01', 2500, '2015-05-14', '2015-05-18');

The problem is that I want to join electricity, water and telephone tables based on the user id that I need to pass in to the query.

And also I want to pick the maximum due_date from the three tables. But, I've some issues with it.

Please can somebody help me.

Upvotes: 1

Views: 74

Answers (2)

NoAnOld
NoAnOld

Reputation: 171

If I understand your question well, you want to do something like this:

SELECT MAX(e.due_date) AS ElectricityDueDate, MAX(w.due_date) AS WaterDueDate, MAX(t.due_date) AS TelephoneDueDate 
FROM user u, electricity e, water w, telephone t
WHERE u.user_id=@user_id AND
e.user_id=u.user_id AND
w.user_id=u.user_id AND
t.user_id=u.user_id AND
GROUP BY u.user_id

However, to do so you should change the due_date type to DATE, in this way you can compute the max value.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271111

One method is to use outer apply:

select u.*, e.*, w.*, t.*
from users u outer apply
     (select top 1 e.*
      from electricity e
      where e.user_id = u.user_id
      order by e.due_date desc
     ) e outer apply
     (select top 1 w.*
      from water w
      where w.user_id = u.user_id
      order by w.due_date desc
     ) w outer appy
     (select top 1 t.*
      from telephone t
      where t.user_id = u.user_id
      order by t.due_date desc
     ) t 
where u.user_id = @user_id;

With indexes on (user_id, due_date) on all three tables, the query should have very good performance as well.

Upvotes: 1

Related Questions