SQL Select Records NOT In Both Tables

Aug 7, 2005

Hi

I'm using Access 2002. I have 2 tables tblGroupContact,
tblGroupPermission, both have 2 fields identical structure:

ContactID GroupID (Both are Composite keys and both hold integers)

tblGroupContact holds everybody and the groups they are members of.

tblGroupPermission holds only those people who have permission to make
changes to another part of the DB.

The SQL at the end of post works, but opens a dialogue box looking for
a parameter value 'query1.ContactID'

Clicking enter or cancel (without entering anything) works OK.

What have I done wrong to cause this parameter request.

Thanks ColinK

SELECT tblGroupContact.ContactID, tblGroupContact.GroupID
FROM tblGroupContact LEFT JOIN tblGroupPermission ON
(tblGroupContact.ContactID = tblGroupPermission.ContactID) AND
(tblGroupContact.GroupID = tblGroupPermission.GroupID)
WHERE (((tblGroupPermission.ContactI*D) Is Null) AND
((tblGroupPermission.GroupID) Is Null));

View 3 Replies


ADVERTISEMENT

How To Select Distinct Records From 3 Or More Tables...?

Jun 1, 2001

Attached is my select query, but it is returning the database values twice.

Can anyone tell me how I tell it to stop! A sub query I guess....

SELECT DISTINCT
dbo.Lead_Entry.Lead_ID, dbo.Lead_Entry.Lead_Source, dbo.Lead_Entry.Lead_Approved, dbo.Lead_Entry.Solution, dbo.Lead_Entry.Lead_Date,
dbo.Customer_Company.Company_Name, dbo.Customer_Contacts.Contact_Name
FROM dbo.Customer_Contacts INNER JOIN
dbo.Customer_Company ON dbo.Customer_Contacts.Company_ID = dbo.Customer_Company.Company_ID CROSS JOIN
dbo.Lead_Entry

View 4 Replies View Related

Select Records From Two Tables, Plus Page And Order Them?

Feb 7, 2008

Hi,

I'm trying to look for an efficient way to select records from two tables, combine them (not just one set above the other) and also efficiently page the results as well as dynamically order by specific columns. So far I have this....

DECLARE @Temp TABLE
(
IDINTNOT NULL,
NameNVARCHAR(128)NOT NULL,
TypeCHAR(1)NOT NULL
)

INSERT INTO @Temp
SELECT i.ID, i.Name, Type = 'I' FROM Item i
UNION
SELECT p.ID, p.Name, Type = 'P'FROM Package p

SELECT * FROM @Temp
ORDER BY Name ASC

I was going to then implement some sort of of ROW_NUMBER like paging and ordering on the @Temp table variable. Problem is there could be potentially 1000's or more Items and Packages and they would all go in this single Temp table before having records 1 to 10 returned. Is there a more efficient way of doing this before I proceed any further?

Many thanks for any help! :)

View 10 Replies View Related

Transact SQL :: Select Records From Two Child Tables

Jun 18, 2015

i have 3 tables names parent, child1, child2 parent has 1 record, child1 has 2 record and child 3 has 3 records the script

select Parent.*,child1.f1,child2.f2  from child1 inner join Parent on parent.id =child1.id 
inner join child2 on child1.id  =child2.id 

running above query gives me sixes rows but i want only all rows of childs but not their Cartesian products

Object: Table [dbo].[Parent] Script Date: 06/18/2015 17:33:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Parent](
[id] [int] NOT NULL,

[code]....

View 8 Replies View Related

Why I Am Not Able To Select Records Against The Temparary Tables From Tempdb Database?

Jun 25, 2007

Hi, all experts here,



I encontered a problem which did not allow me to select any thing from the temparary tables of the tempdb database. (e.g one of the temparary tables is #239E4DCF), why is that and how can we solve this problem? As I urgently need to look at this tempdb database, it is full. I am looking forward to hearing from you and thanks a lot in advance for your help.



With best regards,



Yours sincerely,



View 1 Replies View Related

HOW To Select A Matrix (cross Join) With Empty Records To Retrieve The Same Amount Of Records For Each Cell

Nov 2, 2006

Hello

Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:

This is a simple matrix


test a

text b








text c








text d

text e

text f








text g










This is a matrix with all the same row-height.



test a

text b

.








text c

.
.









text d

text e

text f








text g

.

.









Thx you a lot

View 3 Replies View Related

How To: Create A SELECT To Select Records From A Table Based On The First Letter.......

Aug 16, 2007

Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
 
Thanks Ross

View 3 Replies View Related

Insert Records From Foxpro Tables To SQL Server Tables

Apr 22, 2004

Hi,

Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:

1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.

I only know the following ways to import Foxpro data into SQL Server:

#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables

I'm thinking whether the following choices will be better than the current way:

1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2

Thank you for any suggestion.

View 2 Replies View Related

SQL Select Statement To Select The Last Ten Records Posted

Aug 6, 2007

SELECT Top 10    Name, Contact AS DCC, DateAdded AS DateTimeFROM         NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database.  As you know each day someone could add one or two records, how can I write it show the last 10 records entered.

View 2 Replies View Related

Multiple Tables Select Performance - SQL 2005 - Should It Take 90 Seconds For A Select?

Dec 4, 2007

I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks

View 7 Replies View Related

Select * From Table Does Not Select All Records

Aug 17, 2005

I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.

Any suggestions?

Thanks!

GordonG

View 13 Replies View Related

Select Top 25% Records,except For Top 25% Records

Aug 30, 2006



A basic select statement to be used.

View 3 Replies View Related

Select Records

Apr 30, 2007

Hello,I am selecting some articles and some comments related with it:SELECT a.ArticleID, a.Title, a.Content, c.CommentId, c.Title, c.Comment, u.UserName AS ArticleAuthorName, u.UserEmail AS ArticleAuthorEmailFROM Articles aINNER JOIN Users u ON a.AuthorID = u.UserIDINNER JOIN Comments c ON a.ArticleID = c.ArticleIDI have 2 problems which I am trying to solve:1. Comments table also have an AuthorId So for each comment I also want to join to Users table and get the author name and email. How can I do this?2. I want to select all Articles even if it has comments or not. Can I use Inner Join or should I use Left Join? Is Outer Join still available in SQL 2005?Thank You,Miguel

View 2 Replies View Related

Select 11-20 Records?

Jul 14, 2007

 Hi there,
I'm new to SQL.
I have encoutered a problem, I know how to select top 10 records from the database, but what about 11-20?
I can't use
Quote:

View 3 Replies View Related

How To Select Last 10 Records

Jan 12, 2006

I have a simple table something like this
ID    First        Last
1      Bob       Hope2     Charles   Draw3     Mark     Andrews
Etc, etc
I need the SQL statement select the last 10 records in the table, so if the highest ID id 82 I need all fields of records with ID 72-82
Help appreciated, i'm sure it must be simple enough buy mind is blank.
thanks
 

View 1 Replies View Related

Select 11-20 Records?

Jul 14, 2007

Hi there,
I'm new to SQL.
I have encoutered a problem, I know how to select top 10 records from the database, but what about 11-20?
I can't use
Quote: SELECT TOP 10 * FROM table WHERE id > 10
because even though my ID is auto numbered, but I have deleted some entries.
Does anyone have good solutions?

p.s I tried this and doesn't work either
Quote: SELECT TOP 10 * FROM table WHERE id IN(SELECT TOP 20 * FROM table ORDER by date DESC) ORDER BY date ASCENDING
TIA
-noPcz

View 5 Replies View Related

Select Records

Aug 16, 2005

Stijn writes "Hello,

I have SQL 7 with WINDOWS 2000 SERVER

My problem is that i am looking for a script

my db is for equipement inventory

how can i select all the records from a room,
if this room (room1)has less of 100 records he needs te go to the follow room(room2), en the next room until he's got 100 records,

sorry for my english"

View 2 Replies View Related

How To Select Last Few Records

Aug 18, 2005

Mack writes "I want to select last 2 records from a table AA which has following 5 records.

111
222
333
444
555

There is no primary key.
Any tip will be really appretiated.
Thanks for taking time to read it."

View 2 Replies View Related

Select Top 50 Records

Oct 4, 2007

Hi,
How to write a query to get top 50 distinct records from a table.
I mean I have a table 'C1_Subscribers' with a field name 'Nickname'. Here I need to select top 50 non-repeated nickname only.

Can anybody can help me!!

View 3 Replies View Related

SELECT-how To Get Only The Last 20 Records?

Jul 23, 2005

Hello,Firstly, sorry for my english.I have problem with creating SQL statement. I am beginner and I think thatit is very easy to do. Look -I have to get only the last 20 records from table ABC (f.eg.) and accordingto that how to create the SQL statement? F.eg. SELECT name, forename, dateFROM abc WHERE....You know - how to finish the statement to get only 20 records lastlyinserted into the table?Thanks and I am waiting for helpfull answers.

View 3 Replies View Related

Select Top N Records With A Certain Value

Jan 2, 2008

I have a table, productTable, that contains a ProductID column, a bunch of attribute columns like manufacturer etc, and a GroupID. Many different productIDs can share the same GroupID which means that they are basically the same product with one different attribute such as color. What I'm trying to do is select all the products in the TOP N GroupIDs. I'm using this to paginate my query results. I tried this which does not work:

SELECT DISTINCT TOP 10 *
FROM productTable
WHERE GroupID NOT IN
(SELECT DISTINCT TOP 10 GroupID
FROM productTable
ORDER BY GroupID)
ORDER BY GroupID

This returns the TOP 10 rows, but I would like all the ProductIDs for the TOP 10 GroupIDs

View 7 Replies View Related

How Do I Select All Records In One Table That Have NO Related Records In Another Table?

Jul 20, 2005

I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks

View 2 Replies View Related

Size Of Tables/records

Jul 2, 2004

Is it possible to determine the data size of records in a table. basically I would like to run a select query with a condition and the result will be xKB. I know that this is possible on a database level, but can it be done on a record level (or number of records).

Cheers

H

View 4 Replies View Related

Update Of Records From Other Tables

Sep 11, 2000

I am trying to update a field within one table with the values from another table. With the criteria that another field in each table are equal. What is the correct way to do this. My syntax is all wrong.

thanks
Jason

View 1 Replies View Related

Counting All Records In All Tables

Feb 26, 2001

I report SQL Server table information (table names, field names, field datatypes, etc.) to my users using an ACCESS front end. Most of this information exists in the system tables. But I can't find a record count per table in the system tables. What's the fastest way to get a record count for all tables?

View 1 Replies View Related

Need Help With Transfer Many Records Between Tables

Jan 27, 2008

Hi!
I have 2 tables (both have the same structure):
ID -> bigint (identity, not for replication, primary key)
Url -> nvarchar(1000)
MainUrl -> nvarchar(1000)

Tbl1 cantains about 0,5 mln records, and tbl2 - 1 mln.
What I need, is to copy records from tbl2 to tbl1. But records in tbl1 are unique, and it can't change. (Unique must be only "Url"; (and ID, but it's automatic)). How can I do this in fast way? Now I'm using SELECT for each record in tbl2 to see if it exist in tbl1. But it's a bit slow... Is there any faster method? (One thing: I'm beginner in databeses, so I'm wrote VB application to transfer records. How can I do it using only Microsoft Sql server?)
--------------
I'm forgot to write, I'm using MsSql 2005.

View 7 Replies View Related

Outlying Records Between Two Tables

Nov 15, 2006

I have two tables that have a common column (ID). Now, what i am trying to do is find what is not in one table that is in the other.

For instance:

Table A
ID NAME
1 Tom
2 George
3 Richard

Table B
ID NAME
1 Tom
3 Richard
4 Kevin

With this information, I am trying to write a query that would tell me that Kevin is the only record that doesn't exist in both tables ... like an outlier.

I have tried using something like the following:

SELECT distinct id, name
FROM Table 1 INNER JOIN Table 2
ON Table 1.id <> Table 2.id

Any help would be great. Thanks!

-L

View 3 Replies View Related

Count All Records In All Tables

Apr 13, 2006

I need some help with this. I was able to count all the records in ourdatabase using the user_tables and user_tab_columns tables afterrefreshing the statistics on this database.We are doing an upgrade of a system and I will not be able to refreshthe statistics during the upgrade. I need more of a manual process ofrunning these queries.Now I do:select A.table_name, round(A.num_rows,0) as rowcount,count(b.table_name) as ColumnCountfrom dba_tables A, dba_tab_columns Bwhere A.table_name = B.table_name and A.owner in ('PS','SYSADM')group by A.table_name, A.num_rowsorder by rowcount desc, columncount descBut I can't use the num_rows anymore so I was thinking more to do this:Select A.table_name from(select count(*) from A.Table_name B where A.Table_name =B.Table_Name)from user_tableThis does not work for me since I don't know how to pass the table_namefrom the first select to the second select. The logic is there but thesyntax is not.Please help.

View 1 Replies View Related

Changing Records In Tables

Jul 12, 2006

Hi,I was just wondering... Is there any built-in function in MS SQL, which willallow me to do rows' "capitalisation"?Lets say that in database.table.name I've got:"FOO BAR LTD.", which I want to change to "Foo Bar Ltd.""Foo bar LTD.", which I want to be "Foo Bar Ltd.".Is there any way of doing this or do I have to read it from database, changein some script, and then insert it back into the table?Hope it's all clear ;-)Thank you,Martin

View 2 Replies View Related

Compare Records Of Two Tables?

Jul 31, 2007

Hi

I am trying for compare two tables records and if which records are not match insert in to both table and at end both table records will be same.

using stored procedure & I need also pass server name database name.

thanks

View 4 Replies View Related

Delete Same Records (same Pk) In Two Tables

Jan 8, 2008



Hello,

I have the following problem:

2 tables: both have the same pk values.

one table must be deleted based on a filter (I mean the table is not delete completely but only some records), I would like to delete same records in the second table.
for ex:
table 1: pk: 1,2,3,4,5
table 2: pk: 1,2,3,4,5
table 1: deleting 1,2, 3 thus also in table 2 pk: 1,2,3 must be deleted.
At the and of process Table1 and Table2 must have the same records (always also in the case of failure, errors and so on ).

The target is avoid using triggers.
OUTPUT is not useful because it writes what is deleted (or may be useful but how to use it?).

How can I do?

Thank

View 1 Replies View Related

Select Records. Going Crazy.

Apr 29, 2007

Hello,I have two tables, Articles and Comments, with the following columns:Articles - [ArticleId] (PK), [ArticleTitle], [ArticleText], [ArticlePubDate]Comments - [CommentId] (PK), [ArticleId] (FK), [CommentTitle], [CommentText]I need to display on a web page the articles published during the last week and their comments.What I need is:1. Get Articles (DONE)  2. Get Comments for each ArticleThe solution I see are:1. Create a Stored procedure that somehow outputs 2 tables:     Articles and Comments associated with those articles2. Create 2 Stored procedures:    The first one outputs the articles.    The second output all comments given an article ID    In this case, while the data is being displayed on the page it will load the comments for each article.    The problem is that I will have many round trips to the server.I know how to use (2) but this would give me many round trips to the database.Could someone help me out with this?Thanks,Miguel  

View 4 Replies View Related

Select Distinct Records

Oct 10, 2007

 Hello, I have the following tables: declare @B table (Bid int identity, description varchar(50)) declare @P table (Pid int identity, Bid int, description varchar(50)) declare @T table (Tid int identity, description varchar(50)) declare @TinP table (TinPid int identity, Tid int, Pid int) insert into @B (description) select 'B1' insert into @B (description) select 'B2' insert into @P (description, Bid) select 'P1', 1 insert into @P (description, Bid) select 'P2', 1 insert into @P (description, Bid) select 'P3', 2 insert into @T (description) select 'T1' insert into @T (description) select 'T2' insert into @T (description) select 'T3' insert into @TinP (Tid, Pid) select 1, 2 insert into @TinP (Tid, Pid) select 2, 2 insert into @TinP (Tid, Pid) select 3, 3 select * from @B select * from @P select * from @T select * from @TinP I need to get all records in T (Tid and description) which are related to a given BId So for @Bi = 1 I would get: Tid   Description 1     T1 2     T2 So I need the distinct values. How to solve this? Thanks, Miguel

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved