left join issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

left join issue

Author

Topic

adao.monteiro
Starting Member

3 Posts

Posted - 2012-04-12 : 04:46:35

Hello all,

I'm new to the forum so I hope I'm posting in the right spot.

My scenario:

Table "meters" - meter description

meter_id name serial number
1 a 123
2 b 345
3 c 678
4 d 912

Table "Data" - meter data

id meter_id timestamp value
12 2 '2012-05-09' 12.3
13 3 '2012-05-09' 12.5
14 4 '2012-05-09' 12.9

My query:

SELECT meters.meter_id, meters.meter_name, meters.serial_number, data.data_date, data.value
FROM meters INNER JOIN data ON meters.meter_id = data.meter_id
where data.data_date='2012-04-09'

This query only returns the meters that have data on that particular day, but what I need is to return all meters with or without data for that day.

What is the query for this situation? Or is there a better table organization?

My other aproach to get all meters with or without data involves a query for each meter. I have 1000 now but I plan to have 5000 so a query for each one would be very slow (it's for a web server project).

Any ideas?

Thanks,

Adam

xDil
Starting Member

2 Posts

Posted - 2012-04-12 : 05:37:03

1. use left join instead of inner join.
2. Hope meter_id is the primary key on Meters table. If not, do it.
Index the MeterData table on meterID which is the foriegn key. This will be good enough for tens and twenty thousands of rows. Indexing should be the key to get things working faster in your case. Without the index it will crawl when you have a thousands of rows in the detail table (meter data).

Adil Azeez

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-12 : 06:45:47

If you want all meters irrespective of the day then whats the point of using the date in your Where Condition.
Drop the where condition and just write the query as below and it will give you all meters which are present in both tables:


SELECT meters.meter_id, meters.meter_name, meters.serial_number, data.data_date, data.value
FROM meters JOIN data ON meters.meter_id = data.meter_id

If this is not what you wanted, then you can still get all the meters irrespective of date and irrespective of which table they are in by using a left join as follows(without the Where Condition):


SELECT meters.meter_id, meters.meter_name, meters.serial_number, data.data_date, data.value
FROM meters LEFT JOIN data ON meters.meter_id = data.meter_id

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

adao.monteiro
Starting Member

3 Posts

Posted - 2012-04-12 : 07:56:01

quote:
Originally posted by xDil

1. use left join instead of inner join.

I had already tried that. The query I posted should have been:

SELECT meters.meter_id, meters.meter_name, meters.serial_number, data.data_date, data.value
FROM meters LEFT JOIN data ON meters.meter_id = data.meter_id
where data.data_date='2012-04-09'

But still same result.

quote:
Originally posted by xDil
2. Hope meter_id is the primary key on Meters table. If not, do it.

already done.

quote:
Originally posted by xDil
Index the MeterData table on meterID which is the foriegn key. This will be good enough for tens and twenty thousands of rows. Indexing should be the key to get things working faster in your case. Without the index it will crawl when you have a thousands of rows in the detail table (meter data).

Do you mean something like this?

http://odetocode.com/articles/70.aspx

Your suggestions fix the speed of the queries if I have to do many queries at the same time.

But I was looking for a query that would give me all the meters values in one particular day, regardless of having data. So if a meter does not have data for that day it will display null.

thanks

Adam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-12 : 13:27:56

When you use the WHERE clasue on an outer join you basically turned it into an inner join. Try moving the where predicate to the join condition:

SELECT meters.meter_id, meters.meter_name, meters.serial_number, data.data_date, data.value
FROM meters 
LEFT JOIN data 
ON meters.meter_id = data.meter_id
AND data.data_date='2012-04-09'

adao.monteiro
Starting Member

3 Posts

Posted - 2012-04-13 : 07:27:26

quote:
Originally posted by Lamprey

When you use the WHERE clasue on an outer join you basically turned it into an inner join. Try moving the where predicate to the join condition:
SELECT meters.meter_id, meters.meter_name, meters.serial_number, data.data_date, data.value
FROM meters
LEFT JOIN data
ON meters.meter_id = data.meter_id
AND data.data_date='2012-04-09'

Hello Lamprey,

It worked!!

Thanks for the help!

Adam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources