SELECT

Question

Post reply

SELECT

bdh-550389

Old Hand

Points: 380
More actions
September 22, 2008 at 5:35 am

#193371

Hi All
I am trying to write a select statement that has to return data from fields that might not be filled in yet. So far I have relied on the fields to be filled and then anded a lot of statements together like so:
SELECT index, B.Value
FROM table1,A.table2, B.Table2
WHERE index = A.index AND
index = B.index2 AND
B.Value = 10
But lets say that there isent anything in B.Value = 10. This means that when I run this query no rows are returned. But I need to rewrite this SELECT so that I get the index even if there is no value in B.value.
I hope somebody can help me.
Kind regards
Bo

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

I may be missing something, but if you don't need the where condition why don't you remove it?

SELECT index, B.Value

FROM table1,A.table2, B.Table2

WHERE index = A.index AND

index = B.index2

Also you should really be using Inner Join to join tables rather than put the join conditions in the where clause.

Post some sample data and I should be able to help further

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 2

This looks like a legacy join syntax..

But as Steveb says, don't use leagacy joinsyntax, start using ANSI joins instead.

It seems that what you're after is an outer join in this case.

SELECT A.index,

B.Value

FROM table1 A

LEFT JOIN table2 B

ON A.index = b.index2

AND B.Value = 10

...something like that.

However, without any knowledge of your actual tables and data, it's hard to be more specific.

/Kenneth

bdh-550389 Old Hand Points: 380 More actions · Answer 3

Hi SteveB

I need the where clause. I will try to make some simple sample data that might clearify my problem.

I have 2 tables where my system stores data:

entity

e_string

The tables have the following columns and data:

Entity

entity_id,template_id,name

1,34,'incident1'

2,34,'incident2'

3,34,'incident3'

e_string

es_id,es_entity,es_attribute, es_value

1,1,125,'data1'

2,1,126,'data2'

3,2,125,'data3'

4,2,126,'data4'

5,3,126,'data6'

My SELECT so far is

SELECT entity_id, B.es_value, C.es_value

FROM entity, B.e_string, C.e_string

WHERE entity_id = B.es_entity AND

B.es_attribute = 125 AND

entity_id = C.es_entity AND

C.es_attribute = 126

My results are:

1,'Data1','Data2'

2,'Data3','Data4'

But I want

1,'Data1','Data2'

2,'Data3','Data4'

3,'','Data6'

I hope this gives you a better idea of what I need.

Regards

Bo

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

You don't want to join in the where clause. It's an old join style and outer joins (which you need here) are not supported any longer.

Does this work?

SELECT entity_id, ISNULL(B.es_value,''), ISNULL(C.es_value,'')

FROM entity

LEFT OUTER JOIN e_string AS B ON entity_id = B.es_entity AND B.es_attribute = 125

LEFT OUTER JOIN e_string AS C ON entity_id = C.es_entity AND C.es_attribute = 126

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

bdh-550389 Old Hand Points: 380 More actions · Answer 5

It works

I think I am starting to see the error of my previous way of join tables ;). But now I have a lot of statements I have to rewrite...

Tank you all

Regards

Bo

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Here's a little sample data in case this thread is revisited.

[font="Courier New"]SET NOCOUNT ON

DROP TABLE #Entity

CREATE TABLE #Entity (entity_id INT, template_id INT, [name] VARCHAR(20))

INSERT INTO #Entity (entity_id, template_id, [name])

SELECT 1,34,'incident1' UNION ALL

SELECT 2,34,'incident2' UNION ALL

SELECT 3,34,'incident3'

--SELECT * FROM #Entity

DROP TABLE #e_string

CREATE TABLE #e_string (es_id INT, es_entity INT, es_attribute INT, es_value VARCHAR(10))

INSERT INTO #e_string (es_id, es_entity, es_attribute, es_value)

SELECT 1,1,125,'data1' UNION ALL

SELECT 2,1,126,'data2' UNION ALL

SELECT 3,2,125,'data3' UNION ALL

SELECT 4,2,126,'data4' UNION ALL

SELECT 5,3,126,'data6'

--SELECT * FROM #e_string

--My SELECT so far is

SELECT entity_id, B.es_value, C.es_value

FROM #Entity, #e_string B, #e_string C

WHERE entity_id = B.es_entity AND

B.es_attribute = 125 AND

entity_id = C.es_entity AND

C.es_attribute = 126

/*

My results are:

1,'Data1','Data2'

2,'Data3','Data4'

But I want

1,'Data1','Data2'

2,'Data3','Data4'

3,'','Data6'

*/

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bdh-550389 Old Hand Points: 380 More actions · Answer 7

Hi Again

Now I hope you still see this.

But I have done what I been told and I use LEFT OUTER JOINS. But now the query takes 10 min to exec instead of 15 sec before. Is there a reasonable explanation for this?

/Bo

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Bo, we're a little short on information here - as an absolute minimum, would it be possible for you to post your query? All of it?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bdh-550389 Old Hand Points: 380 More actions · Answer 9

Hi

Sorry about that, I don't know what I was thinking. I might have over done it with the JOINS, however I don't know I else to combine all my data.

There are index'es on most of the ID's. When I made the same query with the generics approch I show earlier, the query took 15 sec or so. But now I am approching 10 min.

SELECT A.entity_id AS 'index',
ISNULL(B.es_value,0) AS 'Efecte_id',
9 AS 'Sagstype',
ISNULL(C.er_target_id,0) AS 'Kunde',
ISNULL(D.er_target_id,0) AS 'Kategori',
ISNULL(O.ss_value,'') AS 'Status',
ISNULL(E.er_target_id,0) AS 'Bruger',
ISNULL(H.er_target_id,0) AS 'Bruger_afdeling',
J.er_target_name,
K.entity_name,
L.es_value,
ISNULL(I.entity_name,'') AS 'Bruger land',
M.ed_value AS 'Oprettet den',
P.ed_value AS 'Lukket den',
S.er_target_id AS 'Efecte_user_id'
FROM entity A
LEFT OUTER JOIN entitydata_string B ON A.entity_id = B.es_entity AND
B.es_class_attribute = 20
LEFT OUTER JOIN entitydata_reference C ON A.entity_id = C.er_entity AND NOT
C.er_is_target_deleted = 1 AND
C.er_class_attribute = 895
LEFT OUTER JOIN entitydata_reference D ON A.entity_id = D.er_entity AND
D.er_class_attribute = 83 AND NOT
D.er_is_target_deleted = 1
LEFT OUTER JOIN entitydata_reference E ON A.entity_id = E.er_entity AND NOT
E.er_is_target_deleted = 1 AND
E.er_class_attribute = 53
LEFT OUTER JOIN entity F ON E.er_target_id = F.entity_id AND NOT
F.entity_is_deleted = 1 AND
F.entity_template = 41--Person
LEFT OUTER JOIN entitydata_reference H ON F.entity_id = H.er_entity AND NOT
H.er_is_target_deleted = 1 AND
H.er_class_attribute = 749
LEFT OUTER JOIN entity I ON H.er_target_id = I.entity_id AND NOT
I.entity_is_deleted = 1 AND
I.entity_template = 61--Department
LEFT OUTER JOIN entitydata_reference J ON I.entity_id = J.er_entity AND NOT
J.er_is_target_deleted = 1 AND
J.er_class_attribute = 735
LEFT OUTER JOIN entity K ON J.er_target_id = K.entity_id AND NOT
K.entity_is_deleted = 1 AND
K.entity_template = 33--Location
LEFT OUTER JOIN entitydata_string L ON K.entity_id = L.es_entity AND
L.es_class_attribute = 439
LEFT OUTER JOIN entitydata_date M ON A.entity_id = M.ed_entity AND
M.ed_class_attribute = 21
LEFT OUTER JOIN entitydata_date P ON A.entity_id = P.ed_entity AND
P.ed_class_attribute = 111
LEFT OUTER JOIN entitydata_static_link N ON A.entity_id = N.esl_entity AND
N.esl_class_attribute = 82
LEFT OUTER JOIN static_string O ON N.esl_static_id = O.ss_static_value
LEFT OUTER JOIN entitydata_reference Q ON A.entity_id = Q.er_entity AND
Q.er_class_attribute = 65
LEFT OUTER JOIN entity R ON Q.er_target_id = R.entity_id AND
R.entity_template = 27 AND NOT
R.entity_is_deleted = 1 AND
R.entity_is_hidden = 0
LEFT OUTER JOIN entitydata_reference S ON R.entity_id = S.er_entity AND NOT
S.er_is_target_deleted = 1 AND
S.er_class_attribute = 372
WHEREA.entity_template = 9 AND NOT
A.entity_is_deleted = 1 AND NOT
A.entity_id IN ( SELECT Incident_index FROM BI.DBO.INCIDENT
)
ORDER BY ISNULL(C.er_target_name,'')

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Hi Bo

Thanks for providing the whole query. The next job is to identify joins which can be changed from OUTER to INNER, which will shift the run duration back towards the 15 seconds which you originally had.

If your database schema is available to you, and you have knowledge of the data, then this job is usually straightforward. But you're guessing what sort of join to use in this query, so I'll assume that you don't have it.

You're going to have to change and test, and change and test again.

Run your query as it is into a temporary table. Then change each join in turn from an outer join to an inner join, run again, and see if the results change. Don't change more than one join at a time before testing.

Start with joins to your parent table [FROM entity A].

Almost always, the rowcount of the result will tell you what you need to know.

There are shortcuts to this process. I usually test join types as a pair of tables chopped out of the whole query. Also, I organise the joins into groups (using white space) according to the functional area of the application/database which they relate to, because often you will find that a parent-child join must be an outer join, but child-grandchild joins etc are inner joins.

Here's your query reformatted slightly to make it more readable:

[font="Courier New"]SELECT A.entity_id AS 'index',

ISNULL(B.es_value,0) AS 'Efecte_id',

9 AS 'Sagstype',

ISNULL(C.er_target_id,0) AS 'Kunde',

ISNULL(D.er_target_id,0) AS 'Kategori',

ISNULL(O.ss_value,'') AS 'Status',

ISNULL(E.er_target_id,0) AS 'Bruger',

ISNULL(H.er_target_id,0) AS 'Bruger_afdeling',

J.er_target_name,

K.entity_name,

L.es_value,

ISNULL(I.entity_name,'') AS 'Bruger land',

M.ed_value AS 'Oprettet den',

P.ed_value AS 'Lukket den',

S.er_target_id AS 'Efecte_user_id'

FROM entity A --#

LEFT OUTER JOIN entitydata_string B ON A.entity_id = B.es_entity AND --#

B.es_class_attribute = 20

LEFT OUTER JOIN entitydata_reference C ON A.entity_id = C.er_entity AND NOT --#

C.er_is_target_deleted = 1 AND

C.er_class_attribute = 895

LEFT OUTER JOIN entitydata_reference D ON A.entity_id = D.er_entity AND --#

D.er_class_attribute = 83 AND NOT

D.er_is_target_deleted = 1

LEFT OUTER JOIN entitydata_reference E ON A.entity_id = E.er_entity AND NOT --#

E.er_is_target_deleted = 1 AND

E.er_class_attribute = 53

LEFT OUTER JOIN entity F ON E.er_target_id = F.entity_id AND NOT

F.entity_is_deleted = 1 AND

F.entity_template = 41--Person

LEFT OUTER JOIN entitydata_reference H ON F.entity_id = H.er_entity AND NOT --#

H.er_is_target_deleted = 1 AND

H.er_class_attribute = 749

LEFT OUTER JOIN entity I ON H.er_target_id = I.entity_id AND NOT --#

I.entity_is_deleted = 1 AND

I.entity_template = 61--Department

LEFT OUTER JOIN entitydata_reference J ON I.entity_id = J.er_entity AND NOT --#

J.er_is_target_deleted = 1 AND

J.er_class_attribute = 735

LEFT OUTER JOIN entity K ON J.er_target_id = K.entity_id AND NOT --#

K.entity_is_deleted = 1 AND

K.entity_template = 33--Location

LEFT OUTER JOIN entitydata_string L ON K.entity_id = L.es_entity AND --#

L.es_class_attribute = 439

LEFT OUTER JOIN entitydata_date M ON A.entity_id = M.ed_entity AND --#

M.ed_class_attribute = 21

LEFT OUTER JOIN entitydata_date P ON A.entity_id = P.ed_entity AND --#

P.ed_class_attribute = 111

LEFT OUTER JOIN entitydata_static_link N ON A.entity_id = N.esl_entity AND

N.esl_class_attribute = 82

LEFT OUTER JOIN static_string O ON N.esl_static_id = O.ss_static_value --#

LEFT OUTER JOIN entitydata_reference Q ON A.entity_id = Q.er_entity AND

Q.er_class_attribute = 65

LEFT OUTER JOIN entity R ON Q.er_target_id = R.entity_id AND

R.entity_template = 27 AND NOT

R.entity_is_deleted = 1 AND

R.entity_is_hidden = 0

LEFT OUTER JOIN entitydata_reference S ON R.entity_id = S.er_entity AND NOT --#

S.er_is_target_deleted = 1 AND

S.er_class_attribute = 372

WHERE A.entity_template = 9 AND NOT

A.entity_is_deleted = 1 AND NOT

A.entity_id IN ( SELECT Incident_index FROM BI.DBO.INCIDENT)

ORDER BY ISNULL(C.er_target_name,'')[/font]

Let us know how you get on, and if you have any further questions.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden