I know this is a self join, but I can't remember exactly how it goes.
Could someone help me out?
create table A
{
int idA,
varchar(30) dataA
}
create table B
{
int idB,
varchar(30) dataB
}
create table A_B
{
int idA references A(idA),
int idB references B(idB)
}
insert into A values(1, "foobar")
insert into A values(2, "barfoo")
insert into B values(1, "a")
insert into B values(2, "b")
insert into B values(3, "c")
insert into B values(4, "d")
insert into B values(5, "e")
insert into B values(6, "f")
insert into B values(7, "g")
insert into B values(8, "h")
insert into A_B values (1, 1)
insert into A_B values (1, 2)
insert into A_B values (1, 3)
insert into A_B values (1, 4)
desired results
foobar a b c d
Thanks,
-- Rick 9 7741
Geez, have I been writing too much C code, or what. Here's the T-SQL
statements that actually work. :-)
create table #A
(
idA int,
dataA varchar(30)
)
create table #B
(
idB int,
dataB varchar(30)
)
create table ##A_B
(
idA int references A(idA),
idB int references B(idB)
)
insert into #A values(1, 'foobar')
insert into #A values(2, 'barfoo')
insert into #B values(1, 'a')
insert into #B values(2, 'b')
insert into #B values(3, 'c')
insert into #B values(4, 'd')
insert into #B values(5, 'e')
insert into #B values(6, 'f')
insert into #B values(7, 'g')
insert into #B values(8, 'h')
insert into #A_B values (1, 1)
insert into #A_B values (1, 2)
insert into #A_B values (1, 3)
insert into #A_B values (1, 4)
desired results
foobar a b c d
Thanks again,
Guinness Mann (GM***@dublin.c om) writes: I know this is a self join, but I can't remember exactly how it goes. Could someone help me out?
create table A { int idA, varchar(30) dataA }
For your next posting it would be appreciated if you actually run your
repro scripts in Query Analyzer, and fixed the syntax errors. This is
SQL, not C structs. :-)
Here a query that gives you the desired result. Notice that while you can
extend to more columns, the result set always has a fixed number of
columns, as those are the rules for SQL. For a completely dynamic result
set, you may look into RAC, www.rac4sql.net.
Anyway here is the query from hell:
SELECT A.dataA, B1.dataB, B2.dataB, B3.dataB, B4.dataB
FROM A
LEFT JOIN (A_B AB1 JOIN B B1 ON AB1.idB = B1.idB)
ON A.idA = AB1.idA
AND AB1.idB = (SELECT MIN(AB1a.idB)
FROM A_B AB1a
WHERE AB1a.idA = A.idA)
LEFT JOIN (A_B AB2 JOIN B B2 ON AB2.idB = B2.idB)
ON A.idA = AB2.idA
AND AB2.idB = (SELECT MIN(AB2a.idB)
FROM A_B AB2a
WHERE AB2a.idA = A.idA
AND AB2a.idB > AB1.idB)
LEFT JOIN (A_B AB3 JOIN B B3 ON AB3.idB = B3.idB)
ON A.idA = AB3.idA
AND AB3.idB = (SELECT MIN(AB3a.idB)
FROM A_B AB3a
WHERE AB3a.idA = A.idA
AND AB3a.idB > AB2.idB)
LEFT JOIN (A_B AB4 JOIN B B4 ON AB4.idB = B4.idB)
ON A.idA = AB4.idA
AND AB4.idB = (SELECT MIN(AB4a.idB)
FROM A_B AB4a
WHERE AB4a.idA = A.idA
AND AB4a.idB > AB3.idB)
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
In article <Xn************ **********@127. 0.0.1>, so****@algonet. se
says... For your next posting it would be appreciated if you actually run your repro scripts in Query Analyzer, and fixed the syntax errors. This is SQL, not C structs. :-)
I was in a hurry to post. <sigh> I did correct them in a follow-up
post.
Anyway here is the query from hell:
SELECT A.dataA, B1.dataB, B2.dataB, B3.dataB, B4.dataB FROM A LEFT JOIN (A_B AB1 JOIN B B1 ON AB1.idB = B1.idB) ON A.idA = AB1.idA AND AB1.idB = (SELECT MIN(AB1a.idB) FROM A_B AB1a WHERE AB1a.idA = A.idA) LEFT JOIN (A_B AB2 JOIN B B2 ON AB2.idB = B2.idB) ON A.idA = AB2.idA AND AB2.idB = (SELECT MIN(AB2a.idB) FROM A_B AB2a WHERE AB2a.idA = A.idA AND AB2a.idB > AB1.idB) LEFT JOIN (A_B AB3 JOIN B B3 ON AB3.idB = B3.idB) ON A.idA = AB3.idA AND AB3.idB = (SELECT MIN(AB3a.idB) FROM A_B AB3a WHERE AB3a.idA = A.idA AND AB3a.idB > AB2.idB) LEFT JOIN (A_B AB4 JOIN B B4 ON AB4.idB = B4.idB) ON A.idA = AB4.idA AND AB4.idB = (SELECT MIN(AB4a.idB) FROM A_B AB4a WHERE AB4a.idA = A.idA AND AB4a.idB > AB3.idB)
I was afraid it would look something like that. It almost makes me want
to restructure table A with four columns for the table B items.
Unfortunately, while the current solution always requires four columns,
future solutions may not, so I don't want to hardwire table A.
This query won't be run more than once or twice a day. Is there any
point in creating a stored procedure, or a view?
-- Rick
Well, depending on how big your actual problem is, you can use some other
solutions which may or may not be efficient. Like using a cursor to fill a
temporary table, or creating a dynamic script.
Shervin
"Guinness Mann" <GM***@dublin.c om> wrote in message
news:MP******** *************** *@news.newsguy. com... In article <Xn************ **********@127. 0.0.1>, so****@algonet. se says... For your next posting it would be appreciated if you actually run your repro scripts in Query Analyzer, and fixed the syntax errors. This is SQL, not C structs. :-)
I was in a hurry to post. <sigh> I did correct them in a follow-up post.
Anyway here is the query from hell:
SELECT A.dataA, B1.dataB, B2.dataB, B3.dataB, B4.dataB FROM A LEFT JOIN (A_B AB1 JOIN B B1 ON AB1.idB = B1.idB) ON A.idA = AB1.idA AND AB1.idB = (SELECT MIN(AB1a.idB) FROM A_B AB1a WHERE AB1a.idA = A.idA) LEFT JOIN (A_B AB2 JOIN B B2 ON AB2.idB = B2.idB) ON A.idA = AB2.idA AND AB2.idB = (SELECT MIN(AB2a.idB) FROM A_B AB2a WHERE AB2a.idA = A.idA AND AB2a.idB > AB1.idB) LEFT JOIN (A_B AB3 JOIN B B3 ON AB3.idB = B3.idB) ON A.idA = AB3.idA AND AB3.idB = (SELECT MIN(AB3a.idB) FROM A_B AB3a WHERE AB3a.idA = A.idA AND AB3a.idB > AB2.idB) LEFT JOIN (A_B AB4 JOIN B B4 ON AB4.idB = B4.idB) ON A.idA = AB4.idA AND AB4.idB = (SELECT MIN(AB4a.idB) FROM A_B AB4a WHERE AB4a.idA = A.idA AND AB4a.idB > AB3.idB)
I was afraid it would look something like that. It almost makes me want to restructure table A with four columns for the table B items. Unfortunately, while the current solution always requires four columns, future solutions may not, so I don't want to hardwire table A.
This query won't be run more than once or twice a day. Is there any point in creating a stored procedure, or a view?
-- Rick
Ok, I'm still having trouble. Let's make this even simpler. Take this
table:
create table A_B
(
idA int,
idB int
)
Insert this data into it:
insert into A_B values (1, 1)
insert into A_B values (1, 2)
insert into A_B values (1, 3)
insert into A_B values (1, 4)
insert into A_B values (2, 5)
insert into A_B values (2, 6)
insert into A_B values (2, 7)
insert into A_B values (2, 8)
Now what I want out is
idA idB1 idB2 idB3 idB4
--- ---- ---- ---- ----
1 1 2 3 4
2 5 6 7 8
How would I structure the query?
-- Rick
Guinness Mann (GM***@dublin.c om) writes: I was afraid it would look something like that. It almost makes me want to restructure table A with four columns for the table B items. Unfortunately, while the current solution always requires four columns, future solutions may not, so I don't want to hardwire table A.
This query won't be run more than once or twice a day. Is there any point in creating a stored procedure, or a view?
Certainly it would be a good idea to package it in some way, in stored
procedure, a view or even in an ASP page. Would be a bit of work to
type in that monster all the time.
But I think you should look into change the design a bit. The below
still gives a complex query, but still one which is more straight
forward to write. The change I'm making is that I make the values
in the connection table A_B known to avoid those subqueries.
CREATE TABLE orders
(orderid int NOT NULL PRIMARY KEY,
somedata varchar(20) NOT NULL)
CREATE TABLE products (
productid int NOT NULL PRIMARY KEY,
productname varchar(23) NOT NULL)
CREATE TABLE orderdetails
(orderid int NOT NULL REFERENCES orders(orderid) ,
rowno tinyint NOT NULL CHECK (rowno BETWEEN 1 AND 4),
productid int NOT NULL REFERENCES products(produc tid),
PRIMARY KEY (orderid, rowno))
go
INSERT orders (orderid, somedata) VALUES (1, 'First order')
INSERT orders (orderid, somedata) VALUES (2, 'Second order')
INSERT products (productid, productname) VALUES (1, 'Airplane')
INSERT products (productid, productname) VALUES (2, 'Banana')
INSERT products (productid, productname) VALUES (3, 'Porridge')
INSERT products (productid, productname) VALUES (4, 'Stucko')
INSERT products (productid, productname) VALUES (5, 'Lightblub')
INSERT products (productid, productname) VALUES (6, 'Transistor')
INSERT orderdetails (orderid, rowno, productid) VALUES (1, 1, 2)
INSERT orderdetails (orderid, rowno, productid) VALUES (1, 2, 5)
INSERT orderdetails (orderid, rowno, productid) VALUES (1, 3, 6)
INSERT orderdetails (orderid, rowno, productid) VALUES (1, 4, 1)
INSERT orderdetails (orderid, rowno, productid) VALUES (2, 1, 4)
INSERT orderdetails (orderid, rowno, productid) VALUES (2, 2, 3)
go
SELECT o.somedata, p1.productname, p2.productname,
p3.productname, p4.productname
FROM orders o
LEFT JOIN (orderdetails od1
JOIN products p1 ON od1.productid = p1.productid)
ON o.orderid = od1.orderid
AND od1.rowno = 1
LEFT JOIN (orderdetails od2
JOIN products p2 ON od2.productid = p2.productid)
ON o.orderid = od2.orderid
AND od2.rowno = 2
LEFT JOIN (orderdetails od3
JOIN products p3 ON od3.productid = p3.productid)
ON o.orderid = od3.orderid
AND od3.rowno = 3
LEFT JOIN (orderdetails od4
JOIN products p4 ON od4.productid = p4.productid)
ON o.orderid = od4.orderid
AND od4.rowno = 4
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
In article <Xn************ ********@127.0. 0.1>, so****@algonet. se says... But I think you should look into change the design a bit. The below still gives a complex query, but still one which is more straight forward to write. The change I'm making is that I make the values in the connection table A_B known to avoid those subqueries.
I see. Thank you. And if I needed to allow up to five, or six entries,
I would just add on another, or two more, sections. Very clever.
Thanks,
-- Rick
It occurred to me that the complex query can be even more simplfied
if you introduce a view:
CREATE VIEW orderproducts (orderid, rowno, productid, productname) AS
SELECT od.orderid, od.rowno, od.rowno, p.productname
FROM orderdetails od
JOIN products p ON od.productid = p.productid
go
SELECT o.somedata, op1.productname , op2.productname ,
op3.productname , op4.productname
FROM orders o
LEFT JOIN orderproducts op1 ON o.orderid = op1.orderid
AND op1.rowno = 1
LEFT JOIN orderproducts op2 ON o.orderid = op2.orderid
AND op2.rowno = 2
LEFT JOIN orderproducts op3 ON o.orderid = op3.orderid
AND op3.rowno = 3
LEFT JOIN orderproducts op4 ON o.orderid = op4.orderid
AND op4.rowno = 4
It is not likely that this will matter for performance. The view only
serves the purpose to make the query easier to read and maintain.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
In article <Xn************ **********@127. 0.0.1>, so****@algonet. se
says... It occurred to me that the complex query can be even more simplfied if you introduce a view:
It is not likely that this will matter for performance. The view only serves the purpose to make the query easier to read and maintain.
If one of your "performanc e" metrics is ease of maintenance, then the
view definitely aids performance.
Thanks again!
-- Rick
P.S. Although I could understand the query from hell and I even managed
to modify it for my particular situation, anyone who had to maintain the
code after I leave would have been overwhelmed. (Most likely) That's
not a good solution in my book. I always try to write code that seems
very easy to me, in the hopes that it will seem very easy to my
replacement.
[RF] This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Diego TERCERO |
last post by:
Hi...
I'm working on a tool for editing text resources for a family of software
product my company produces.
These text resources are found in a SQL Server database, in a table called
"Resource" with the following structure :
Resource{,en,fr,es}
Yes.. these are the only languages supported actually.
A couple of rows in that table would look like this :
|
by: Ireneus Broncel |
last post by:
I have a class which reads Groups and Users from ActiveDirectory. The
Problem is, that i have about 10000 rows as product. When I am trying to
read the "memberOf" Objects out of this field i get allways different
count of rows.
If anybody knows something about this kind of problem, I would
appreciate any help.
Thx.
|
by: Jim Heavey |
last post by:
Trying to figure out the technique which should be used
to add rows to a datagrid. I am thinking that I would
want an "Add" button on the footer, but I am not quite
sure how to do that. Is that the best method? Do you
have a sample of how to do this?
|
by: Chris Smith |
last post by:
Experience Posters,
Sorry if this is not the right group to post this question.
He is my issue;
Is there a way without the use of 3rd party controls, to transpose the rows
of a datatable to columns of the datatable?
Any help would be great.
|
by: Martin Joergensen |
last post by:
Hi,
I have some files which has the following content:
0 0 0 0 0 0
0 1 1 1 1 0
0 1 1 1 1 0
0 1 1 1 1 0
0 1 1 1 1 0
0 0 0 0 0 0
| |
by: Lars E |
last post by:
Hi all
I have a small problem.
I have a datatable with 8 columns. But it is only data in 5 of the columns.
Data for the remaing 3 columns is in another dataset.
I Want to run trough the datatable and fill out the remaining data.
My code so far:
if (this.fetch("custinfo", "fetchCustInfo", out customers, parameters))
|
by: sysmanint1 |
last post by:
I am a total neophyte at Visual Basic but found the following post and reply from Clint concerning a dynamic range.
Also, have never "posted" to a discussion
I have made a macro that works on a template spreadsheet with a fixed number of columns to be "macro'd" bounded by data that changes in its number of rows.
That is, the bounding data is imported to the spreadsheet before the calculated columns are run. But the rows of this data...
|
by: lethek39 |
last post by:
Hey
I have been trying to figure out how to sum rows and columns in a matrix square. I also have been trying to get the program to list the numbers of the diagonal in the matrix. So far this is the code I have (I am using Python):
def generate (rows, cols): # This just prints the coordinates and the number that goes in it, It also prints the matrix in a square
import random
m = {}
for r in range(rows):
for c in...
|
by: Sieldan |
last post by:
I'm trying to throw some data around so that I can manipulate it more easily. Unfortunately, I don't really know what I'm doing. :) So I'm creeping my way through it step by step.
What I have done so far is find the rows of data I need to work with, copied them into a temporary table, and removed them from the original table. Now I am trying to send the manipulated rows back to the original table. The hitch is the original table has two...
|
by: tucson |
last post by:
I have a gridview that has a blank row with 2 input fields: file to upload, and a description of the file. They click Add and a new row is added, Remove and the row is removed.
The problem is:
When a new row is added, I loop through the existing gridview rows, store the data in a dataset, and rebind. In debug mode, I see the values I entered but when I rebind, it's not displayed in the gridview.
Here's the code aspx code:
...
|
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...
| |
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,...
|
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...
|
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,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |