SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 duplicate data in a view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ElianeAsmar
Starting Member

Lebanon
2 Posts

Posted - 02/23/2014 :  07:37:08  Show Profile  Reply with Quote
Hello. I have 3 tables:
members(serial, name,address,phone)
membercars(memberid,color,make,model,)
memberpayments(memberid,from date,expirydate,amout,tax)
the memberid in membercars and memberpayments, is the serial in members.
i created a view: membersvw that contains the fields of all these three tables.
But there is a problem. If one member has more than one payment, i will find the member more several times in the view.

How can i solve this problem?

ElianeAsmar
Starting Member

Lebanon
2 Posts

Posted - 02/24/2014 :  05:32:29  Show Profile  Reply with Quote
Solved :).
I just added an order by and group by to the queries.


Private Sub btnFilter_Click(sender As System.Object, e As System.EventArgs) Handles btnFilter.Click

        Dim strsql As String = ""
        If mcon.State = ConnectionState.Closed Then mcon.Open()
        If cmbFilter.Text = "Name" Then
            strsql = "select serial,name, familyname from membersvw where name like '%" & txtFilter.Text & "%' group by serial,name,familyName order by name "
        ElseIf cmbFilter.Text = "Family Name" Then
            strsql = "select serial,name, familyname from membersvw where familyname like '%" & txtFilter.Text & "%' group by serial,name,familyName order by name "
        ElseIf cmbFilter.Text = "Tag" Then
            strsql = "select serial,name, familyname,Tag from membersvw where tag like '%" & txtFilter.Text & "%' group by serial,name,familyName,tag,memberCar order by tag "
        ElseIf cmbFilter.Text = "Make" Then
            strsql = "select serial,name, familyname,Make from membersvw where make like '%" & txtFilter.Text & "%' group by serial,name,familyName,make,memberCar order by make "
        ElseIf cmbFilter.Text = "Model" Then
            strsql = "select serial,name, familyname,Model from membersvw where model like '%" & txtFilter.Text & "%' group by serial,name,familyName,model,memberCar order by model "
        ElseIf cmbFilter.Text = "Color" Then
            strsql = "select serial,name, familyname,Color from membersvw where color like '%" & txtFilter.Text & "%' group by serial,name,familyName,color,memberCar order by color "
        ElseIf cmbFilter.Text = "Plate" Then
            strsql = "select serial,name, familyname,Plate from membersvw where plate like '%" & txtFilter.Text & "%' group by serial,name,familyName,plate,memberCar order by plate "
        Else
            btnNoFilter.PerformClick()
            Exit Sub
        End If
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000