.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Compare if every table entry exist at least once in other table

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server


I have 2 tables:

- Salesmen with Pk.SalesmenID
- Appointments with Fk.SalesmenID and busy(bit)

I want to query:

(Every salesmen has at least one appointment)
(Select non busy salesmen)
(select * from salesmen)

Should I compare distinct counts of both tables?

Purpose is to get salesmens even if there's no appointment made yet. I hope it's somehow logic what I'm trying to explain here :-)

View Complete Post

More Related Resource Links

filter n compare between two table in resultset


Hi .. I have two table 

Say Table1 n Table2


Item              ItemName

001                 Laptop

001                  PC

002                  PC

002                  Printer

003                  Pendrive

004                  Keyboard

004                  CPU


Item                  ItemName

001                   laptop

001                   PC

002                   Printer

004                   CPU

Here am comparing table1 with table2

The Itemname which is available in both table1 n table2 should be dropout ... Other Item should be displayed ...

I need the sql result set like this :

Item                 &n

InfoPath Repeating Table - Force new row entry

Hi,Wondering if anyone has had a similar situation.. I have an InfoPath form with a repeating table. When the user completes a row and saves the doc, I want this to be non-editable, so any subsequent changes have to be added as new row. Seems simple enough, but maybe it's just been a long day.. :) Any suggestions?Thanks,Laura

I need only the most recent entry from a table.

I have the following tables: Prospect Table ------------------------------------------------------------------------------------------------ | prospect_id | prospect_first | prospect_last | prospect_phone | prospect_email    | ------------------------------------------------------------------------------------------------ |          1       | John              | Doe              | 1234567890      | email@email.com | ------------------------------------------------------------------------------------------------ Corp Table --------------------------- | corp_id | corp_name | --------------------------- |    1       |   My Corp   | ---------------------------- Stores Table ---------------------------------------- | store_id | store_name | corp_id | ---------------------------------------- |    1        | Main Store   |    1      | ---------------------------------------- Unitsize Table ---------------------- | us_id | us_desc | --------------------- |   1     | &

Prevent duplicate entry into aspnet_profile table through CreateUserWizard?


Is it possible to setup the CreateUserWizard to check for a duplicate entry in the aspnet_profile table (for a custom field) before the new user is created?  For example, if I added a phone number field and wanted to make sure nobody else could create an account using that same phone number if a user already has an account with that phone number in the aspnet_Profile table's PropertyValuesString row? 

copy from table to other exist table



I tried to insert new values (new rows into table) to Table A according to specific Select query on table A

Table B holds many datarows. I just want to add more from Table A to it.

Both tables are with the same columns (and properties).


I tried this :

FROM     A
WHERE   (YEAR(t_date) = 2009)

 I got an error:

An explicit value for the identity column in table 'Tnuot_Archive' can only be specified when a column list is used and IDENTITY_INSERT is ON.

select top row for multiple entry for a date in table


My current query is

select  Date= createdon , Total=count(*) from ReportDetail where 
reportid = 9 and (CreatedOn BETWEEN '07/01/2010' AND '09/30/2010') 
group by  createdon
order by createdon desc

return data like

2010-09-21 09:36:46.493    112
2010-09-21 08:33:12.667    114
2010-09-21 07:45:20.830    176
2010-09-21 07:33:34.340    114
2010-09-20 07:27:43.753    125
2010-09-17 10:04:27.120    75
2010-09-16 11:50:05.777    52

What I am looking for

2010-09-21 09:36:46.493    112
2010-09-17 10:04:27.120    75
2010-09-16 11:50:05.777    52

Basically for 9/21, I want to get only one latest row. Please advice.

How can I check (true/false) if every single entry in table exists in other table?



I have 2 tables:

- Salesmen with Pk.SalesmenID
- Appointments with Fk.SalesmenID and busy(bit)

How can I check if every salesmen has at least one appointment

Am I on the right way with statement below, though I got syntax problems

(SELECT COUNT(distinct SalesmanID)FROM Appointments)
(SELECT COUNT(SalesmanID) FROM Salesmen)

Find and compare values from a repating table?


Hi all

I am trying to loop through a repeating table, which is a secondary data source, and compare the Title column from the repeating table to another string which is not from the table. If they equal, I want all other rows to be hidden, except the equal one. I could do it easily in regular c#, but have not done it with InfoPath. I am not quite sure how to acheive that in InfoPath. If somebody could give me some sample code to put me in the right direction, I would be most thankful.


any compare tool which will cater table rename script

Any compare tool which will cater table rename script.
what i have done in my application new version is rename about 6 table.
I am use to run the AdeptSQL Diff tool to get the upgrade script.
Now I am stuck with table renaming.I have also called that table names in number of stored procedure.
Is there any tool I can generate the upgrade script with table renaming?

Kamran Shahid Senior Software Engineer/Analyst (MCP,MCAD,MCSD.NET,MCTS,MCPD.net[web])

delete from one table if a combination of data doesn't exist in another


this will work, but i am 100% certain there's a more efficient way:

create table stgClients(intClientId int, status1 varchar(50), status2 varchar(50), status3 varchar(50))

create table lkpClientStatus(intStatusId int, vchStatusCode varchar(50), bitDeleted bit)

create table trefClientsStatus(intClientId, intStatusId)

DELETE FROM trefClientsStatus WHERE cast(intClientId as varchar(10)) + '|' + CAST(intStatusId as varchar(10)) NOT IN
SELECT cast(a.intClientId as varchar(10)) + '|' + CAST(a.intStatusId as varchar(10)) FROM
(SELECT DISTINCT sc1.intClientId, cs1.intStatusId
FROM stgClients sc1 INNER JOIN lkpClientStatuses cs1 ON (LTRIM(RTRIM(sc1.status1)) = cs1.vchStatusCode AND cs1.bitDeleted = 0)
WHERE NULLIF(LTRIM(RTRIM(sc1.status1)), '') is not null
SELECT DISTINCT sc2.intClientId, cs2.intStatusId
FROM stgClients sc2 INNER JOIN lkpClientStatuses cs2 ON (LTRIM(RTRIM(sc2.status2)) = cs2.vchStatusCode AND cs2.bitDeleted = 0)
WHERE NULLIF(LTRIM(RTRIM(sc2.status2)), '') is not null
SELECT DISTINCT sc3.intClientId, cs3.intStatusId
FROM stgClients sc3 INNER JOIN lkpClientStatuses cs3 ON (LTRIM(RTRIM(sc3.status3)) = cs3.vchStatusCode AND cs3.bitDeleted = 0)
WHERE NULLIF(LTRIM(RTRIM(sc3.status3)), '') is not null) a

yes the trims and nullifs are

Updating a table - or add when the record doesn't exist



I'd like to be able to update a table from a VB application without the table being part of the project.

I know how to insert a record:


Dim connectionstring As String = My.Settings.AdminCentreConnectionString

Dim eSQL As String =

Checking if item already exist in sql table using BCS



I am new to using BCS. I have a student table in my sql db, which has columns as Birthdate, student name, gender, student id,Maths Mark.

I am having a external list which has all the columns as in the sql table.

While inserting the list item using the external list, I would like to check if the user entered student details, for example: if Birthdate,student name, gender, student id already exists, and if exist prompt the user that data already exist. if the 4 columns combined exist already, i need to prompt the user.

How to achieve this.

SQL ERROR : showing maximum records 9 even if more than 10 records exist in table!!


hiii guys,,

i dont know how to deal with this error. i have 4 tables. each with equal and same columns. all tables have more than 10 records. when i fire a query to find out maximum records , it shows only 9. datatype used is varchar. and since this is very crucial as it is used as primary key and its a student id which gets incremented every time a new student adds to the 4 tables(in vb.net). my whole project is stuck cos of this. if i get this i have no other work to complete for my project.it will be over.


front end is vb.net

back end is sql.


i want that everytime when user inserts an entry and press a button,he should get student id + 1 from the existing.but it is only showing me 9records and always there's a ambiguity of 10th record in database..


guys pls help out of this!!!!

After Update trigger for a record which does not exist in table.


Hi Everyone,

I have a update query which sometimes results in no row updated (i.e. 0 rows updated),

I want to apply after update trigger on it , if update results in 0 row affected then insert that record,

I have written following trigger for it,

ALTER TRIGGER [dbo].[table1]

   ON  [dbo].[table1]





            SET NOCOUNT ON;

Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Insert value using Table Value Functions

a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Using jQuery to Filter Table Rows

The project is using the .net GridView control, so I had limited control over the output HTML code. Still, I think this code can work for most tables. One thing to notice: you should use the class "filterable" on your table or on one of its parents for the code to work.
First, we need a text box:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend