473,699 Members | 2,440 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

help with data transformations with outer join?

Hello all.

I am trying to write a query that "just" switches some data around so
it is shown in a slightly different format. I am already able to do
what I want in Oracle 8i, but I am having trouble making it work in
SQL Server 2000. I am not a database newbie, but I can't seem to
figure this one out so I am turning to the newsgroup. I am thinking
that some of the SQL Gurus out there have done this very thing a
thousand times before and the answer will be obvious to them.

This message is pretty long but hopefully gives you enough information
to replicate the issue.

There are 3 tables involved in my scenario. Potentially a lot more in
the real application, but I'm trying to keep this example as simple as
possible.

In my database I have many "things". Let's call them "User Records"
(table: users) for this example. My app allows the customer to create
any number of custom "Extra Fields" (XF's) for a given User Record.
The Extra Field definitions are stored in a table which we can call
attribs. The actual XF values for a given user record are stored in a
third table, let's call it users_attribs.

users_attribs will look something like this (actual DDL below.)

UserID | ExtraFieldID | Value
--------------------------------------
User_1 | XF_1 | ham
User_1 | XF_2 | eggs
User_2 | XF_1 | bacon
User_2 | XF_2 | cheese
User_3 | XF_2 | onions

The end result is that I want a SQL query that returns something like
this:

UserID | XF_1 | XF_2
-------------------------------------
User_1 | ham | eggs
User_2 | bacon | cheese
User_3 | NULL | onions

Potentially there would be one column for each extra field definition.
One interesting question is how to get a dynamic number of columns to
show up in results, (so new XF's show up automatically) but I'm not
worried about that for now. Assume I will hard-code a specific set of
extra fields into my query.

The key here is that all users must show up in the final result EVEN
IF they don't have some extra field value defined. Since User_3 in
the example above doesn't have an XF_1 record, we see a NULL in that
column in the final result.

With Oracle I am able to accomplish this via an Outer Join, and I know
SQL Server supports Outer Joins, but I can't seem to make it work. In
ever version I have tried so far, if any user is missing any extra
field value, the entire row for the user goes "missing", and that is
my problem.

It seems like one possible solution would be to just go ahead and
populate the users_attribs table with a NULL value for that
combination of user ID and extra field ID, basically adding a new row
like this:

UserID | ExtraFieldID | Value
--------------------------------------
User_3 | XF_1 | NULL

I would like to avoid that if possible, for a number of reasons,
particularly the question of *when* that NULL would be added. I don't
want my report to touch the database and add stuff at reporting time
if at all possible. In Oracle, I seemingly don't have to, and I want
to get to that point on SQL Server.

So, here is some specific DDL to recreate this scenario:
CREATE TABLE users (user_id varchar(60), username varchar(60));

-- Extra Field (attribs) definitions
CREATE TABLE attribs (xf_id varchar(60), xf_name varchar(60));

-- Extra Field values for Users
CREATE TABLE users_attribs (user_id varchar(60), xf_id varchar(60),
val varchar(60));

-- populate the sample tables

-- sample User recs
INSERT INTO users VALUES ('U_1', 'John Smith');
INSERT INTO users VALUES ('U_2', 'Mary Rogers');

-- sample extra field definitions
INSERT INTO attribs VALUES ('XF_1', 'Extra Field 1');
INSERT INTO attribs VALUES ('XF_2', 'Extra Field 2');
INSERT INTO attribs VALUES ('XF_3', 'Extra Field 3');

-- sample values for User Extra Fields (XF's)
-- U_1 ("John Smith") has complete values for each XF
INSERT INTO users_attribs VALUES ('U_1', 'XF_1', 'XF_1 value for
U_1');
INSERT INTO users_attribs VALUES ('U_1', 'XF_2', 'XF_2 value for
U_1');
INSERT INTO users_attribs VALUES ('U_1', 'XF_3', 'XF_3 value for
U_1');

-- U_2 ("Mary Rogers") only has one value, missing the other two..
INSERT INTO users_attribs VALUES ('U_2', 'XF_2', 'XF_2 value for
U_2');
Now, I can get what I want on Oracle, provided that I define an new
view that joins the three tables together, then do a separate query on
that view that does an outer join. I could dispense with the view,
but I don't want to hard-code the XF ID's into the query. I am fine
with hardcoding the XF names, though. (Long story.)

-- Create a User Extra Field view that joins Users
-- extra field definitons (attribs)
-- and values (users_attribs. )

CREATE VIEW u_xf_view AS
SELECT u.user_id, at.xf_name, uxf.val
FROM
users u,
attribs at,
users_attribs uxf
WHERE
uxf.user_id = u.user_id AND
uxf.xf_id = at.xf_id
-- Oracle-only outer join syntax works if you use the view:

SELECT
u.username as "User Name",
uxf1.val as "Extra Field 1 Value",
uxf2.val as "Extra Field 2 Value",
uxf3.val as "Extra Field 3 Value"

FROM
users t,
u_xf_view uxf1,
u_xf_view uxf2,
u_xf_view uxf3

WHERE
uxf1.user_id(+) = t.user_id AND
uxf1.xf_name(+) = 'Extra Field 1' AND

uxf2.user_id(+) = t.user_id AND
uxf2.xf_name(+) = 'Extra Field 2' AND

uxf3.user_id(+) = t.user_id AND
uxf3.xf_name(+) = 'Extra Field 3'
;

-- RESULTS (correct):

User Name Extra Field 1 Value Extra Field 2 Value Extra
Field 3 Value
------------- ------------------------ ------------------------
------------------------
John Smith XF_1 value for U_1 XF_2 value for U_1 XF_3
value for U_1
Mary Rogers NULL XF_2 value for U_2 NULL

2 Row(s)

So far I have not been able to get the equivalent result in SQL
Server. Like I said, I am really hoping to avoid populating those
NULL values. Can anything think of a way to replicate Oracle's
behavior here? I have tried a number of variations on the ANSI join
syntax instead of Oracle's (+) operator, but everything I tried so far
has only yielded a row when ALL extra fields are populated (or even
worse behavior.)

I greatly appreciate any assitance you may be able to give. I would be
happy to provide any additional information if I forgot to mention
something important. I apologize in advance for any broken / wrapped
lines. Thank you for taking the time to read this.

I'm going to be out of town for the next week or so, so I won't check
for a response until then, but as soon as I get back home I will check
back in the newsgroup.

Thank you!!

Preston Landers

pibble (at) yahoo (dot) com
Jul 20 '05 #1
2 2237
[posted and mailed, please reply in news]

Preston Landers (pi****@yahoo.c om) writes:
Potentially there would be one column for each extra field definition.
One interesting question is how to get a dynamic number of columns to
show up in results, (so new XF's show up automatically) but I'm not
worried about that for now. Assume I will hard-code a specific set of
extra fields into my query.
For that you would have to build the query dynamically. Since a SELECT
statement produces a table, and a table has a fixed set of columns,
a SELECT could not produce a variable set of columns. In SQL Server,
this is possible since you can build statemetns and then execute them
with sp_executesql or EXEC(). See http://www.sommarskog.se/dynamic_sql.html
if you want to learn more.
-- Oracle-only outer join syntax works if you use the view:

SELECT
u.username as "User Name",
uxf1.val as "Extra Field 1 Value",
uxf2.val as "Extra Field 2 Value",
uxf3.val as "Extra Field 3 Value"

FROM
users t,
u_xf_view uxf1,
u_xf_view uxf2,
u_xf_view uxf3

WHERE
uxf1.user_id(+) = t.user_id AND
uxf1.xf_name(+) = 'Extra Field 1' AND

uxf2.user_id(+) = t.user_id AND
uxf2.xf_name(+) = 'Extra Field 2' AND

uxf3.user_id(+) = t.user_id AND
uxf3.xf_name(+) = 'Extra Field 3'
;


And with ANSI JOINS (which I would expect to work on Oracle too):

SELECT t.username as "User Name",
uxf1.val as "Extra Field 1 Value",
uxf2.val as "Extra Field 2 Value",
uxf3.val as "Extra Field 3 Value"
FROM users t
LEFT JOIN u_xf_view uxf1 ON uxf1.user_id = t.user_id AND
uxf1.xf_name = 'Extra Field 1'
LEFT JOIN u_xf_view uxf2 ON uxf2.user_id = t.user_id AND
uxf2.xf_name = 'Extra Field 2'
LEFT JOIN u_xf_view uxf3 ON uxf3.user_id = t.user_id AND
uxf3.xf_name = 'Extra Field 3'

The key to use LEFT JOIN correctly is to understand that the entire
FROM clause, which includes the JOIN operators, are built first,
and then the WHERE clause filters that result. So if you move
uxf1.xf_name = 'Extra Field 1' to the WHERE clause, you filter way
the rows where uxf1.xf_name are NULL. When it is in the JOIN operator
you are saying "join the two tables [users] and [ux_f_view WHERE
xf_name = 'Extra Field 1'], and keep all rows in [users] even if
there is no match in the other table.

Again, I don't know about Oracle, but in SQL Server the old operators
*= and =* are highly deprecated, and LEFT|RIGHT JOIN are much preferred.
It is possible that the (+) stuff works better, but I would suggest that
you should use the ANSI-style join in Oracle as well.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
-P-
If you know how many columns "across" you'll have, this is a fairly simple query.

SELECT
u.user_id,
MAX( (CASE at.xf_name WHEN 'XF_1' then '' else NULL end) + uxf.val ) as XF_1,
MAX( (CASE at.xf_name WHEN 'XF_2' then '' else NULL end) + uxf.val ) as XF_2,
...
MAX( (CASE at.xf_name WHEN 'XF_n' then '' else NULL end) + uxf.val ) as XF_n

FROM
users u,
attribs at,
users_attribs uxf
WHERE
uxf.user_id = u.user_id AND
uxf.xf_id = at.xf_id
GROUP BY
u.user_id ;

This assumes that (NULL + 'string') evaluates to NULL, whereas (NULL || 'string') evaluates to 'string'.
If you have a dynamic number of columns across, then this solution doesn't apply.

--
Paul Horan
Sr. Architect
VCI Springfield, Mass
www.vcisolutions.com

"Preston Landers" <pi****@yahoo.c om> wrote in message news:11******** *************** ***@posting.goo gle.com...
Hello all.

I am trying to write a query that "just" switches some data around so
it is shown in a slightly different format. I am already able to do
what I want in Oracle 8i, but I am having trouble making it work in
SQL Server 2000. I am not a database newbie, but I can't seem to
figure this one out so I am turning to the newsgroup. I am thinking
that some of the SQL Gurus out there have done this very thing a
thousand times before and the answer will be obvious to them.

This message is pretty long but hopefully gives you enough information
to replicate the issue.

There are 3 tables involved in my scenario. Potentially a lot more in
the real application, but I'm trying to keep this example as simple as
possible.

In my database I have many "things". Let's call them "User Records"
(table: users) for this example. My app allows the customer to create
any number of custom "Extra Fields" (XF's) for a given User Record.
The Extra Field definitions are stored in a table which we can call
attribs. The actual XF values for a given user record are stored in a
third table, let's call it users_attribs.

users_attribs will look something like this (actual DDL below.)

UserID | ExtraFieldID | Value
--------------------------------------
User_1 | XF_1 | ham
User_1 | XF_2 | eggs
User_2 | XF_1 | bacon
User_2 | XF_2 | cheese
User_3 | XF_2 | onions

The end result is that I want a SQL query that returns something like
this:

UserID | XF_1 | XF_2
-------------------------------------
User_1 | ham | eggs
User_2 | bacon | cheese
User_3 | NULL | onions

Potentially there would be one column for each extra field definition.
One interesting question is how to get a dynamic number of columns to
show up in results, (so new XF's show up automatically) but I'm not
worried about that for now. Assume I will hard-code a specific set of
extra fields into my query.

The key here is that all users must show up in the final result EVEN
IF they don't have some extra field value defined. Since User_3 in
the example above doesn't have an XF_1 record, we see a NULL in that
column in the final result.

With Oracle I am able to accomplish this via an Outer Join, and I know
SQL Server supports Outer Joins, but I can't seem to make it work. In
ever version I have tried so far, if any user is missing any extra
field value, the entire row for the user goes "missing", and that is
my problem.

It seems like one possible solution would be to just go ahead and
populate the users_attribs table with a NULL value for that
combination of user ID and extra field ID, basically adding a new row
like this:

UserID | ExtraFieldID | Value
--------------------------------------
User_3 | XF_1 | NULL

I would like to avoid that if possible, for a number of reasons,
particularly the question of *when* that NULL would be added. I don't
want my report to touch the database and add stuff at reporting time
if at all possible. In Oracle, I seemingly don't have to, and I want
to get to that point on SQL Server.

So, here is some specific DDL to recreate this scenario:
CREATE TABLE users (user_id varchar(60), username varchar(60));

-- Extra Field (attribs) definitions
CREATE TABLE attribs (xf_id varchar(60), xf_name varchar(60));

-- Extra Field values for Users
CREATE TABLE users_attribs (user_id varchar(60), xf_id varchar(60),
val varchar(60));

-- populate the sample tables

-- sample User recs
INSERT INTO users VALUES ('U_1', 'John Smith');
INSERT INTO users VALUES ('U_2', 'Mary Rogers');

-- sample extra field definitions
INSERT INTO attribs VALUES ('XF_1', 'Extra Field 1');
INSERT INTO attribs VALUES ('XF_2', 'Extra Field 2');
INSERT INTO attribs VALUES ('XF_3', 'Extra Field 3');

-- sample values for User Extra Fields (XF's)
-- U_1 ("John Smith") has complete values for each XF
INSERT INTO users_attribs VALUES ('U_1', 'XF_1', 'XF_1 value for
U_1');
INSERT INTO users_attribs VALUES ('U_1', 'XF_2', 'XF_2 value for
U_1');
INSERT INTO users_attribs VALUES ('U_1', 'XF_3', 'XF_3 value for
U_1');

-- U_2 ("Mary Rogers") only has one value, missing the other two..
INSERT INTO users_attribs VALUES ('U_2', 'XF_2', 'XF_2 value for
U_2');
Now, I can get what I want on Oracle, provided that I define an new
view that joins the three tables together, then do a separate query on
that view that does an outer join. I could dispense with the view,
but I don't want to hard-code the XF ID's into the query. I am fine
with hardcoding the XF names, though. (Long story.)

-- Create a User Extra Field view that joins Users
-- extra field definitons (attribs)
-- and values (users_attribs. )

CREATE VIEW u_xf_view AS
SELECT u.user_id, at.xf_name, uxf.val
FROM
users u,
attribs at,
users_attribs uxf
WHERE
uxf.user_id = u.user_id AND
uxf.xf_id = at.xf_id
-- Oracle-only outer join syntax works if you use the view:

SELECT
u.username as "User Name",
uxf1.val as "Extra Field 1 Value",
uxf2.val as "Extra Field 2 Value",
uxf3.val as "Extra Field 3 Value"

FROM
users t,
u_xf_view uxf1,
u_xf_view uxf2,
u_xf_view uxf3

WHERE
uxf1.user_id(+) = t.user_id AND
uxf1.xf_name(+) = 'Extra Field 1' AND

uxf2.user_id(+) = t.user_id AND
uxf2.xf_name(+) = 'Extra Field 2' AND

uxf3.user_id(+) = t.user_id AND
uxf3.xf_name(+) = 'Extra Field 3'
;

-- RESULTS (correct):

User Name Extra Field 1 Value Extra Field 2 Value Extra
Field 3 Value
------------- ------------------------ ------------------------
------------------------
John Smith XF_1 value for U_1 XF_2 value for U_1 XF_3
value for U_1
Mary Rogers NULL XF_2 value for U_2 NULL

2 Row(s)

So far I have not been able to get the equivalent result in SQL
Server. Like I said, I am really hoping to avoid populating those
NULL values. Can anything think of a way to replicate Oracle's
behavior here? I have tried a number of variations on the ANSI join
syntax instead of Oracle's (+) operator, but everything I tried so far
has only yielded a row when ALL extra fields are populated (or even
worse behavior.)

I greatly appreciate any assitance you may be able to give. I would be
happy to provide any additional information if I forgot to mention
something important. I apologize in advance for any broken / wrapped
lines. Thank you for taking the time to read this.

I'm going to be out of town for the next week or so, so I won't check
for a response until then, but as soon as I get back home I will check
back in the newsgroup.

Thank you!!

Preston Landers

pibble (at) yahoo (dot) com

Jul 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
3131
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use SUBSTRING(ProductName, 1, CHARINDEX('(', ProductName)-2). I can get this result, but I had to use several views (totally inefficient). I think this can be done in one efficient/fast query, but I can't think of one. In the case that one query is not...
3
6412
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName, Employees.LastName, TerritoryID, Employees.EmployeeID, RegionID, ProductID from Employees
2
7129
by: Anthony | last post by:
I have an inventory database that Im trying to create a report out of the IP address are a lookup on a seperat table but I keep getting the above error can I change the table row to something to fix this or what. SELECT i.INVENTORY_ITEM_ID AS ,i.HOST_NAME AS , '' AS Flag, i.MEMO AS Comments, 'Seattle' AS City, 'Washington' AS State, CASE WHEN fv.value = 'EL EET1410' THEN '1111 3rd Ave.' WHEN fv.value = 'EL WFL17' THEN '999 3rd Ave.'...
5
3262
by: David Logan | last post by:
Hello, I am trying to construct a query across 5 tables but primarily 3 tables. Plan, Provider, ProviderLocation are the three primary tables the other tables are lookup tables for values the other tables. PlanID is the primary in Plan and Plan Provider ProviderLocation Lookups -------- ---------- ---------------- ----------- PlanID ProviderID ProviderID LookupType
0
482
by: Preston Landers | last post by:
Hello all. I am trying to write a query that "just" switches some data around so it is shown in a slightly different format. I am already able to do what I want in Oracle 8i, but I am having trouble making it work in SQL Server 2000. I am not a database newbie, but I can't seem to figure this one out so I am turning to the newsgroup. I am thinking that some of the SQL Gurus out there have done this very thing a thousand times before...
28
3300
by: stu_gots | last post by:
I have been losing sleep over this puzzle, and I'm convinced my train of thought is heading in the wrong direction. It is difficult to explain my circumstances, so I will present an identical make-believe challenge in order to avoid confusing the issue further. Suppose I was hosting a dinner and I wanted to invite exactly 12 guests from my neighborhood. I'm really picky about that... I have 12 chairs besides my own, and I want them all...
7
31556
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins" slows the system down considerably. I've tried creating a temp db, but I can't figure out how to execute two select commands. (It throws the exception "The column prefix 'tempdb' does not match with a table name or alias name used in the query.")
4
1451
by: CK | last post by:
Good Morning, I have a person table with personID. I have a personRate table with personID, rateID, and effectiveDate. I need to select fields from personRate, but I want the fields from the proper record. I need the one child record that has the most current date of the largest rateID. For example a person may have many rate records. I need the record that has
5
1450
by: Bob Stearns | last post by:
The following query runs very slowly compared to other queries on my system. If I read the explain plan and its data correctly, it is due to a table scan on animals caused by the join separated from the others by space. There is an index on both dam_bhid and recip_bhid. I have tried converting the on condition to a case statement to no good effect. Likewise reordering the conditions. Do you have any suggestions. The semantics of the join...
3
1697
by: angelnjj | last post by:
I'm going to do my best to describe purpose and what I need...here goes. I'm writing a data entry "quality" report and working. I'm trying to identify anytime a user adds a new contact to the db during a date range that already existed within the account prior to the first date of the range. When the contact existed already, the returned value should be 1, if they didn't exist the returned value should be 0 (I'll then total up all the 1s to...
0
8703
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8623
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9185
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9050
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8935
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7773
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6540
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5879
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4636
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.