Help with cross products and functions

Hello,
I have two tables that i need to join. The first table has a column which
is an encoded addition of one or more values in a second table.
table 1:
col1 col2 col3
1 a b 12
...
table 2:
col1 col2
1 x 4
2 y 8
3 z 16
...
the record for table 1, maps to records 1 and 2 from table 2. I know that
the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of this
math function that decodes the encryption.
I have a function that is able to return a table that lists all the values
in table 2 that correspond to the supplied value in table 1..ie if i call
function fn_mappings(12) i get a table called @.result:
table2value
1 4
2 8
what i need to do is then combine these records together to get a view as
follows
col1 col2 table2value
1 a b 4
2 a b 8
...
is this possible to do? i need this to happen for EACH row in table 1 to
find corresponding records in table 2.
thanks for any and all help!
BenIn order to link both tables, I took the liberty of "re-create" a convenient
environment to get the results you asked. I hope it is ok.
Let me know if it works for you
-- Begin Script
create table tbl1
( id int primary key
, col1 varchar(10)
, col2 varchar(10)
, col3 int
)
create table tbl2
(
id int primary key
, fkid int
, col1 varchar(10)
, col2 int
)
insert into tbl1
values (1, 'a', 'b', 12)
insert into tbl2
values (1, 1, 'x', 4)
insert into tbl2
values (2, 1, 'x', 8)
insert into tbl2
values (3, 2, 'x', 16)
-- View the output of both tables
select * from tbl1
select * from tbl2
go
create function dbo.fn_mappings(@.in int)
returns table
as
return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
col3 = @.in))
go
-- View the dbo.fn_mappings() output
select * from dbo.fn_mappings(12)
-- Output requested
select t1.id
, t1.col1
, t1.col2
, t2.col2
from tbl1 t1
inner join tbl2 t2
on t1.id = t2.fkid
-- Drop all objects
drop function dbo.fn_mappings
drop table tbl1
drop table tbl2
"Ben" wrote:

> Hello,
> I have two tables that i need to join. The first table has a column which
> is an encoded addition of one or more values in a second table.
> table 1:
> col1 col2 col3
> 1 a b 12
> ...
> table 2:
> col1 col2
> 1 x 4
> 2 y 8
> 3 z 16
> ...
> the record for table 1, maps to records 1 and 2 from table 2. I know that
> the value 12 in table 1 (col3) mapps to rows 1,2 in table 2 because of thi
s
> math function that decodes the encryption.
> I have a function that is able to return a table that lists all the values
> in table 2 that correspond to the supplied value in table 1..ie if i call
> function fn_mappings(12) i get a table called @.result:
> table2value
> 1 4
> 2 8
> what i need to do is then combine these records together to get a view as
> follows
> col1 col2 table2value
> 1 a b 4
> 2 a b 8
> ...
> is this possible to do? i need this to happen for EACH row in table 1 to
> find corresponding records in table 2.
> thanks for any and all help!
> Ben|||Edgardo,
Thank you for your response...however, I do not have the liberty of changing
table2's structure. The data is provided by an external source and I am
creating custom reporting for it. Therefor i cant give it a foreign key typ
e
column.
do you have any other ideas?
Thanks again
Ben
"Edgardo Valdez, MCSD, MCDBA" wrote:
> In order to link both tables, I took the liberty of "re-create" a convenie
nt
> environment to get the results you asked. I hope it is ok.
> Let me know if it works for you
> -- Begin Script
> create table tbl1
> ( id int primary key
> , col1 varchar(10)
> , col2 varchar(10)
> , col3 int
> )
> create table tbl2
> (
> id int primary key
> , fkid int
> , col1 varchar(10)
> , col2 int
> )
> insert into tbl1
> values (1, 'a', 'b', 12)
> insert into tbl2
> values (1, 1, 'x', 4)
> insert into tbl2
> values (2, 1, 'x', 8)
> insert into tbl2
> values (3, 2, 'x', 16)
> -- View the output of both tables
> select * from tbl1
> select * from tbl2
> go
> create function dbo.fn_mappings(@.in int)
> returns table
> as
> return (select id, col2 from tbl2 where fkid = (select id from tbl1 where
> col3 = @.in))
> go
> -- View the dbo.fn_mappings() output
> select * from dbo.fn_mappings(12)
> -- Output requested
> select t1.id
> , t1.col1
> , t1.col2
> , t2.col2
> from tbl1 t1
> inner join tbl2 t2
> on t1.id = t2.fkid
> -- Drop all objects
> drop function dbo.fn_mappings
> drop table tbl1
> drop table tbl2
>
> "Ben" wrote:
>

Help with counting query

This is a counting issue following an inner join.
I have two tables (simplifying it for clarity)
Offering Table: OfferingID, year
Registration Table: RegistrationID, OfferingID, Registration_Status
-- Start Table Definitions
--
CREATE TABLE [dbo].[Test1] (
[OfferingID] [int] NOT NULL ,
[Offering_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[test2] (
[RegistrationID] [int]NOT NULL ,
[OfferingID] [int] NOT NULL ,
[Registration_Status] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO Test1 Values (1,2005)
INSERT INTO Test1 Values (2,2005)
INSERT INTO Test1 Values (3,2006)
INSERT INTO Test2 Values (1,1,1)
INSERT INTO Test2 Values (2,1,1)
INSERT INTO Test2 Values (3,1,2)
INSERT INTO Test2 Values (4,1,2)
INSERT INTO Test2 Values (5,1,2)
INSERT INTO Test2 Values (6,2,1)
INSERT INTO Test2 Values (7,2,1)
INSERT INTO Test2 Values (8,3,1)
INSERT INTO Test2 Values (9,3,2)
-- END Table Definitions
--
Now I want to do some counting, basically I want to count the number of
offerings given in 2005 and 2006 (correct number is 2 and 1 respectivly)
I also want to count the total number of registrations of
Registration_Status 1 (Attended) and 2 (Cancelled) (correct number is 5 and
4
respectivly)
This issue is I want them all in a single record. I came up with this SQL
Query
Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled,
SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005],
SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
FROM test1 t1 INNER JOIN
test2 t2 ON t1.OfferingId = t2.OfferingID
Returns:
Attended Cancelled 2005 2006
-- -- -- --
5 4 7 2
It returns the correct Registration Status counts, but not the correct
number of offerings per year because the records are counted over and over
due to the join. What I really want is to count only distinct records in
test1.
Attended Cancelled 2005 2006
-- -- -- --
5 4 2 1
Thanks!You need to use COUNT(distinct).
Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled,
COUNT(distinct CASE WHEN t1.Offering_Year = '2005'
THEN t1.OfferingID ELSE NULL END) AS [2005],
COUNT(distinct CASE WHEN t1.Offering_Year = '2006'
THEN t1.OfferingID ELSE NULL END) AS [2006]
FROM test1 t1 INNER JOIN
test2 t2 ON t1.OfferingId = t2.OfferingID
Note that the ELSE NULL is optional; if there is no ELSE clause the
CASE defaults to NULL when not matched. But it is a bit clearer with
the explicit assignment. COUNT does not count NULLs.
Roy Harvey
Beacon Falls, CT
On Tue, 2 May 2006 11:05:02 -0700, Ramez
<Ramez@.discussions.microsoft.com> wrote:

>Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
>Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
>Cancelled,
> SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005]
,
> SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
>FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID|||Actually you don't need a join.
try this
select * from
(Select SUM(CASE WHEN Registration_status = 1 THEN 1 ELSE 0 END) AS
Attended,
SUM(CASE WHEN Registration_status = 2 THEN 1 ELSE 0 END) AS
Cancelled from test2) as t2,
(select SUM(CASE WHEN Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005],
SUM(CASE WHEN Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006]
from test1) t1
Hope this helps.
--
"Ramez" wrote:

> This is a counting issue following an inner join.
> I have two tables (simplifying it for clarity)
> Offering Table: OfferingID, year
> Registration Table: RegistrationID, OfferingID, Registration_Status
> -- Start Table Definitions
> --
> CREATE TABLE [dbo].[Test1] (
> [OfferingID] [int] NOT NULL ,
> [Offering_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[test2] (
> [RegistrationID] [int]NOT NULL ,
> [OfferingID] [int] NOT NULL ,
> [Registration_Status] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO Test1 Values (1,2005)
> INSERT INTO Test1 Values (2,2005)
> INSERT INTO Test1 Values (3,2006)
> INSERT INTO Test2 Values (1,1,1)
> INSERT INTO Test2 Values (2,1,1)
> INSERT INTO Test2 Values (3,1,2)
> INSERT INTO Test2 Values (4,1,2)
> INSERT INTO Test2 Values (5,1,2)
> INSERT INTO Test2 Values (6,2,1)
> INSERT INTO Test2 Values (7,2,1)
> INSERT INTO Test2 Values (8,3,1)
> INSERT INTO Test2 Values (9,3,2)
> -- END Table Definitions
> --
> Now I want to do some counting, basically I want to count the number of
> offerings given in 2005 and 2006 (correct number is 2 and 1 respectivly)
> I also want to count the total number of registrations of
> Registration_Status 1 (Attended) and 2 (Cancelled) (correct number is 5 an
d 4
> respectivly)
> This issue is I want them all in a single record. I came up with this SQL
> Query
> Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
> Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
> Cancelled,
> SUM(CASE WHEN t1.Offering_Year = '2005' THEN 1 ELSE 0 END) AS [2005
],
> SUM(CASE WHEN t1.Offering_Year = '2006' THEN 1 ELSE 0 END) AS [2006
]
> FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID
> Returns:
> Attended Cancelled 2005 2006
> -- -- -- --
> 5 4 7 2
> It returns the correct Registration Status counts, but not the correct
> number of offerings per year because the records are counted over and over
> due to the join. What I really want is to count only distinct records in
> test1.
> Attended Cancelled 2005 2006
> -- -- -- --
> 5 4 2 1
> Thanks!|||Hello, Ramez
To get the desired result, you can simply use something like this:
SELECT
(SELECT COUNT(*) FROM test2 WHERE Registration_status=1) AS Attended,
(SELECT COUNT(*) FROM test2 WHERE Registration_status=2) AS Cancelled,
(SELECT COUNT(*) FROM test1 WHERE Offering_Year=2005) AS [2005],
(SELECT COUNT(*) FROM test1 WHERE Offering_Year=2006) AS [2006]
If you really want to use a join (but I don't see any good reason for
this), you can use the following query:
SELECT
SUM(CASE WHEN Registration_status = 1 THEN 1 ELSE 0 END) AS Attended,
SUM(CASE WHEN Registration_status = 2 THEN 1 ELSE 0 END) AS Cancelled,
COUNT(DISTINCT CASE WHEN Offering_Year = '2005' THEN t1.OfferingID
END) AS [2005],
COUNT(DISTINCT CASE WHEN Offering_Year = '2006' THEN t1.OfferingID
END) AS [2006]
FROM test1 t1 INNER JOIN test2 t2 ON t1.OfferingId = t2.OfferingID
However, this comes at the expense of a warning: "Warning: Null value
is eliminated by an aggregate or other SET operation."; the warning can
be eliminated by using SET ANSI_WARNINGS OFF, but this is not
recommended (one of the reasons is because ANSI_WARNINGS is required to
be ON for using indexes on computed columns and indexed views).
Razvan|||Roy,
Do we need the join and case and distinct ops for this scenario. I
seriously doubt it.
--
"Roy Harvey" wrote:

> You need to use COUNT(distinct).
> Select SUM(CASE WHEN t2.Registration_status = 1 THEN 1 ELSE 0 END) AS
> Attended,
> SUM(CASE WHEN t2.Registration_status = 2 THEN 1 ELSE 0 END) AS
> Cancelled,
> COUNT(distinct CASE WHEN t1.Offering_Year = '2005'
> THEN t1.OfferingID ELSE NULL END) AS [2005],
> COUNT(distinct CASE WHEN t1.Offering_Year = '2006'
> THEN t1.OfferingID ELSE NULL END) AS [2006]
> FROM test1 t1 INNER JOIN
> test2 t2 ON t1.OfferingId = t2.OfferingID
> Note that the ELSE NULL is optional; if there is no ELSE clause the
> CASE defaults to NULL when not matched. But it is a bit clearer with
> the explicit assignment. COUNT does not count NULLs.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 2 May 2006 11:05:02 -0700, Ramez
> <Ramez@.discussions.microsoft.com> wrote:
>
>

Help with complex query

Hi Everyone,

I need some help writing a query that joins a table to a UNION query. I was wondering what is the most efficent way to do this.

Tables

Employees (EMPID, FULLNAME)

DailySchedules (SCHID,EMPID, SCHDATE,DEPTID)

GeneralSegments (GSID,EMPID,STARTTIME,STOPTIME)

DetailSegments (DSID,EMPID,STARTTIME,STOPTIME)

I need to join the records from GeneralSegments and DetailSegments THEN inner join DailySchedules and Employees.

Query must output:

EMPID, FULLNAME, SCHDATE,STARTTIME,STOPDTIME

Thank You

Can you provide some sample data (preferrably in the form of insert statements) along with a data representation of what you want for output?

Thanks.

|||

Untested, but, it should give you an idea

Code Snippet

SELECT Segments.EMPID,
Employees.FULLNAME,
DailySchedules.SCHDATE,
Segments.STARTTIME,
Segments.STOPDTIME

FROM

(
SELECT EMPID, STARTTIME, STOPTIME FROM GeneralSegments
UNION
SELECT EMPID, STARTTIME, STOPTIME FROM DetailSegments
) Segments
INNER JOIN Employees
ON Employees.EMPID = Segments.EMPID
INNER JOIN DailySchedules
ON DailySchedules.EMPID = Segments.EMPID

|||

Hi David,

That example will work. One question though, I have to display the next 14 days (2 weeks) on the web. Where should I place my WHERE clause? In the outer query or include the date range in both union statements?

ie. WHERE SCHDATE BETWEEN GETDATE() AND GETDATE() + 14

Thank You

|||

Is SCHDATE column present in the tables in the DailySchedule table or the segments tables? In any case, it doesn't matter. SQL Server will automatically roll the schdate predicate into the inner queries as well based on their reference. For example, if you want to filter on STARTTIME then you can include just one WHERE clause like below and it will be applied to both GeneralSegments & DetailSegments

Code Snippet

SELECT Segments.EMPID,
Employees.FULLNAME,
d.SCHDATE,
Segments.STARTTIME,
Segments.STOPDTIME

FROM

(
SELECT EMPID, STARTTIME, STOPTIME FROM GeneralSegments
UNION
SELECT EMPID, STARTTIME, STOPTIME FROM DetailSegments
) Segments
INNER JOIN Employees
ON Employees.EMPID = Segments.EMPID
INNER JOIN DailySchedules
ON DailySchedules.EMPID = Segments.EMPID
WHERE Segments.STARTTIME >= @.Start

sql

Help with an SQL view problem

Hi,

Have hit upon a problem with an SQL view, and wondered if anyone else had come upon a similar problem.

I have 2 tables in my database:

Categories

Requests

The Categories table contains three fields: CategoryID, ParentID, CategoryName

My Categories table has 3 entries:

CategoryID: 1
ParentID: Null
Title: Category 1

CategoryID: 2
ParentID: 1
Title: Category 2

CategoryID: 3
ParentID: 2
Title: Category 3

The Reqests table contains the following: RequestID, CategoryID, Title etc

My Requests table also has 3 entries:

RequestID: 1
CategoryID: 1
Title: Request 1

RequestID: 2
CategoryID: 2
Title: Request 2

RequestID: 3
CategoryID: 3
Title: Request 3

The problem I am faced with is that I need to build up a select statement which can return all requests filtered by the category ID, but this also needs to include any requests whose sub categories are a sub of the master category (if that makes sense).

Am not sure how I could go about this, I'm not sure what I would need to do to be able to loop up through all the parent ID's until I reach a null value?

Any help on this would be much appreciated.

Matt

Its good that you provided sample data. Can you also provide expected output for a given categoryID of 1 or 2 so we can understand your requirement better?

|||

Here is a query to get you a listing of all top level parents and one child.

select c.CategoryID ParentCategoryID, c.Title ParentTitle , r.RequestID ParentRequestID, r.Title , cc.CategoryID ChildCategoryID, cc.CategoryTitle ChildTitle , cc.RequestID ChildRequestID, cc.ChildRequestTitlefrom Categories cleftouter join (select cx.CategoryID, cx.ParentID, cx.Title CategoryTitle , rx.RequestID, rx.Title RequestTitlefrom Categories cxleftouter join Requests rxon cx.CategoryID = rx.CategoryIDand cx.ParentIDISNOT NULL )as ccon cc.ParentID = c.CategoryIDleftouter join Requests ron c.CategoryID = r.CategoryIDwhere c.ParentIDISNULL

But I do believe that this only gets you part of the way.

|||

Hi,

This article should provide you with some helpful guidance.

http://mssqltips.com/tip.asp?tip=938

|||

Thanks for all of your help, had a look at the recursive common table expressions in SQL server 2005 and looks like I now have my solution.

Cheers,

Matt

|||

Hi,

Managed to get around my first problem using the recursive common table expressions but have now hit upon another as I want to add paging using the Row_Number and custom sorting.

Here is what I have in my stored procedure so far:

DECLARE @.sqlString nvarchar(4000)

IF @.SortExpression = ''
BEGIN
SET @.SortExpression = 'DateCreated DESC'
END

IF @.CategoryID = 0
BEGIN
WITH AllJobRequests AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY @.SortExpression) AS RowNum FROM dbo.JobRequests
)
SELECT * FROM AllJobRequests WHERE RowNum BETWEEN @.Start AND @.End ORDER BY RowNum ASC
END
ELSE
BEGIN
SET @.sqlString = 'WITH GetSubCategories (JobRequestID, CategoryID, ParentID, CategoryName, Depth, RowNum) AS
(
SELECT dbo.JobRequests.JobRequestID, dbo.JobRequests.CategoryID, dbo.Categories.ParentID, dbo.Categories.CategoryName, 0 AS Depth, ROW_NUMBER() OVER (ORDER BY dbo.JobRequests.' + @.SortExpression + ') AS RowNum
FROM dbo.Categories INNER JOIN
dbo.JobRequests ON dbo.Categories.CategoryID = dbo.JobRequests.CategoryID
WHERE dbo.Categories.CategoryID = ' + CONVERT(nvarchar(10), @.CategoryID) + '
UNION ALL
SELECT dbo.JobRequests.JobRequestID, dbo.JobRequests.CategoryID, dbo.Categories.ParentID, dbo.Categories.CategoryName, GetSubCategories.Depth + 1 AS Depth, ROW_NUMBER() OVER (ORDER BY dbo.JobRequests.' + @.SortExpression + ') AS RowNum
FROM dbo.Categories INNER JOIN
dbo.JobRequests ON dbo.Categories.CategoryID = dbo.JobRequests.CategoryID
JOIN GetSubCategories ON Categories.ParentID = GetSubCategories.CategoryID
)
SELECT DISTINCT * FROM GetSubCategories WHERE RowNum BETWEEN ' + CONVERT(nvarchar(10), @.Start) + ' AND ' + CONVERT(nvarchar(10), @.End) + ' ORDER BY RowNum ASC'
EXEC sp_executesql @.sqlString

This works to an extent apart from the sorting, have a look at the following output gained (the last bold characters are the RowNum order)

2 13 12 Central Heating 1 1
7 12 NULL Plumbing 0 1
6 12 NULL Plumbing 0 2
5 12 NULL Plumbing 0 3
4 12 NULL Plumbing 0 4
3 12 NULL Plumbing 0 5

The results are good in that they return both the output for the master category "Plumbing" and then the child category "Central Heating", but what would I now need to do to change this stored procedure to ensure I order the columns correctly?

Thanks,

Matt

Help with an Outer Join

Hello I'm tying to create query that selects data from two tables depending on the Employee and the range of dates. I have not used outer joins before and I either receive no data or receive too much data.

SELECT qad.NEW_USERS, qad.TRANSACTIONS, CONVERT( VARCHAR, qad.DATA_DATE, 101) DATA_DATE,
qas.QA_SCORE
FROM QA_SCORES qas FULL OUTER JOIN QA_DATA qad
ON (qas.EMPLOYEE_ID = qad.EMPLOYEE_ID AND qas.QA_DATE >= @.startDate AND qas.QA_DATE < @.endDate)
WHERE qad.DATA_DATE >= @.startDate AND qad.DATA_DATE < @.endDate
ORDER BY qad.DATA_DATE

Here are the tables:

QA_DATA:

QA_DATA_ID

EMPLOYEE_ID

NEW_USERS

TRANSACTIONS

DATA_DATE

4

11

0

0

12/1/2006

5

11

9

14

12/2/2006

6

1

2

3

1/1/2006

7

1

2

3

12/1/2006

8

11

6

18

12/3/2006

9

11

14

17

12/4/2006

10

11

7

16

12/5/2006

11

12

8

15

12/1/2006

12

12

0

0

12/3/2006

13

12

8

21

12/4/2006

QA_SCORES:

QA_ID

QA_DATE

QA_SCORE

EMPLOYEE_ID

1

1/18/2007

85

11

2

1/9/2007

83.01

11

Sometimes I receive this:

NEW_USERS

TRANSACTIONS

DATA_DATE

QASCORE

0

0

12/1/2006

85

0

0

12/1/2006

83.01

9

14

12/2/2006

83.01

9

14

12/2/2006

85

6

18

12/3/2006

85

6

18

12/3/2006

83.01

14

17

12/4/2006

83.01

14

17

12/4/2006

85

7

16

12/5/2006

85

7

16

12/5/2006

83.01

If the startDate = 12/01/2006 and the endDate = 12/31/2006 it should look like this:

NEW_USERS

TRANSACTIONS

DATA_DATE

QASCORE

0

0

12/1/2006

9

14

12/2/2006

6

18

12/3/2006

14

17

12/4/2006

7

16

12/5/2006

Because there are no QA_SCOREs in 2006

And if the start date is 1/01/2007 and the endDate is 1/31/2007 it should look like this:

NEW_USERS

TRANSACTIONS

DATA_DATE

QASCORE

1/9/2007

85

1/18/2007

83.01

I have tried quite a few things and just can't figure this out.

Any help is appreciated.

.

Milla:

(1) It appears that your data has the 85 score and the 83 score switched with each other. (2) Also, it appears to me that you have an additional filter criteria that you are probably leaving out -- probably a filter based on "EMPLOYEE_ID". Based on this guess I came up with this query that seems to return the results that you are looking for. I have no idea whether this is close or not, but maybe it will help:

declare @.filter_id integer
set @.filter_id = 11

SELECT qad.NEW_USERS,
qad.TRANSACTIONS,
CONVERT( VARCHAR, coalesce (qad.DATA_DATE, qas.qa_date), 101)
DATA_DATE,
qas.QA_SCORE
FROM QA_SCORES qas
FULL OUTER JOIN QA_DATA qad
ON qas.EMPLOYEE_ID = qad.EMPLOYEE_ID
AND qad.DATA_DATE >= @.startDate AND qad.DATA_DATE < @.endDate
AND qas.QA_DATE >= @.startDate AND qas.QA_DATE < @.endDate
WHERE coalesce (qad.DATA_DATE, qas.qa_date) >= @.startDate
AND coalesce (qad.DATA_DATE, qas.qa_date) < @.endDate
AND coalesce (qas.employee_id, qad.employee_id) = @.filter_id
ORDER BY coalesce (qad.DATA_DATE, qas.qa_date)

-- - Sample Output for 12/1/6 through 12/31/6: -

-- NEW_USERS TRANSACTIONS DATA_DATE QA_SCORE
-- -- --
-- 0 0 12/01/2006 NULL
-- 9 14 12/02/2006 NULL
-- 6 18 12/03/2006 NULL
-- 14 17 12/04/2006 NULL
-- 7 16 12/05/2006 NULL

-- - Sample Output for 1/1/7 through 1/31/7: -

-- NEW_USERS TRANSACTIONS DATA_DATE QA_SCORE
-- -- --
-- NULL NULL 01/09/2007 83.01
-- NULL NULL 01/18/2007 85.00

Dave

|||

Milla:

(If this post is a duplicate, I am sorry; it seems that my post has been lost.)

It appears to me that your query is missing a filter -- probably based on EMPLOYEE_ID. Based on this guess, the following query seems to return the correct data:

declare @.filter_id integer
set @.filter_id = 11

SELECT qad.NEW_USERS,
qad.TRANSACTIONS,
CONVERT( VARCHAR, coalesce (qad.DATA_DATE, qas.qa_date), 101)
DATA_DATE,
qas.QA_SCORE
FROM QA_SCORES qas
FULL OUTER JOIN QA_DATA qad
ON qas.EMPLOYEE_ID = qad.EMPLOYEE_ID
AND qad.DATA_DATE >= @.startDate AND qad.DATA_DATE < @.endDate
AND qas.QA_DATE >= @.startDate AND qas.QA_DATE < @.endDate
WHERE coalesce (qad.DATA_DATE, qas.qa_date) >= @.startDate
AND coalesce (qad.DATA_DATE, qas.qa_date) < @.endDate
AND coalesce (qas.employee_id, qad.employee_id) = @.filter_id
ORDER BY coalesce (qad.DATA_DATE, qas.qa_date)

-- - Sample Output for 12/1/6 through 12/31/6: -

-- NEW_USERS TRANSACTIONS DATA_DATE QA_SCORE
-- -- --
-- 0 0 12/01/2006 NULL
-- 9 14 12/02/2006 NULL
-- 6 18 12/03/2006 NULL
-- 14 17 12/04/2006 NULL
-- 7 16 12/05/2006 NULL

-- - Sample Output for 1/1/7 through 1/31/7: -

-- NEW_USERS TRANSACTIONS DATA_DATE QA_SCORE
-- -- --
-- NULL NULL 01/09/2007 83.01
-- NULL NULL 01/18/2007 85.00

|||

Hi,

Can you please tell what you want to do in text?

By setting the WHERE clause like this, you are not making a FULL OUTER JOIN but a RIGHT OUTER JOIN.

Why? For all the records that are in QA_SCORES and that have no associated record in QA_DATA, the field QA_DATA.DATA_DATE will always be NULL. Therefor, these records can not pass the WHERE clause.

You can prevent this by changing the WHERE keyword to the AND keyword so that the condition is included in the JOIN.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Take a look at your WHERE clause:

SELECT qad.NEW_USERS, qad.TRANSACTIONS,
CONVERT( VARCHAR, qad.DATA_DATE, 101) DATA_DATE,
qas.QA_SCORE
FROM QA_SCORES qas
FULL OUTER JOIN QA_DATA qad
ON (qas.EMPLOYEE_ID = qad.EMPLOYEE_ID
AND qas.QA_DATE >= @.startDate AND qas.QA_DATE < @.endDate)
WHERE qad.DATA_DATE >= @.startDate AND qad.DATA_DATE < @.endDate
ORDER BY qad.DATA_DATE

This where clause will apply to all rows that are returned from the FROM clause. So you will not receive qas rows where the qad.DATA_DATE is not greater than @.startDate. So any null rows would be eliminated from the set. You can put the WHERE clause stuff up in the ON clause and this won't affect how rows match in the qas set.

|||Since you didn't tell us what you are trying to get as a result, use "LEFT OUTER JOIN" instead of "FULL OUTER JOIN", that will probably get you closer.|||

Thanks for the replies. What you are saying makes sence.

But I still can't seem to get it to work.

Here is what I have now:

declare @.startDate datetime
declare @.endDate datetime
set @.startDate = 1/1/2007
set @.endDate = 1/31/2007

SELECT emp.NAME NAME, qa.NEW_USERS NewUsers, qa.TRANSACTIONS Trans,
CONVERT( VARCHAR, coalesce (qa.DATA_DATE, qas.qa_date), 101) DATA_DATE, qas.QA_SCORE
FROM EMPLOYEE emp INNER JOIN
QA_DATA qa ON (emp.EMPLOYEE_ID = qa.EMPLOYEE_ID AND emp.REPORTABLE = 1) FULL OUTER JOIN QA_SCORES qas
ON (qas.EMPLOYEE_ID = qa.EMPLOYEE_ID
AND qa.DATA_DATE >= @.startDate AND qa.DATA_DATE < @.endDate
AND qas.QA_DATE >= @.startDate AND qas.QA_DATE < @.endDate)

No matter what dates I enter it returns all rows. I'm guessing it's because there is no where statement, but if I enter a where statement it returns no rows.

Here is what it is returning:

Allard

Rich

0

0

12/1/2006

NULL

Allard

Rich

9

14

12/2/2006

NULL

Allard

Rich

6

18

12/3/2006

NULL

Allard

Rich

14

17

12/4/2006

NULL

Allard

Rich

7

16

12/5/2006

NULL

Bass

Gary

8

15

12/1/2006

NULL

Bass

Gary

0

0

12/3/2006

NULL

Bass

Gary

8

21

12/4/2006

NULL

NULL

NULL

NULL

1/18/2007

85

NULL

NULL

NULL

1/9/2007

83.01

This is what it should look like:

NULL

NULL

NULL

1/18/2007

85

NULL

NULL

NULL

1/9/2007

83.01

And if I change the dates to 12/1/2007 and 12/31/2007 the result should be this:

Allard

Rich

0

0

12/1/2006

NULL

Allard

Rich

9

14

12/2/2006

NULL

Allard

Rich

6

18

12/3/2006

NULL

Allard

Rich

14

17

12/4/2006

NULL

Allard

Rich

7

16

12/5/2006

NULL

Bass

Gary

8

15

12/1/2006

NULL

Bass

Gary

0

0

12/3/2006

NULL

Bass

Gary

8

21

12/4/2006

NULL

I know this is probably an easy fix but I can't seem to figure out what I'm doing wrong.

Thanks in advance!

|||

Another thing I just noticed is it is returning :

NULL	NULL	NULL	1/18/2007	85
NULL	NULL	NULL	1/9/2007	83.01

and it should look like this:

Allard, Rich NULL NULL 1/18/2007 85
Allard, Rich NULL NULL 1/9/2007 83.01

|||

Milla:

I used the following data:

insert into QA_DATA values (4, 11, 0, 0, '12/1/2006' )
insert into QA_DATA values (5, 11, 9, 14, '12/2/2006' )
insert into QA_DATA values (6, 1, 2, 3, '1/1/2006' )
insert into QA_DATA values (7, 1, 2, 3, '12/1/2006' )
insert into QA_DATA values (8, 11, 6, 18, '12/3/2006' )
insert into QA_DATA values (9, 11, 14, 17, '12/4/2006' )
insert into QA_DATA values (10, 11, 7, 16, '12/5/2006' )
insert into QA_DATA values (11, 12, 8, 15, '12/1/2006' )
insert into QA_DATA values (12, 12, 0, 0, '12/3/2006' )
insert into QA_DATA values (13, 12, 8, 21, '12/4/2006' )

insert into dbo.QA_SCORES values (1, '1/18/2007', 85, 11 )
insert into dbo.QA_SCORES values (2, '1/9/2007', 83.01, 11 )

truncate table dbo.employee
insert into employee values (1, 'Employee #1')
insert into employee values (11, 'Allard, Rich')
insert into employee values (12, 'Bass, Gary')

with this query:

declare @.startDate datetime
declare @.endDate datetime
--set @.startDate = '12/1/6'
--set @.endDate = '12/31/6'
set @.startDate = '1/1/7'
set @.endDate = '1/31/7'

SELECT emp.[name],
qad.NEW_USERS,
qad.TRANSACTIONS,
CONVERT( VARCHAR, coalesce (qad.DATA_DATE, qas.qa_date), 101)
DATA_DATE,
qas.QA_SCORE
FROM QA_SCORES qas
FULL OUTER JOIN QA_DATA qad
ON qas.EMPLOYEE_ID = qad.EMPLOYEE_ID
AND qad.DATA_DATE >= @.startDate AND qad.DATA_DATE < @.endDate
AND qas.QA_DATE >= @.startDate AND qas.QA_DATE < @.endDate
inner join employee emp
on coalesce (qas.employee_id, qad.employee_id) = emp.employee_id
WHERE coalesce (qad.DATA_DATE, qas.qa_date) >= @.startDate
AND coalesce (qad.DATA_DATE, qas.qa_date) < @.endDate
ORDER BY EMP.[name], coalesce (qad.DATA_DATE, qas.qa_date)

to get these results:

name NEW_USERS TRANSACTIONS DATA_DATE QA_SCORE
-- --
Allard, Rich NULL NULL 01/09/2007 83.01
Allard, Rich NULL NULL 01/18/2007 85.00

name NEW_USERS TRANSACTIONS DATA_DATE QA_SCORE
-- --
Allard, Rich 0 0 12/01/2006 NULL
Allard, Rich 9 14 12/02/2006 NULL
Allard, Rich 6 18 12/03/2006 NULL
Allard, Rich 14 17 12/04/2006 NULL
Allard, Rich 7 16 12/05/2006 NULL
Bass, Gary 8 15 12/01/2006 NULL
Bass, Gary 0 0 12/03/2006 NULL
Bass, Gary 8 21 12/04/2006 NULL
Employee #1 2 3 12/01/2006 NULL

|||

Not sure what I did the first time that made it not work but the last one you entered is working for me.

Thanks Waldrop!

|||

You didn't do anything wrong the first time; my guess was wrong! Remember: I made an assertion that the employee_id was 11 -- and this was not correct. When you gave some more information it became apparent that my guess was not correct and I dropped this part from my version of the query. Glad it worked out!

Dave

Help with aggregate functions

I am new to SQL. I am trying to write a query that will join two tables. The join works fine, however I am getting incorrect results. I would like the joined tables to be grouped by ProposalId for the most current ProposalDate listing the ProposalAmount associated with the most current ProposalDate. I have listed the query, the result I get, and the result I would like to get. Any help or direction you could give me would be greatly appreciated.

The SQL script:

SELECT
p.ProposalId, p.ProjectManagerId, p.JobNumber, p.ClientName,
s.[Proposal Date], s.[Proposal Amount], s.[Times Submitted]
FROM
Proposals p join
(SELECT ProposalId 'ProposalId', ProposalAmount 'Proposal Amount', MAX(ProposalDate) 'Proposal Date', COUNT(ProposalId) 'Times Submitted'

FROM
SubmissionHistory
WHERE
ProjectManagerId = 1
GROUP BY
ProposalAmount, ProposalId) s on p.ProposalId = s.ProposalId

This is the result I get:

Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted

1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 1
2 1 01007-001 SAP2007-07-07 10000.00 1
4 1 01008-001 EAS2007-05-30 75000.00 1
4 1 01008-001 EAS2007-05-07 80000.00 1
4 1 01008-001 EAS2007-05-05 90000.00 1

This is the result I would like to get:

Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted

1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 2
4 1 01008-001 EAS2007-05-30 75000.00 3

Quote:

Originally Posted by yoyo35

I am new to SQL. I am trying to write a query that will join two tables. The join works fine, however I am getting incorrect results. I would like the joined tables to be grouped by ProposalId for the most current ProposalDate listing the ProposalAmount associated with the most current ProposalDate. I have listed the query, the result I get, and the result I would like to get. Any help or direction you could give me would be greatly appreciated.

The SQL script:

SELECT
p.ProposalId, p.ProjectManagerId, p.JobNumber, p.ClientName,
s.[Proposal Date], s.[Proposal Amount], s.[Times Submitted]
FROM
Proposals p join
(SELECT ProposalId 'ProposalId', ProposalAmount 'Proposal Amount', MAX(ProposalDate) 'Proposal Date', COUNT(ProposalId) 'Times Submitted'

FROM
SubmissionHistory
WHERE
ProjectManagerId = 1
GROUP BY
ProposalAmount, ProposalId) s on p.ProposalId = s.ProposalId

This is the result I get:

Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted

1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 1
2 1 01007-001 SAP2007-07-07 10000.00 1
4 1 01008-001 EAS2007-05-30 75000.00 1
4 1 01008-001 EAS2007-05-07 80000.00 1
4 1 01008-001 EAS2007-05-05 90000.00 1

This is the result I would like to get:

Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted

1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 2
4 1 01008-001 EAS2007-05-30 75000.00 3

Your core problem is having the ProposalAmount field in the inner query un-aggregated. Either remove it from there or use an aggregation function. I suggest that you reference to the amount in the outer query by proposal ID. (It is however a question how and if you want to have a single amount mixed with aggregated values (dates, times submitted) in the same line. If not, use aggregation inside.)

If you're really new to SQL this is more than decent even so far...|||Thank you for your reply. I appreciate your time. I will give it a shot and see what happens. Once again, thanks!!!!|||Hi i am not sure but i think you are trying to do a union join,, you should try and put in distinct in yout select statements so distinct(ID) for example

Help with a stored procedure/query?

I did a join on two tables to get the following results. I saved the
results in a #temptable.

idtable2idtable2descripdateinserted
================================================== ==
13descrip111/3/2002
24descrip211/2/2002
33descrip111/4/2001
43descrip110/5/2003
54descrip212/8/2001
65descrip39/10/2002

I want to query that #temptable to get the max date for each table2id
and only return those record. So I need a query to get the follow
results...

idtable2idtable2descripdateinserted
================================================== ==
24descrip211/2/2002
43descrip110/5/2003
65descrip39/10/2002

Question...
What query can I make with #temptable to give me the results?try...

select *
from #tmp t1
where t1.id=(select top 1 id
from #tmp t2
where t2.table2id=t1.table2id
order by dateinserted desc)

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

<newsgroper@.yahoo.com> wrote in message
news:70dd91df.0311191736.109cde98@.posting.google.c om...
> I did a join on two tables to get the following results. I saved the
> results in a #temptable.
>
> id table2id table2descrip dateinserted
> ================================================== ==
> 1 3 descrip1 11/3/2002
> 2 4 descrip2 11/2/2002
> 3 3 descrip1 11/4/2001
> 4 3 descrip1 10/5/2003
> 5 4 descrip2 12/8/2001
> 6 5 descrip3 9/10/2002
> I want to query that #temptable to get the max date for each table2id
> and only return those record. So I need a query to get the follow
> results...
> id table2id table2descrip dateinserted
> ================================================== ==
> 2 4 descrip2 11/2/2002
> 4 3 descrip1 10/5/2003
> 6 5 descrip3 9/10/2002
> Question...
> What query can I make with #temptable to give me the results?

Help with a SQL Query using temp tables

Hi All,

I have 4 temporary tables that hold criteria selected through a report wizard.
I've created a SQL statement and used the four tables in my WHERE/ AND clauses but the results retuned are not being filtered correctly.

Would somebody be kind enough to help me out please.

To briefly summarise, I have created a SQL statement that returns all rows in my recordset, I now need to implement some additional SQL to filter the recordset using my temporary tables, which contain the filters as follows:

(1) Temp table 1 (##tblTempAssetFilt) is mandatory and will always contain at least one row.
(2) Temp table 2 (##tblTempRepairTypeFilter) is optional and may never contain any rows. If this is the case then I have no reason to filter my resultset against this table.
(3) Temp table 3 (##tblTempRepairFilter) / Temp table 4 (##tblTempRepairElementFilter) are both optional, only one of these tables will contain data at one time. Again, as an optional filter the tables may never contain rows, and thus need to be ignored.

I have the following SQL, can somebody tell me how I would go about filtering the recordset using the temporary tables. The creation of the temporary tables occurs at the beginning so will always exist even when no rows have been inserted.

SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
WHERE ActualWork.intAssetID IN (Select intAssetID From ##tblTempAssetFilter) AND Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter)
AND Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter)
AND Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter)

Any filtering must be based on the recordset filtered by temp table 1, which is a mandatory filter. Rows will always exist in this temp table.

Please help, not having much joy with this. Many thanks.Basically all I did was move your manditory temp table out of the where clause and add ORs to the where so that if a table was empty you would evaluate to TRUE for that table. Maybe not the best solution but this should get you going.

SELECT *
FROM tblActualWork [ActualWork]
JOIN tblRepair [Repair] ON ActualWork.intRepairID = Repair.intRepairID
JOIN tblRepairElement [RepairElement] ON Repair.intRepairElementID = RepairElement.intRepairElementID
JOIN tblRepairType [RepairType] ON Repair.intRepairTypeID = RepairType.intRepairTypeID
JOIN tblAsset [Asset] ON ActualWork.intAssetID = Asset.intAssetID
join ##tblTempAssetFilter [TempAssetFilter] on ActualWork.intAssetID = TempAssetFilter.intAssetID
WHERE (Repair.intRepairTypeID IN (Select intRepairTypeID From ##tblTempRepairTypeFilter) or not exists(select * from ##tblTempRepairTypeFilter)
AND (Repair.intRepairID IN (Select intRepairID From ##tblTempRepairFilter) or not exists(select * from ##tblTempRepairFilter)
AND (Repair.intRepairElementID IN (Select intRepairElementID From ##tblTempRepairElementFilter) or not exists(select * from ##tblTempRepairElementFilter)|||Paul,

Thank you for your reply, this has indeed fixed my problem. I am now returning a recordset with the desired results using my temporary tables.

Thanks again.|||Cool!

Here is a twist, if you do not need global temp tables I would switch to using table variables, there are some restrictions to using table variables but they can be much faster.

Also, if you can pre test the emptiness of your tables and store the reslts in a bit variable you can speed things up even more. As it is you are testing for empty temp tables each time you move to a new row.

Help with a simple query please

I have Two Tables, TableA and TableB, both containing a common field,
Feild1.

How do I find all records in TableA, where Field1 is not in TableB?

Regards,
CiarnSELECT A.*
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.col1 = B.col1
WHERE B.col1 IS NULL

--
David Portas
SQL Server MVP
--|||Try

SELECT
A.*

FROM
TableA A
LEFT JOIN TableB B ON
A.Field1 = B.Field1|||Sorry, didn't read it properly. David's answer is correct.

help with a simple query

hi all, i was wondering if there is an alternate way of creating a simple
query that'' get the results below, without using temp tables or declaring
any cursors.
Table:
col1 col2 col3 col4 col5
----
A Bob 2/2/2007 notes pepsi
A Bob 2/2/2007 notes coke
A Bob 2/2/2007 notes sarsi
Result:
col1 col2 col3 col4 col5
----
A Bob 2/2/2007 notes pepsi,coke,sarsi
any suggestions will be appreciated
Thanks a million!No. Use a FAST_FORWARD cursor and table variable/temp table, process
the data on the client side of your code, or use a reporting product
such as Reporting Services or (ack) Crystal to do it.
-Dave
ChiWhiteSox wrote:
> hi all, i was wondering if there is an alternate way of creating a simple
> query that'' get the results below, without using temp tables or declaring
> any cursors.
> Table:
> col1 col2 col3 col4 col5
> ----
> A Bob 2/2/2007 notes pepsi
> A Bob 2/2/2007 notes coke
> A Bob 2/2/2007 notes sarsi
> Result:
> col1 col2 col3 col4 col5
> ----
> A Bob 2/2/2007 notes pepsi,coke,sarsi
> any suggestions will be appreciated
> Thanks a million!|||See: http://www.aspfaq.com/show.asp?id=2529
Razvansql

HELP with a Select/Union statement

I have 3 tables One table is the order Table, Bill to table and ship to table
I have to Views created as followed

This query uses the Ship to table to pull the ship to information to the shipping system.
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO);

This query uss the Bill to as the ship to inforamtion
SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID;

I need this infroamtion in one table which I have done in the UNION statement as followed:
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO)
UNION ALL
SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID;

Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.

NAME ADDR_1 ADDR_2 ADDR_3 CITY STATE ZIPCODE COUNTRY SHIP_VIA ID
DIEBOLD INC (4076A) ATTN: RANCE AARON 343 MANOR DR PACIFICA CA 94044 18932
DIEBOLD, INC OH UPS #88X08X 18932

MY POINT: Is there a way to select a over all DISTINCT order ID.

Thank you for any help hope this make sense!...I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.Define what you mean by "first line". The order of data in a database has no meaning. Do you have a date field or some other value you can sort on to determine which shipping information is "first"?|||Sorry
In the Union query the first select statement will Always be the correct shipping information when the ORDER form has one(Shipping info) if it does not have a Shipping address printed on the form then the second select statement will pull from the bill table which will only have one record per the order number.

Example Order form:
Bill-to Ship to information
Bill to Compnay Ship to company
Bill to address Ship to address
Bill to city Ship to City
Bill to State Ship to state
Etc Etc

When this order occurs then there is one record in the bill to table with that info and 1 record in the Ship to table with the shipping info This is where the problme occurs when I do the UNION it looks in the Shipping table first and pulls the shipping info and then the other statement in the UNION pulls the BILL to info which give me 2 records in the Query so what happends in this case I pull the info using ODBC to my shipping system and it says it found 2 records but I only what the First select statement to show. Another problem is when the ship to is not fill out I need to use the second part of the UNION select statement in order ot pull the bill to info for the shipping info but this works correctly due to only 1 order number exists in the bill to tableEX:

Example Order form:
Bill-to Ship to information
Bill to Compnay SAME
Bill to address
Bill to city
Bill to State
Etc
When this occurs then I need to use the Bill to table for the shipping info.

Hopfully this clears things up.|||So this query:SELECT Cust_address.NAME,
Cust_address.ADDR_1,
Cust_address.ADDR_2,
Cust_address.ADDR_3,
Cust_address.CITY,
Cust_address.STATE,
Cust_address.ZIPCODE,
Cust_address.COUNTRY,
Cust_address.SHIP_VIA,
customer_order.ID
FROM Cust_address
INNER JOIN customer_order
ON Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID
AND Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO...contains all the orders that you need, but in some cases the address information is missing and you want to pull address information from the CUSTOMER table to fill it in?|||Yes Sir that is correct!

Help with a query.

Hello, I have TWO tables.
TABLE1
--
ID ProgramID
01 Program1
02 Program2
03 Program3
04 Program4
...
15 Program15
TABLE2
--
ID Username ProgramID
01 Gary Program1
02 John Program2
03 James Program15
04 Gary Program30
05 Gary Program16
Now, we can see in TABLE2 that Gary has joined Program1, Program16 and
Program30, but he has not joined any of the others. How can I return a list
of all the programs GARY has NOT joined? I basically need to return ALL
rows from Table1, WHERE Username <> Gary in Table2.
Not sure if I should be using Subqueries our Joins.
Any help appreciated!!
Gary.SELECT t1.ProgramID
FROM Table1 t1
LEFT OUTER JOIN table2 t2
ON t1.ProgramID = t2.ProgramID
AND t2.UserName = 'Gary'
WHERE t2.ProgramID IS NULL
Jacco Schalkwijk
SQL Server MVP
"G" <G@.G.COM> wrote in message
news:Uotge.30046$G8.6851@.text.news.blueyonder.co.uk...
> Hello, I have TWO tables.
> TABLE1
> --
> ID ProgramID
> 01 Program1
> 02 Program2
> 03 Program3
> 04 Program4
> ...
> 15 Program15
>
> TABLE2
> --
> ID Username ProgramID
> 01 Gary Program1
> 02 John Program2
> 03 James Program15
> 04 Gary Program30
> 05 Gary Program16
>
> Now, we can see in TABLE2 that Gary has joined Program1, Program16 and
> Program30, but he has not joined any of the others. How can I return a
> list
> of all the programs GARY has NOT joined? I basically need to return ALL
> rows from Table1, WHERE Username <> Gary in Table2.
> Not sure if I should be using Subqueries our Joins.
> Any help appreciated!!
> Gary.
>
>|||Hi
You can wriite the query this way:
SELECT * FROM TABLE1 WHERE TABLE1.ID
NOT IN ( SELECT TABLE2.ID FROM TABLE2 WHERE Username = 'Gary')
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"G" wrote:

> Hello, I have TWO tables.
> TABLE1
> --
> ID ProgramID
> 01 Program1
> 02 Program2
> 03 Program3
> 04 Program4
> ...
> 15 Program15
>
> TABLE2
> --
> ID Username ProgramID
> 01 Gary Program1
> 02 John Program2
> 03 James Program15
> 04 Gary Program30
> 05 Gary Program16
>
> Now, we can see in TABLE2 that Gary has joined Program1, Program16 and
> Program30, but he has not joined any of the others. How can I return a li
st
> of all the programs GARY has NOT joined? I basically need to return ALL
> rows from Table1, WHERE Username <> Gary in Table2.
> Not sure if I should be using Subqueries our Joins.
> Any help appreciated!!
> Gary.
>
>