Hi guys,
My challenge is really 3 problems in 1.
I have Table1 and Table2 which are inner joined with an ID.
Each record in Table1 may have up to two corresponding values in Table2, which are Type and Name.
req1- I want to select Table2.Name twice (or more), with each selection conditioned on a specific Type.
req2- I also want all information pertaining to the same ID to be returned in each row
req3- I want all IDs to be returned, even if Table2.Name are empty for Type1 and/or Type2.
Ideally, my results should look like this:
Table1.ID | Table2. Name (Type=1) | Table2.Name (Type=2)
----------------------------------------------------------
ID1 | NameA for Type1 | NameB for Type2
ID2 | NameC for Type1 | (empty)
ID3 | (empty) | NameD for Type2
ID4 | (empty) | (empty)
Sorry the abstractness, but this is the simplest way for me to express the problem.
Any help will be appreciated, thanks!
8 5128
Kindly post the query that you have tried to solve this problem.
Ok - the query is at the bottom, and just some clarifications
So, the source tables look something like this:
Table1.ID | ……
-----------------------
ID001
ID002
ID003
ID004
Table2.ID | Table2.Type | Table2.Name
--------------------------------------------------------
ID001 | Type1 | NameA
ID001 | Type2 | NameB
ID002 | Type1 | NameC
ID003 | Type2 | NameD
Ideally, my results should look like this:
Table1.ID | Table2. Name (Type=1) | Table2.Name (Type=2)
-------------------------------------------------------------------------------------
ID1 | NameA for Type1 | NameB for Type2
ID2 | NameC for Type1 | (empty)
ID3 | (empty) | NameD for Type2
ID4 | (empty) | | (empty)
Right now, I can only get Table2.Name to display once, and it won’t return the empty values, even with outer joins. The structure that I’m using is:
SELECT …
FROM …
WHERE Table2.Name IN
(SELECT Table2.Name
FROM Table1 INNER JOIN Table2 …
WHERE Table2.Name = ‘Type1’)
Thanks!
Ah sorry, I realised that I defined the problem slightly incorrectly and also oversimplified. Here is the corrected full problem:
The Name field should be in Table1, not Table2 and there should be 3 tables with 2 different keys that link them. ID01 links Table0 and 1, and ID12 links Table1 and 2. Let me explain with example:
Source tables Table0, Table1, Table2 look like this:
Table0.ID01
-----------
ID111
ID222
ID333
ID444
Table1.ID01 | Table1.ID12 | Table1.Name
----------------------------------------
ID111 | IDAAA | NameA
ID111 | IDBBB | NameB
ID222 | IDAAA | NameC
ID333 | IDBBB | NameD
Table2.ID12 | Table2.Type
--------------------------
IDAAA | Type1
IDBBB | Type2
Result should look like this:
T0.ID | T1.Name (T2.Type=Type1) | T1.Name (T2.Type=Type2)
------------------------------------------------------------
ID111 | NameA | NameB
ID222 | NameC | (empty)
ID333 | (empty) | NameD
ID444 | (empty) | (empty)
Also, Table1 is inner joined to Table0 through ID01, so Table0 may have an ID444, which needs to be returned also with (empty)s.
Thanks a lot!
Btw, I have tried this, but I get the error that 'Subquery returned more than 1 value. This is not permitted when subquery follows =,!=,>,< or when subquery is used as an expression' -
SELECT Table0.ID01,
-
(SELECT Table1.Name
-
FROM Table1 INNER JOIN Table2
-
ON Table1.ID12 = Table2.ID12
-
WHERE Table2.Type = Type1)
-
-
FROM Table0 INNER JOIN Table1
-
ON Table0.ID01 = Table1.ID01
-
Ah sorry, I realised that I defined the problem slightly incorrectly and also oversimplified. Here is the corrected full problem:
The Name field should be in Table1, not Table2 and there should be 3 tables with 2 different keys that link them. ID01 links Table0 and 1, and ID12 links Table1 and 2. Let me explain with example:
Source tables Table0, Table1, Table2 look like this:
Table0.ID01
-----------
ID111
ID222
ID333
ID444
Table1.ID01 | Table1.ID12 | Table1.Name
----------------------------------------
ID111 | IDAAA | NameA
ID111 | IDBBB | NameB
ID222 | IDAAA | NameC
ID333 | IDBBB | NameD
Table2.ID12 | Table2.Type
--------------------------
IDAAA | Type1
IDBBB | Type2
Result should look like this:
T0.ID | T1.Name (T2.Type=Type1) | T1.Name (T2.Type=Type2)
------------------------------------------------------------
ID111 | NameA | NameB
ID222 | NameC | (empty)
ID333 | (empty) | NameD
ID444 | (empty) | (empty)
Also, Table1 is inner joined to Table0 through ID01, so Table0 may have an ID444, which needs to be returned also with (empty)s.
Thanks a lot!
OK. Let's first do a fist step join Table2 and Table1: -
SELECT Table1.ID01, Table1.Name, Table2.Type
-
FROM Table1 INNER JOIN Table2 ON Table1.ID12=Table2.ID12
-
I'll call this table Table12 (I created a view of this for myself but it's not necessary only a shorthand)
Now join Table12 to Table0 twice (once for Type1 and once for Type2): -
SELECT Table0.ID01, TableType1.Name, TableType2.Name
-
FROM Table0 LEFT OUTER JOIN Table12 as TableType1 ON (Table0.ID01=TableType1.ID01 and TableType1.Type='Type1')
-
LEFT OUTER JOIN Table12 as TableType2 ON (Table0.ID01=TableType2.ID01 and TableType2.Type='Type2')
-
And there you fly :-)
Thanks for your help azimmer
With your solution, I get the results, but they look like this
T0.ID | T1.Name (T2.Type=Type1) | T1.Name (T2.Type=Type2)
------------------------------------------------------------
ID111 | NameA | (empty)
ID111 | (emptry) | NameB
ID222 | NameC | (empty)
ID333 | (empty) | NameD
ID444 | (empty) | (empty)
So the two records for ID111 appear on different lines...and I want them on one line like this
T0.ID | T1.Name (T2.Type=Type1) | T1.Name (T2.Type=Type2)
------------------------------------------------------------
ID111 | NameA | NameB
Reason being, I'm further using this for Reporting, and I actually have Type1,2,3,4, so it's best if the info can all appear in 1 line and not 4!
It already helps a lot, but if it can be further tweaked, then it would be perfect :)
OK. Let's first do a fist step join Table2 and Table1: -
SELECT Table1.ID01, Table1.Name, Table2.Type
-
FROM Table1 INNER JOIN Table2 ON Table1.ID12=Table2.ID12
-
I'll call this table Table12 (I created a view of this for myself but it's not necessary only a shorthand)
Now join Table12 to Table0 twice (once for Type1 and once for Type2): -
SELECT Table0.ID01, TableType1.Name, TableType2.Name
-
FROM Table0 LEFT OUTER JOIN Table12 as TableType1 ON (Table0.ID01=TableType1.ID01 and TableType1.Type='Type1')
-
LEFT OUTER JOIN Table12 as TableType2 ON (Table0.ID01=TableType2.ID01 and TableType2.Type='Type2')
-
And there you fly :-)
...and if that can't be done, can I maniuplate the reporting to display the way I want it? (with redundant rows removed and the data 'merged' into 1 row'
I'm using SQL Server2000 Reporting Services for the reporting.
Thanks!
Thanks for your help azimmer
With your solution, I get the results, but they look like this
T0.ID | T1.Name (T2.Type=Type1) | T1.Name (T2.Type=Type2)
------------------------------------------------------------
ID111 | NameA | (empty)
ID111 | (emptry) | NameB
ID222 | NameC | (empty)
ID333 | (empty) | NameD
ID444 | (empty) | (empty)
So the two records for ID111 appear on different lines...and I want them on one line like this
T0.ID | T1.Name (T2.Type=Type1) | T1.Name (T2.Type=Type2)
------------------------------------------------------------
ID111 | NameA | NameB
Reason being, I'm further using this for Reporting, and I actually have Type1,2,3,4, so it's best if the info can all appear in 1 line and not 4!
It already helps a lot, but if it can be further tweaked, then it would be perfect :)
I don't. I mean: I get what you said you want. Are you positive you did everything as I wrote?
Anyway, you can make these little changes (tweak?) -- I believe they're not needed, however, this way you make sure that there's one row per ID only: -
SELECT Table0.ID01, min(TableType1.Name), min(TableType2.Name)
-
FROM Table0 LEFT OUTER JOIN Table12 as TableType1 ON (Table0.ID01=TableType1.ID01 and TableType1.Type='Type1')
-
LEFT OUTER JOIN Table12 as TableType2 ON (Table0.ID01=TableType2.ID01 and TableType2.Type='Type2')
- GROUP BY Table0.ID01
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Guy Hocking |
last post by:
Hi there,
I have a problem in my ASP/SQL Server application i am developing, i hope
you guys can help.
I have a ASP form with list boxes populated by SQL tables. When a user
selects a value...
|
by: Rick |
last post by:
It seems I should be able to do 1 select and both return that
recordset and be able to set a variable from that recordset.
eg.
Declare @refid int
Select t.* from mytable as t --return the...
|
by: Kevin Lyons |
last post by:
Hello,
I am trying to get my select options (courses) passed correctly from
the following URL: http://www.dslextreme.com/users/kevinlyons/selectBoxes.html
I am having difficulty getting the...
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I...
|
by: romixnews |
last post by:
Hi,
I'm facing the problem of analyzing a memory allocation dynamic and
object creation dynamics of a very big C++ application with a goal of
optimizing its performance and eventually also...
|
by: rAinDeEr |
last post by:
Hi,
I have a web application with a table to store terms and
conditions of a Company.
This may some times run into many pages and some times it may be just a
few sentences. It is a character...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: Matik |
last post by:
Hello Everybody,
I have a problem, with select stmt:
SELECT TOP 15 *
FROM oaVIEW_MainData AS TOP_VIEW,
oaLanguageData_TAB AS RwQualifierJoin with (nolock)
WHERE (c_dateTime>='2007.01.10...
|
by: canugi |
last post by:
I need to store the contents of an SQL "in clause" in an MS Access 2000 table.
I use MS Access version 9.0.8960 (SP3)
This is my statement (and it works fine with the explicit "in clause"...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
| |