Database

30-May-09 10:30

declare @BatchHeader table
(bhid int, status int)
insert into @BatchHeader values (1, 10)
insert into @BatchHeader values (2, 10)
insert into @BatchHeader values (3, 10)
insert into @BatchHeader values (4, 10)
insert into @BatchHeader values (5, 15)
insert into @BatchHeader values (6, 15)
insert into @BatchHeader values (7, 15)

select * from @BatchHeader

declare @BatchHeaderReference table
(parentid int, child int)

insert into @BatchHeaderReference values (1,2)
insert into @BatchHeaderReference values (1,3)
insert into @BatchHeaderReference values (1,4)
insert into @BatchHeaderReference values (1,5)
insert into @BatchHeaderReference values (1,6)
insert into @BatchHeaderReference values (1,7)

select * from @BatchHeaderReference

@BatchHeaderReference.ParentId and Child are the FKs to @BatchHeader.bhid

The output is this:
@BatchHeader
bhid Status
1 10
2 10
3 10
4 10
5 15
6 15
7 15

@BatchHeaderReference
Parentid Child
1 2
1 3
1 4
1 5
1 6
1 7

I want to write a query to fetch unique @BatchHeader rows only if the parent and child have status = 10. So in this case the query shoudnt return any @BatchHeader rows (bcos bhid 5,6,7 have status 15). But if I update the status for bhid=5,6,7 to 10 then the query should return bhid=1.

I tried something like this but it returns bhid=1
Select Distinct bhr.ParentId From @BatchHeaderReference bhr
Join @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid
Join @BatchHeader bh2 with (readpast) on bhr.ParentId = bh2.bhid
Where
bh1.Status = 10 -- Child batch status
And bh2.Status = 10 -- Header batch status

Please help.

Thanks,
Joe

Mycroft Holmes30-May-09 23:06

30-May-09 23:06

I had to go look at this properly IE in TSQL. @BatchHeaderReference joins ALL records to the header record 1, therefore header records 2-7 do not take part in the statement. no link to the @BatchHeaderReference table!

Joe_P wrote:
oin @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid

What database are you using, I have never seen readpast

Never underestimate the power of human stupidity
RAH

Joe_P31-May-09 4:11

31-May-09 4:11

I am using SQL Server as the database. I have copied and pasted the query as-is but for simplicity, I changed the tables to table variables, so you see the (readpast) in the query that fetches rows from a table variable.

I have to achieve this in a single SQL, so cant use any complex logic of additional table variables.

Thanks,
Joe

Mycroft Holmes31-May-09 11:10

31-May-09 11:10

You missed the point, the problem is in your join. All reference records join to hdr record 1 and are therefore valid.

parentid int (1) = bhid int (1) therefore status will always be 10

@BatchHeaderReference
Parentid Child
1	2
1	3
1	4
1	5
1	6
1	7

Is correct, status 15 never comes into it and the 2-7 are NOT FROM THE HDR table. I think your data structure is screwed, if your status was on the reference table then the filtering would work

Never underestimate the power of human stupidity
RAH

Joe_P31-May-09 11:23

31-May-09 11:23

I understand, but I cant have a status on the BatchHeaderReference table. This table is just to figureout the parent/child relationship and the status of these should be retained on the BatchHeader table only.

So only if all the header and child rows from HDR table are in status 10 then the query should return the id.

Thanks,
Joe

Mycroft Holmes31-May-09 11:36

31-May-09 11:36

Joe_P wrote:
Join @BatchHeader bh1 with (readpast) on bhr.Child = bh1.bhid
Join @BatchHeader bh2 with (readpast) on bhr.ParentId = bh2.bhid
Where
bh1.Status = 10 -- Child batch status
And bh2.Status = 10 -- Header batch status

One begins to see the light....
Change the joins to INNER JOIN and add the filter (=10) on BH2 join rather than the where clause

Never underestimate the power of human stupidity
RAH

Joe_P31-May-09 14:08

31-May-09 14:08

Thanks for the reply Mycroft Holmes, but no luck.

Tried this
Select Distinct bhr.ParentId From @BatchHeaderReference bhr
Inner Join @BatchHeader bh1 on bhr.ChildId = bh1.id and bh1.Status = 10
Inner Join @BatchHeader bh2 on bhr.ParentId = bh2.id and bh2.Status = 10

And
Select Distinct bhr.ParentId From @BatchHeaderReference bhr
Inner Join @BatchHeader bh1 on bhr.ChildId = bh1.id
Inner Join @BatchHeader bh2 on bhr.ParentId = bh2.id and bh2.Status = 10
Where
bh1.Status = 10

