Problem with master-detail query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Problem with master-detail query

Author

Topic

function
Starting Member

12 Posts

Posted - 2009-11-05 : 09:40:39

Hi guys, i am new in here.
Let me tell you a little about what i want to do with my project.
I am trying to fill a datagrid by databinding it with a dataset.
The dataset needs to be filled with the results from an sql query, combining 2 tables.

The first table (master) contains the following fields:
id, pat_id, description

The second table (detail) contains the following fields:
firstTable_id, firstTable_id, pat_id, description

When i am making an INSERT into the first table, SOMETIMES AND NOT ALL THE TIMES i am making an INSERT into the second table by fetching the latest firstTable_id that was inserted.
So, resuming, the second table does not contain all the firstTable_ids but some of them.

All i want to do is, when the datagrid fills with data, the last column to write 'CONTAINS' if the firstTable_id is contained in the second table, and 'NOT CONTAINS' if it does not contain it.

Explaining:
Lets assume that the firstTable contains the following rows:
1 | 22 | 'sick'
2 | 22 | 'sicker'
3 | 22 | 'sickest'
Lets now assume that the second table contains the following row:
1 | 2 | 22 | 'drug-1'

I want the datagrid to show something like this:
1 | 22 | 'sick' | NOT CONTAINS
2 | 22 | 'sicker' | CONTAINS
3 | 22 | 'sickest' | NOT CONTAINS

function
Starting Member

12 Posts

Posted - 2009-11-05 : 09:49:14

Just correcting somethings.... (this is the write question)
===========================================================

Hi guys, i am new in here.
Let me tell you a little about what i want to do with my project.
I am trying to fill a datagrid by databinding it with a dataset.
The dataset needs to be filled with the results from an sql query, combining 2 tables.

The first table (master) contains the following fields:
firstTable_id, pat_id, description

The second table (detail) contains the following fields:
id, firstTable_id, pat_id, description

When i am making an INSERT into the first table, SOMETIMES AND NOT ALL THE TIMES i am making an INSERT into the second table by fetching the latest firstTable_id that was inserted.
So, resuming, the second table does not contain all the firstTable_ids but some of them.

All i want to do is, when the datagrid fills with data, the last column to write 'CONTAINS' if the firstTable_id is contained in the second table, and 'NOT CONTAINS' if it does not contain it.

Explaining:
Lets assume that the firstTable contains the following rows:
1 | 22 | 'sick'
2 | 22 | 'sicker'
3 | 22 | 'sickest'
Lets now assume that the second table contains the following row:
1 | 2 | 22 | 'drug-1'

I want the datagrid to show something like this:
1 | 22 | 'sick' | NOT CONTAINS
2 | 22 | 'sicker' | CONTAINS
3 | 22 | 'sickest' | NOT CONTAINS

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-05 : 10:03:08

select
t1.id,
t1.pat_id,
t1.description,
case
when t2.firstTable_id is null then 'NOT CONTAINS'
ELSE 'CONTAINS'
end as [Contains or not]
from first_table t1
left join second_table t2
on t1.id=t2.firstTable_id

No, you're never too old to Yak'n'Roll if you're too young to die.

function
Starting Member

12 Posts

Posted - 2009-11-06 : 02:15:34

Thank you so so so so so so so so much!
It works like a charm. If i have any problems, i'll be back and ask for your help.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-06 : 02:36:14

You are welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

function
Starting Member

12 Posts

Posted - 2009-11-11 : 08:32:28

Lets say now that i have 3 tables (2 masters and 1 detail, that's connecting the 2 masters between them).
The first MASTER table has:

firstTable_id | firstTable_perigrafi
1 | ok
2 | ok2
========================================
The second MASTER table has:
secondTable_id | secondTable_perigrafi
1 | clue-1
2 | clue-2
3 | clue-3
4 | clue-4
5 | clue-5
========================================

The table that's connecting the above tables is

thirdTable_id | thirdTable_firstTableID | thirdTable_secondTableID
1 | 1 | 1
2 | 1 | 3
3 | 2 | 2
4 | 2 | 4
5 | 2 | 5
========================================

All i want to get as result is

firstTable_id | memberlist (concatenate the secondTable_ids)
1 | 1-3
2 | 2-4-5

Any ideas??

Thanks guys

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-11 : 11:09:33

Maybe here is a guru who can show a better way to do this...
But I am only able to solve this by using a function:

-- create a function to do the concatenation
create function dbo.concat_secondTableID(@keyval int)
returns  varchar(max)
as
begin
declare @ttt varchar(max)
set @ttt=null
select
@ttt=coalesce(@ttt+'-','')+convert(varchar(max),thirdTable_secondTableID)
from thirdTable
where thirdTable_firstTableID = @keyval
return @ttt
end

-- use the function in select
select 
thirdTable_firstTableID as firstTableID,
dbo.concat_secondTableID(convert(varchar(255),thirdTable_firstTableID )) as memberlist
from thirdTable

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources