Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL one to many join help

Posted on 2011-10-14
17
Medium Priority
?
469 Views
Last Modified: 2012-05-12
Dear all,

I need help with a join issue. It is for an informix database but I imagine this solution is standard SQL:

I am trying to create an export of data that has a table with one id that connects with a table that may have no rows, one row, or twenty rows that match a particular id. I just want to take say the first 5 that it finds. I need this to be consistent since I am creating a flat file from it and if there are no results it will just be an empty string.

I am hard-coding cucref1, cucref2, cucref3, etc. But the data I want is from a field called cucref from a table called ref_ref and a single id can have 1-5.

Is there a way to use a join to get the first instance for a certain id and select that, then get the 2nd instance and select that, and the 3rd instance and get that, etc. If there was no data then it would just come back blank.

Here is the current select query:
select i.id, a.common_app_id, i.firstname, i.lastname, i.middlename, a.pref_name, p.birth_date, 'IN' as International, p.hispanic, p.race, p.citz, i.ctry,
p.sex, a.email, i.phone, a.doc_ctgry, a.plan_enr_sess, a.plan_enr_yr, a.fa, a.early_decsn, a.discipline, a.first_gen, a.cl, a.cnslr_init, e.prim_sch, e.ceeb, e.grad_date,
'School City' as city, 'CA' as state, 91711 as zip, a.legacy, 'IMPTCT' as vip, 'JJV' as staff_init, 1 as score, 'EM' as cucref1, 'CV' as cucref2, 'S12' as cucref3,
'' as cucref4, '' as cucref5, a.hm_merit, a.exam_doc,
'C' as stat1, a.cl, '' as resrc2
from id_rec i, adm_rec a, profile_rec p, ed_rec e where i.id = p.id and i.id = a.id and i.id = e.id and a.plan_enr_yr = '2011' and e.prim_sch = 1

0
Comment
Question by:dcrowley_01
  • 9
  • 4
  • 2
  • +1
17 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 36971710
it's hard answering your question without having the table structures, so i will use sample tables
lets say you have
main_table with a column id and rows 1,2,3
and you have another table called details with id and attribute that have the following rows
1, red
1, long
1, heavy
1, tall
2, small
2, blue
3, orange

you can run this query in order to get 5 attributes or blanks

select t1.id, coalesce(t2.attribute, '') attr1, coalesce(t3.attribute, '') attr2, coalesce(t4.attribute, '') attr3,
 coalesce(t5.attribute, '') attr4, coalesce(t6.attribute, '') attr5
from main_table t1
  left outer join details_table t2 on t1.id = t2.id
  left outer join details_table t3 on t1.id = t3.id and t3.id <> t2.id
  left outer join details_table t4 on t1.id = t4.id and t4.id <> t3.id and t4.id <> t2.id
  left outer join details_table t5 on t1.id = t5.id and t5.id <> t4.id and t5.id <> t3.id and t5.id <> t2.id
  left outer join details_table t6 on t1.id = t6.id and t6.id <> t5.id and t6.id <> t4.id and t6.id <> t3.id and t6.id <> t2.id


0
 
LVL 27

Expert Comment

by:tliotta
ID: 36971739
So, a conceptual possibility would be to have a view over ref_ref that had all cucref values presented in a single row for a given id. Your query could join to that view and grab whatever was in the first five columns. Or the view might concatenate all cucref values into a single column; and as long as the values all were, say, CHAR(10) values, your query could grab the first five 10-character substrings.

Am I understanding correctly?

The view might present a row for id=1 that looked like:

ID  CUCREFs
 1  EM        CV        S12       bbbbbbbbbbbbbbbbbbbb

Open in new window


(..the trailing 'b's represent two blank cucref values.)

If you had a function or recursive query that presented your cucref values appropriately, your query could use it, right?

Tom
0
 

Author Comment

by:dcrowley_01
ID: 36971779
the main table would only have one row for a particular id.

the table i want to join would have with multiple rows for a particular id.

tliotta, yes you have the right idea
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:dcrowley_01
ID: 36971881
Is there not a way to select the 1st result returned and stick it in one variable, the secend result in another, etc?
0
 

Author Comment

by:dcrowley_01
ID: 36971882
I need the values in different columns in the result. I just tried creating a view but I don't see how that is any different when writing a select statement to query it?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36971907
i don't understand what is wrong with my solution
it is exactly what you need
0
 

Author Comment

by:dcrowley_01
ID: 36971920
momi, there are hundreds of thousands of id's in the table. I can't put in an id <> for every single value.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36971924
you don't need to
you just need to copy my query as is
the <> just means that you won't get the same attribute more than once in the result
if you have some other way to identify the 5 values you want, this code can be remove
0
 

Author Comment

by:dcrowley_01
ID: 36971934
I guess I am not understanding how to fit your example into my database. I am attaching two excel sheets, one from adm_rec and one from ref_rec to try and give you the table structure.
ref-rec-3rows.csv
0
 

Author Comment

by:dcrowley_01
ID: 36971939
0
 

Author Comment

by:dcrowley_01
ID: 36971940
0
 
LVL 8

Expert Comment

by:Andrei Fomitchev
ID: 36971956
The query that provides the result on MS SQL Server:


SELECT t.Ref_Id, t.Ref_Name, tt.[1] AS [Eye Color],[2] AS [Zip], [3] AS [Hair Style], [4] AS [Education], [5] AS [Gender]
FROM ref t
JOIN (
      SELECT Ref_Id,[1],[2],[3],[4],[5]
      FROM (
            SELECT Ref_Id, Ref_Type_Id, Value FROM ref_ref
      ) AS SourceTable
      PIVOT
      (
      MAX(Value)
      FOR Ref_Type_Id IN ([1],[2],[3],[4],[5])
      ) AS PivotTable
) tt ON tt.Ref_Id = t.Ref_Id

The complete script is in the code.
Result:

Ref_Id      Ref_Name      Eye Color      Zip              Hair Style         Education      Gender
1              Name1              Blue              22222      Long         MS              Male
2              Name2              Hazel      33333      Short         BS                      Female

From 2 tables:

Ref:
Ref_Id      Ref_Name
1      Name1
2      Name2

Ref_Ref:
Id      Ref_Id      Ref_Type_Id      Value
1      1      1      Blue
2      1      2      22222
3      1      3      Long
4      1      4      MS
5      1      5      Male
6      2      1      Hazel
7      2      2      33333
8      2      3      Short
9      2      4      BS
10      2      5      Female

---- Tables and Test Data: ---------------------
CREATE TABLE ref (Ref_Id int NOT NULL PRIMARY KEY, Ref_Name NVarChar(50))
CREATE TABLE ref_ref(Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, Ref_Id int, Ref_Type_Id int, Value NVarChar(100))
GO
INSERT INTO ref(Ref_Id,Ref_Name) VALUES (1,'Name1'),(2,'Name2')
INSERT INTO ref_ref(Ref_Id,Ref_Type_Id,Value) VALUES
(1,1,'Blue'), -- Eyes
(1,2,'22222'), -- Zip
(1,3,'Long'), -- Hair
(1,4,'MS'), -- Degree
(1,5,'Male'), -- Gender
(2,1,'Hazel'), -- Eyes
(2,2,'33333'), -- Zip
(2,3,'Short'), -- Hair
(2,4,'BS'), -- Degree
(2,5,'Femail') -- Gender
GO


USE [PivotTest]
GO
CREATE TABLE ref (Ref_Id int NOT NULL PRIMARY KEY, Ref_Name NVarChar(50))
CREATE TABLE ref_ref(Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, Ref_Id int, Ref_Type_Id int, Value NVarChar(100))
GO
INSERT INTO ref(Ref_Id,Ref_Name) VALUES (1,'Name1'),(2,'Name2')
INSERT INTO ref_ref(Ref_Id,Ref_Type_Id,Value) VALUES
(1,1,'Blue'), -- Eyes
(1,2,'22222'), -- Zip
(1,3,'Long'), -- Hair
(1,4,'MS'), -- Degree
(1,5,'Male'), -- Gender
(2,1,'Hazel'), -- Eyes
(2,2,'33333'), -- Zip
(2,3,'Short'), -- Hair
(2,4,'BS'), -- Degree
(2,5,'Femail') -- Gender
GO
SELECT t.Ref_Id, t.Ref_Name, tt.[1] AS [Eye Color],[2] AS [Zip], [3] AS [Hair Style], [4] AS [Education], [5] AS [Gender]
FROM ref t
JOIN (
	SELECT Ref_Id,[1],[2],[3],[4],[5]
	FROM (
		SELECT Ref_Id, Ref_Type_Id, Value FROM ref_ref
	) AS SourceTable
	PIVOT
	(
	MAX(Value)
	FOR Ref_Type_Id IN ([1],[2],[3],[4],[5])
	) AS PivotTable
) tt ON tt.Ref_Id = t.Ref_Id

Open in new window

0
 
LVL 8

Expert Comment

by:Andrei Fomitchev
ID: 36971972
When you export to a file you should keep columns. Otherwise how somebody could possibly understand what is what.

My solution is good for CSV or TAB delimited files.

There is also a solution to generate XML from a query (if you interested).
0
 
LVL 27

Expert Comment

by:tliotta
ID: 36972128
I can't put in an id <> for every single value.

You don't put one in for every ID; you put one in for each cucref that you want and you said you wanted exactly five of those.

Momi showed how one table (main_table t1) can be joined to five other tables that are referenced as t2, t3, t4, t5 and t6. But notice that each of those last five references is to the same actual table -- details_table.

For your query, the five references would be to the ref_ref table. Each reference would supply one of the cucref values.

I haven't tried that, but it looks like it would work.

Tom
0
 

Author Comment

by:dcrowley_01
ID: 36983302
momi, I understand how your solution would work. But there is no coalesce function in informix. That appears to be MS SQL specific. Is there another way to select the value if it did exist or just populate the select with a blank?

Anyone else know how to do a coalesce type function in informix?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 36983377
try nvl instead of coalesce
0
 

Author Closing Comment

by:dcrowley_01
ID: 36987854
I still can't get it to work. But I will accept this as a solution and ask a simpler question because I can't even get a left outer join to work properly.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question