Query problem using groups

Hi
Is there any way to run a query on a pair of related tables with a one
to many relationship such that for each parent record you include the
details of just one child
e.g. Employees contains a foreign key to the identity of Departments
I want to select the department name and the first employee in the
database associated with that department
The query below would select all employees for all departments
SELECT deptname, surname, firstname from Departments inner join
Employees on Department.DeptId = Employee.DebtId
I need to select just the first employee.
Any ideas?
Thanks
Damien
How do you define which is the "first" emloyee? (Longest serving maybe? Or
most senior?) Tables have no inherent order so you have to define "first"
based on the information in your data. You could use the date they started:
SELECT deptname, surname, firstname
FROM Departments AS D
INNER JOIN Employees AS E
ON D.deptid = E.deptid
AND E.date_started =
(SELECT MIN(date_started)
FROM Employees
WHERE deptid = D.deptid)
but that may still give multiple rows if more than one person started on the
same day. Maybe you just want ANY one employee but don't really care which.
In that case just use the primary key of the Employees table in place of the
date in this query (assuning you have a single-column key).
Hope this helps.
David Portas
SQL Server MVP
|||Yes that solves it for me, thank you.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Query problem using groups

Hi
Is there any way to run a query on a pair of related tables with a one
to many relationship such that for each parent record you include the
details of just one child
e.g. Employees contains a foreign key to the identity of Departments
I want to select the department name and the first employee in the
database associated with that department
The query below would select all employees for all departments
SELECT deptname, surname, firstname from Departments inner join
Employees on Department.DeptId = Employee.DebtId
I need to select just the first employee.
Any ideas?
Thanks
DamienHow do you define which is the "first" emloyee? (Longest serving maybe? Or
most senior?) Tables have no inherent order so you have to define "first"
based on the information in your data. You could use the date they started:
SELECT deptname, surname, firstname
FROM Departments AS D
INNER JOIN Employees AS E
ON D.deptid = E.deptid
AND E.date_started = (SELECT MIN(date_started)
FROM Employees
WHERE deptid = D.deptid)
but that may still give multiple rows if more than one person started on the
same day. Maybe you just want ANY one employee but don't really care which.
In that case just use the primary key of the Employees table in place of the
date in this query (assuning you have a single-column key).
Hope this helps.
--
David Portas
SQL Server MVP
--

Query Performance Question

I have a query that takes approximately 2 mins to run. The query is derived from two tables that have a one to many relationship. When I comment out 4 of the fields in the 'select' portion of the query, it speeds up to 18 seconds. I know that I am doin
g a bookmark lookup (this is 60% of the query time) but I am not sure how to avoid this. Any ideas?
Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?

Query Performance Question

I have a query that takes approximately 2 mins to run. The query is derived from two tables that have a one to many relationship. When I comment out 4 of the fields in the 'select' portion of the query, it speeds up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of the query time) but I am not sure how to avoid this. Any ideas?Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?

Query Performance Question

I have a query that takes approximately 2 mins to run. The query is derived
from two tables that have a one to many relationship. When I comment out 4
of the fields in the 'select' portion of the query, it speeds up to 18 seco
nds. I know that I am doin
g a bookmark lookup (this is 60% of the query time) but I am not sure how to
avoid this. Any ideas?Hi,
The covering index is only effective when the query
does a Bookmark lookup. if you have covering index the query will not hit
the source table for data. but , the disadvantage is that a covering index
increases the
number of writes when one or more of the columns in the index is updated.
This might be a issue durng insert./update and delete.
Thanks
Hari
MCDBA
"Daniel Avsec" <Daniel Avsec@.discussions.microsoft.com> wrote in message
news:1611776E-29E8-4F46-BB8F-97A8E6A32C45@.microsoft.com...
> I have a query that takes approximately 2 mins to run. The query is
derived from two tables that have a one to many relationship. When I
comment out 4 of the fields in the 'select' portion of the query, it speeds
up to 18 seconds. I know that I am doing a bookmark lookup (this is 60% of
the query time) but I am not sure how to avoid this. Any ideas?sql

Query over many to many relationship

I have 2 tables with many to many relatioship

tblTitle and tblStyle

I have setup a connecting table tblTitleStyle which contains 2 forein keys TitleID and StyleID to connect the 2 tables.

I can query 2 tables with one to many relatioship using JOINT statement. How do I implement the JOINT statement across tblTitleStyle though? Or is it a wrong aproach altogether, should I use maybe a subquery?

Thanks, Jakub

Does the code below help you out?

Chris

SELECT <insert column names>

FROM dbo.tblTitleStyle ts

INNER JOIN dbo.tblTitle t ON t.TitleID = ts.TitleID

INNER JOIN dbo.tblStyle s ON s.StyleID = ts.StyleID

|||

Wow...that was quick and shure works like a charm!..greatly appreciated.

Now I have in adition 2 more tables (names are slightly different...I am in the process of changing them)

Composer and Category

Here is the complete DB scenario:

Title

with many to one relationship to

Composer

Category

and many to many relationship to

Genre (Style before)

I can use one query to SELECT data from Title, Composer and Category:

SELECT t.Title, c.Category, co.Composer

FROM Title t

INNER JOIN Composer co ON t.Index_Composer = co.Index_Composer

INNER JOIN Category c ON t.Index_Category = c.Index_Category

and one query (thanks to you) from the Genre table:

SELECT t.Title, g.Genre
FROM dbo.TitleGenre tg
INNER JOIN dbo.Title t ON t.Index_Title = tg.Index_Title
INNER JOIN dbo.Genre g ON g.Index_Genre = tg.Index_Genre

Can I combine those 2 queries into one to get the result in one form? The problem is that FROM calls for different tables in those 2 queries.

Thanks again...Jakub

I

|||

This should do the trick....

Chris

SELECT t.Title, c.Category, co.Composer, g.genre

FROM Title t

INNER JOIN Composer co ON t.Index_Composer = co.Index_Composer

INNER JOIN Category c ON t.Index_Category = c.Index_Category

INNER JOIN dbo.TitleGenre tg ON tg.TitleID = t.TitleID

INNER JOIN dbo.Genre g ON g.Index_Genre = tg.Index_Genre

|||

It did, thanks... the query builder in VS web dev produced this convoluted code which didn't work.

Check out www.concertantechamber players.com in few days to see the results...