September 22, 2008 at 5:35 am
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
September 22, 2008 at 6:04 am
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
September 22, 2008 at 6:27 am
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
September 22, 2008 at 6:33 am
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
September 22, 2008 at 6:44 am
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
September 22, 2008 at 7:03 am
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
September 22, 2008 at 7:13 am
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
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
September 24, 2008 at 9:56 am
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
September 24, 2008 at 10:21 am
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
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
September 24, 2008 at 10:37 am
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
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,'')
September 26, 2008 at 8:10 am
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
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply