getting information from 2 tables

[trisha0906]

hi, what i need to do is to get information from 2 tables and show it to the listview. for example, i want to get the surname, first name and middle name from the student_info table and the Payment_Status from the accounting_system. i don't want to show the information that is already on the accounting system that has 'Paid' on the Payment_Status. Hope you can help me

my friend gave me this code

strsql = "select Surname, First_Name, Middle_Name from accounting_system"
        MsgBox("account")
        strsql2 = "select * from student_info where Surname, First_Name, Middle_Name not in ('" & strsql & "')"
        MsgBox("student")

but the listview only showed the first data on the database

[Jx_Man]

Why you not merge it as one sql query?

strsql = "select * from student_info where Surname, First_Name, Middle_Name not in " _
		& "(select Surname, First_Name, Middle_Name from accounting_system)"
MsgBox("student")

[trisha0906]

Why you not merge it as one sql query?

strsql = "select * from student_info where Surname, First_Name, Middle_Name not in " _
		& "(select Surname, First_Name, Middle_Name from accounting_system)"
MsgBox("student")

hi i've tried doing this but the the information on the database did not appear on the listview at all

[Estella]

How about post your 'showing data' codes?
Maybe it's not about the query.

[trisha0906]

How about post your 'showing data' codes?
Maybe it's not about the query.

i am currently using this

Sub fillstudinfo()
                strsql = "select * from student_info"
        sqlcmd.CommandText = strsql
        sqlcmd.Connection = sqlconn
        sqlda.SelectCommand = sqlcmd
        sqldr = sqlcmd.ExecuteReader()
        ListView1.Items.Clear()

        While (sqldr.Read())
            With ListView1.Items.Add(sqldr("Surname"))
                .subitems.add(sqldr("First_Name"))
                .subitems.add(sqldr("Middle_Name"))
                '.subitems.add(sqldr("Payment_Type"))
                '.subitems.add(sqldr("Payment_Amount"))
                '.subitems.add(sqldr("Date_Paid"))
                .subitems.add(sqldr("Student_No"))
                .subitems.add(sqldr("Grade_yearlevel"))
            End With

        End While
        sqldr.Close()

    End Sub

and everything works just fine except for what i really want to happen, but once i insert the codes above or the one that was given by Jx_Man, nothing will happen or nothing will appear on the listview. hope you can help me

[adam_k]

i want to get the surname, first name and middle name from the student_info table and the Payment_Status from the accounting_system.

The above can be achieved with this query:

select Surname, First_Name, Middle_Name, Payment_Status from t_student_info a
inner join t_accounting_system b 
on a.Surname = b.Surname and a.First_Name = b.First_Name and a.Middle_Name = b.Middle_Name

I don't get what you mean by this:

i don't want to show the information that is already on the accounting system that has 'Paid' on the Payment_Status.

The query bellow will show you Surname, First_Name, Middle_Name for students that don't exist in accounting system.

strsql = "select Surname, First_Name, Middle_Name from accounting_system"
        MsgBox("account")
        strsql2 = "select * from student_info where Surname, First_Name, Middle_Name not in ('" & strsql & "')"
        MsgBox("student")

Finally if you want to only show the students with Payment_Status = 'Paid' then add in my first query where Payment_Status = 'Paid'

[trisha0906]

The above can be achieved with this query:

select Surname, First_Name, Middle_Name, Payment_Status from t_student_info a
inner join t_accounting_system b 
on a.Surname = b.Surname and a.First_Name = b.First_Name and a.Middle_Name = b.Middle_Name

I don't get what you mean by this:

The query bellow will show you Surname, First_Name, Middle_Name for students that don't exist in accounting system.

strsql = "select Surname, First_Name, Middle_Name from accounting_system"
        MsgBox("account")
        strsql2 = "select * from student_info where Surname, First_Name, Middle_Name not in ('" & strsql & "')"
        MsgBox("student")

Finally if you want to only show the students with Payment_Status = 'Paid' then add in my first query where Payment_Status = 'Paid'

hi, i tried doing this but the result is still the same, the database is only showing the first data that is already on the accounting system

[ChrisPadgham]

try posting your data model we are guess at the SQL without knowing how your data is structured

[adam_k]

or you could add a where to get yourself the record you are looking for.

PS: What do you mean only the first data?

[trisha0906]

this is the accounting_system table and what i need from here is the Payment_Type only
USE [WAIS.MDF]
GO

/****** Object: Table [dbo].[accounting_system] Script Date: 01/19/2012 09:30:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[accounting_system](
[Studentpayment_id] [int] IDENTITY(1,1) NOT NULL,
[Total_Payment_Amount] [int] NULL,
[Payment_Type] [varchar](35) NULL,
[Discount] [varchar](35) NULL,
[Payment_Period] [varchar](35) NULL,
[Payment_Amount] [int] NULL,
[Payment_Status] [varchar](35) NULL,
[Date_Paid] [varchar](50) NULL,
[Outstanding_Balance] [int] NULL,
[Surname] [varchar](35) NULL,
[First_Name] [varchar](35) NULL,
[Middle_Name] [varchar](35) NULL,
[Grade_YearLevel] [varchar](20) NULL,
[OptionType] [varchar](50) NULL,
CONSTRAINT [PK_accounting_system] PRIMARY KEY CLUSTERED
(
[Studentpayment_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

and this is the student_info table, and what i need from here Surname, First_Name and Middle_Name
USE [WAIS.MDF]
GO

/****** Object: Table [dbo].[student_info] Script Date: 01/19/2012 09:32:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[student_info](
[Student_No] [int] NOT NULL,
[Surname] [nvarchar](35) NULL,
[First_Name] [nvarchar](35) NULL,
[Middle_Name] [nvarchar](35) NULL,
[Home_Address] [nvarchar](50) NULL,
[Birth_Date] [date] NULL,
[Religion] [nvarchar](35) NULL,
[Tel_No] [nvarchar](35) NULL,
[Gender] [text] NULL,
[Name_incase_Emergency] [nvarchar](35) NULL,
[Address_emergency] [nvarchar](35) NULL,
[Tel_No_Emergency] [nvarchar](35) NULL,
[Grade_yearlevel] [text] NULL,
[Section] [text] NULL,
[Place_of_Birth] [nvarchar](35) NULL,
[General_ave] [int] NULL,
[Father_Name] [nvarchar](35) NULL,
[Occupation] [nvarchar](35) NULL,
[EmployedAt] [nvarchar](35) NULL,
[Address_employment] [nvarchar](35) NULL,
[Mother_Name] [nvarchar](35) NULL,
[Occupation_Mother] [nvarchar](35) NULL,
[EmployedAt_Mother] [nvarchar](35) NULL,
[AddEmp_Mother] [nvarchar](35) NULL,
[SchoolLastAttended] [nvarchar](35) NULL,
[Address_School] [nvarchar](35) NULL,
[School_Year] [int] NULL,
[Citizenship] [nvarchar](35) NULL,
[Father_EducAttain] [nvarchar](35) NULL,
[Mother_EducAttain] [nvarchar](35) NULL,
[Father_TelNum_Work] [nvarchar](35) NULL,
[Mother_TelNum_Work] [nvarchar](35) NULL,
[Requirements_NSO] [nvarchar](50) NULL,
[Requirements_GoodMoral] [nvarchar](50) NULL,
[Requirements_ReportCard] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

@adam_k, what i mean is the first data on the student_info table

[ChrisPadgham]

for a start you should be storing the student_no in the accounting_system table not the the student name.