Mycroft Holmes31-May-09 17:24

31-May-09 17:24

Try this

Select * 
From @BatchHeaderReference bhr
	INNER Join @BatchHeader bh1 on bhr.Child = bh1.bhid
WHERE bhr.child IN (SELECT bhid FROM @BatchHeader WHERE status = 10)

Never underestimate the power of human stupidity
RAH

smcnulty20004-Jun-09 1:24

smcnulty2000

4-Jun-09 1:24

The way I read this problem all of the children must have the status of 10. If the parent has any children without the status ten then it doesn't come back.

This makes it a bear because you have to do something to match all children with all children who have a status of 10 and only return the parent if the children all come back as status 10.

So I'd use a counting mechanism.

select distinct x.parentid 
from (
select 
bhr.* , 
(select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.child) status, 
(select count(*) from @BatchHeaderReference bhr2 where bhr2.parentid=bhr.parentid) cnt, 
(select count(*) from @BatchHeaderReference bhr3 inner join @BatchHeader bh3 on bh3.bhid=bhr3.child where bh3.status=10 and bhr3.parentid=bhr.parentid) truecount 
from @BatchHeaderReference bhr
) x
 where x.cnt=x.truecount

I'm more of an oracle guy but the above will work.

Going from the inside out, which is usually the best way to deal with these problems, you create a count of all children. Then a count of all 'true' children- those with a status of 10. Then you put all of that inside of a wrapper which we call X.
From there we treat X as another table.

If you need to also check to see if the parent has a status of 10 then you just change it to this.

select distinct x.parentid  
from (
select 
bhr.* , 
(select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.child) status, 
(select count(*) from @BatchHeaderReference bhr2 where bhr2.parentid=bhr.parentid) cnt, 
(select count(*) from @BatchHeaderReference bhr3 inner join @BatchHeader bh3 on bh3.bhid=bhr3.child where bh3.status=10 and bhr3.parentid=bhr.parentid) truecount, 
 (select max(bh.status) from @BatchHeader bh where bh.bhid=bhr.parentid) pstatus
from @BatchHeaderReference bhr  
  
) x
 where x.cnt=x.truecount 
 and x.pstatus=10

Here you get the parent status, calling it pstatus, and then just check it at the last stage.

edit: the reason MAX is used is to stop certain errors from creeping in if you have the same value show up but multiple times. If you expect different values for status against a given id then you'd have to take another approach because this would be incorrect.

_____________________________
Those who study history are doomed to watch others repeat it. -Scott M.

Re: get the inserted GUID

Niladri_Biswas2-Jul-09 0:25

Niladri_Biswas

2-Jul-09 0:25

If I have understood your problem correctly, then try the following query

SELECT BHID 'PARENTID' FROM @BatchHeader BHR1

WHERE BHR1.STATUS = 10

UNION


SELECT PARENTID  FROM @BatchHeaderReference BHR

INNER JOIN @BatchHeader BH 

ON BH.BHID = BHR.CHILD 

AND BH.STATUS = 10

As per the current scenario, the output will be

PARENTID
--------
1
2
3
4

If you update 5 and 7 of @BatchHeader to 10, the output will be

PARENTID
--------
1
2
3
4
5
7

Hope this helps
Smile | :)

Niladri Biswas

get the inserted GUID

Mohammad Al Hoss30-May-09 2:18

Mohammad Al Hoss

30-May-09 2:18

Hi I used to use the function SCOPE_IDENTITY() to get the inserted identity, now I need to get the inserted GUID any idea how to do so
Thanks

Mohammad Al Hoss

Development To Me Is A Pleasure more than a Job

Mycroft Holmes30-May-09 23:15

how do i use a primary key auto-generated in a table as a foreign key in another table?

30-May-09 23:15

The minimum of research turns up the first in this list clickety[^] which leads me to believe that GUIDs are a really bad idea.

I have used unique identifiers where databases needed to be merged but there was always an identity field for local processing.

Never underestimate the power of human stupidity
RAH

ChiSmile29-May-09 21:10

Re: how do i use a primary key auto-generated in a table as a foreign key in another table?

29-May-09 21:10

Hi all, am not too experienced with microsoft sql database. I want to use a foreign key e.g mod_data_id, auto generated in a table Data_modification, as a foreign key in another table called modification_copy, such that each time data is inserted in Data_modification table, the mod_data_id will be used to update the modification_copy Tanle.
Plz! help. The code is given below:

Data_Modification (mod_data_id,Patient_ID, Date_Collected, Wrong_Data_Entry_Date, Data_Field, Provider)

Modification_Copy (Data_Mod_ID,mod_data_id, Date_Collected, Data_Attribute, Correct_Data_Value)

Thanks in advance for ur help.

Mycroft Holmes29-May-09 23:40

Re: how do i use a primary key auto-generated in a table as a foreign key in another table?

29-May-09 23:40

A couple of things:

What you are describing sounds like a trigger, triggers are EVIL as they make support difficult. I would suggest using a stored procedure to do the updating in both tables wrapped in a transaction @@IDENTITY (SCOPE_IDENTITY() is required if you use triggers) will give you the last entered identity field

nedusmile wrote:
Data_Modification (mod_data_id,Patient_ID, Date_Collected, Wrong_Data_Entry_Date, Data_Field, Provider)

This is going to drive you (or the person who supports your DB) nuts. Get rid of the unserscores they are sooo 80s.

Data_Modification (ModDataID,PatientID, DateCollected, WrongDataEntryDate, DataField, Provider)

is much more readable IMHO.

Foreign keys define relationships and constraints, they will not cause data to be changed.

I recommend getting a beginners book on database design.

Never underestimate the power of human stupidity
RAH

ChiSmile30-May-09 0:29

Re: how do i use a primary key auto-generated in a table as a foreign key in another table?

30-May-09 0:29

Thanks alot mycroft holmes!!. but the following is my idea, i dont know y the ExecuteScalar method is returning nothing i guess.keeps throwing exception about type conversion.Can anyone help me!

Try

    LoadData()

    ' Configure and execute the command.
    comd = New System.Data.SqlClient.SqlCommand("insert into Data_Modification (Patient_ID, Date_Collected, Wrong_Data_Entry_Date, Data_Field, Provider)" & _
        " values (@PatRep_ID, @DateCol, @WrngDataDate, @Data_Field, @Provider)", con)

    'comd.Connection = con
    Dim paraPatId As SqlParameter = comd.Parameters.Add("@PatRep_ID", repDatPatID.Text)
    Dim paraDte As SqlParameter = comd.Parameters.Add("@DateCol", repDatColTmePikar2.Value.ToShortDateString)
    Dim paraWrngD As SqlParameter = comd.Parameters.Add("@WrngDataDate", repWDatEDaTmePika.Value.ToShortDateString)
    Dim paraDaFld As SqlParameter = comd.Parameters.Add("@Data_Field", repDatDFldCmBx.Text)
    Dim paraProv As SqlParameter = comd.Parameters.Add("@Provider", repDatProvTbx.Text)
    comd.ExecuteNonQuery()

Catch ex As SqlException
    MsgBox(ex.Message.ToString())
Finally
    con.Close()
End Try


Try

    LoadData()
    comd = New System.Data.SqlClient.SqlCommand("select distinct scope_identity()as Integer from Data_Modification", con)
    Dim key As Integer = comd.ExecuteScalar
    con.Close()


    LoadData()

    ' Configure and execute the command.
    comd = New System.Data.SqlClient.SqlCommand("insert into Modification_copy (data_mod_ID,Date_Collected, Data_Attribute,Correct_data_Value)" & _
        " values (@DataModIdD,@retDateCol, @DatAtt, @CorDaVal)", con)

    'comd.Connection = con
    Dim paraModID As SqlParameter = comd.Parameters.Add("@DataModIdD", key)
    Dim paraDteCol As SqlParameter = comd.Parameters.Add("@retDateCol", repDatColTmePikar2.Value.ToShortDateString)
    Dim paraDAtt As SqlParameter = comd.Parameters.Add("@datAtt", repMorCmBx.Text)
    Dim paraCorDVa As SqlParameter = comd.Parameters.Add("@corDaVal", repMorTxb.Text)


      comd.ExecuteNonQuery()
    If MsgBox("Report successful!. Do you want to report more Data?", MsgBoxStyle.YesNo, "Confirmation!!!") _
    = Windows.Forms.DialogResult.Yes Then
        repMorDetPnl.Hide()
        repDatTabPg.Show()

    Else
        repMorDetPnl.Hide()
        vwPatient.Show()
    End If

Catch ex As SqlException
    MsgBox(ex.Message.ToString())
Finally
    con.Close()
End Try

Mycroft Holmes30-May-09 0:48

Re: how do i use a primary key auto-generated in a table as a foreign key in another table?

30-May-09 0:48

Seems the usual reply to this question is to use a stored procedure (I ALWAYS use a proc so I had no idea how to do it using parameterised queries) however I found this response [^] which may help you.

I would still recommend a stored proc but that is a personal/professional opinion.

Never underestimate the power of human stupidity
RAH

ChiSmile1-Jun-09 2:09

Re: how do i use a primary key auto-generated in a table as a foreign key in another table?

1-Jun-09 2:09

Thanks alot! mycroft, i resolved the issue.I didn't have to use stored procedure since to me there wasn't any performance issue.So i included the SCOPE_IDENTITY() method in my query command and used ExecuteScalar which only returns the last key generated. I then assigned it to a variable.this variable was then passed on as the foreign key to another table.
Anyway thanks for ur help!!!

Mycroft Holmes1-Jun-09 3:33

Re: how do i use a primary key auto-generated in a table as a foreign key in another table?

1-Jun-09 3:33

Post the script, there is not a lot of examples of that style - most use procs.

Never underestimate the power of human stupidity
RAH

ChiSmile1-Jun-09 16:49

Re: how do i use a primary key auto-generated in a table as a foreign key in another table?

1-Jun-09 16:49

Here is the code, Plz dont mind my variable names as u said b4.But anyway, this works perfectly for me.

  Try

            LoadData()  ' function containing connection string

            ' Configure and execute the command.
'the scope_identity() is inserted in the query that will insert the data
'this will retrieve the last auto-generated number from the first table.
            comd = New System.Data.SqlClient.SqlCommand("insert into Data_Modification (Patient_ID, Date_Collected, Wrong_Data_Entry_Date, Data_Field, Provider)" & _
                " values (@PatRep_ID, @DateCol, @WrngDataDate, @Data_Field, @Provider) select scope_identity()", con)

    'Declare and Initialize parameter that is passed to the query strings
            Dim paraPatId As SqlParameter = comd.Parameters.Add("@PatRep_ID", repDatPatID.Text)
            Dim paraDte As SqlParameter = comd.Parameters.Add("@DateCol", repDatColTmePikar2.Value.ToShortDateString)
            Dim paraWrngD As SqlParameter = comd.Parameters.Add("@WrngDataDate", repWDatEDaTmePika.Value.ToShortDateString)
            Dim paraDaFld As SqlParameter = comd.Parameters.Add("@Data_Field", repDatDFldCmBx.Text)
            Dim paraProv As SqlParameter = comd.Parameters.Add("@Provider", repDatProvTbx.Text)
            

 'command object is used to call the ExecuteScalar() and returns may be a 
'decimal value which is converted to integer and assigned to a variable.

            LoadData()
            
            Dim key As Integer = Convert.ToInt32(comd.ExecuteScalar())
            con.Close()


            LoadData()

            ' this is an insertion to the second table, the integer value (key) 
'Obtained above is passed on to the second query as foreign key (data_mod_ID) to the second table

            comd = New System.Data.SqlClient.SqlCommand("insert into Modification_copy (data_mod_ID,Date_Collected, Data_Attribute,Correct_data_Value)" & _
                " values (@DataModIdD,@retDateCol, @DatAtt, @CorDaVal)", con)

            'comd.Connection = con
            Dim paraModID As SqlParameter = comd.Parameters.Add("@DataModIdD", key.ToString)
            Dim paraDteCol As SqlParameter = comd.Parameters.Add("@retDateCol", repDatColTmePikar2.Value.ToShortDateString)
            Dim paraDAtt As SqlParameter = comd.Parameters.Add("@datAtt", repMorCmBx.Text)
            Dim paraCorDVa As SqlParameter = comd.Parameters.Add("@corDaVal", repMorTxb.Text)

            ' Load the DataTable.
            'datadapt = New System.Data.SqlClient.SqlDataAdapter(comd)
            'datadapt.Fill(dSet)
            'dSet.Tables.Add(dTab)

            ' Dim rowAffected As Integer = 0

            ' cmd.Connection = cn

            ' rowAffected = 
            comd.ExecuteNonQuery()
            If MsgBox("Report successful!. Do you want to report more Data?", MsgBoxStyle.YesNo, "Confirmation!!!") _
            = Windows.Forms.DialogResult.Yes Then
                repMorDetPnl.Hide()
                repDatTabPg.Show()

            Else
                repMorDetPnl.Hide()
                vwPatient.Show()
            End If

        Catch ex As SqlException
            MsgBox(ex.Message.ToString())
        Finally
            con.Close()
        End Try 




Public Sub LoadData()
        dSet = New DataSet
        dTab = New DataTable
        con = New System.Data.SqlClient.SqlConnection("Data Source=192.168.3.101,1433;Initial Catalog=DataBaseName;User ID=Doctor;Password=gudone;")
        con.Open()
    End Sub

Mycroft Holmes1-Jun-09 17:17