473,325 Members | 2,870 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Select a field twice with different (special) conditions

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!
Sep 12 '07 #1
8 5128
debasisdas
8,127 Expert 4TB
Kindly post the query that you have tried to solve this problem.
Sep 12 '07 #2
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!
Sep 12 '07 #3
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!
Sep 12 '07 #4
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'

Expand|Select|Wrap|Line Numbers
  1. SELECT Table0.ID01, 
  2.       (SELECT Table1.Name
  3.        FROM    Table1 INNER JOIN Table2
  4.                    ON Table1.ID12 = Table2.ID12
  5.        WHERE  Table2.Type = Type1)
  6.  
  7. FROM Table0 INNER JOIN Table1 
  8.          ON Table0.ID01 = Table1.ID01
  9.  
Sep 12 '07 #5
azimmer
200 Expert 100+
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ID01, Table1.Name, Table2.Type
  2. FROM Table1 INNER JOIN Table2 ON Table1.ID12=Table2.ID12
  3.  
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):
Expand|Select|Wrap|Line Numbers
  1. SELECT Table0.ID01, TableType1.Name, TableType2.Name
  2. FROM Table0 LEFT OUTER JOIN Table12 as TableType1 ON (Table0.ID01=TableType1.ID01 and TableType1.Type='Type1')
  3.             LEFT OUTER JOIN Table12 as TableType2 ON (Table0.ID01=TableType2.ID01 and TableType2.Type='Type2')
  4.  
And there you fly :-)
Sep 13 '07 #6
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ID01, Table1.Name, Table2.Type
  2. FROM Table1 INNER JOIN Table2 ON Table1.ID12=Table2.ID12
  3.  
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):
Expand|Select|Wrap|Line Numbers
  1. SELECT Table0.ID01, TableType1.Name, TableType2.Name
  2. FROM Table0 LEFT OUTER JOIN Table12 as TableType1 ON (Table0.ID01=TableType1.ID01 and TableType1.Type='Type1')
  3.             LEFT OUTER JOIN Table12 as TableType2 ON (Table0.ID01=TableType2.ID01 and TableType2.Type='Type2')
  4.  
And there you fly :-)
Sep 13 '07 #7
...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!
Sep 13 '07 #8
azimmer
200 Expert 100+
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT Table0.ID01, min(TableType1.Name), min(TableType2.Name)
  2. FROM Table0 LEFT OUTER JOIN Table12 as TableType1 ON (Table0.ID01=TableType1.ID01 and TableType1.Type='Type1')
  3.             LEFT OUTER JOIN Table12 as TableType2 ON (Table0.ID01=TableType2.ID01 and TableType2.Type='Type2')
  4. GROUP BY Table0.ID01
  5.  
Sep 13 '07 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

7
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...
3
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...
12
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...
3
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...
17
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...
5
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...
22
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="...
1
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...
1
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"...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
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...
0
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...
0
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...
0
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...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....

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.