3 triggers into one

Hi,
I want to synchronize some records in one destination table in a
SQL server from some records in 3 source tables in another SQL server.
Instead of building 3 triggers for each source table, how can I make one
trigger and work for 3? Thanks.
supernova,
You need three triggers, one on each source table. I'm not entirely sure
what you're trying to do, but if you're just moving data, then consider
DTS or an INSERT..SELECT statement.
What are you trying to do exactly? Three triggers might not be appropriate.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
supernova wrote:
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>
|||Hi
I'm afraid you cannot do that or perhaps I did not understand you.
A trigger fires per UPDATE/DELETE/INSERT statement.
If you have a trigger on tableA for INSERT for example and the user did
perform INSERT statement into this table so you don't have a problem to
insert the new rows into another table by server.database.dbo.table.But you
said that you have three tables so how do you want to identify what's rows
to be inserted into a destination server if let me say an INSERT was
performed only on one table?
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>
|||I read your question a little differently from the others, so my answer
differs also, but only slightly..
You would have to have a trigger of EACH of the three tables, but you do
not have to have 3 triggers on each table... You may consolidate the
insert,update, delete triggers for a single table into a single trigger..
A futher way to consolidate is to put common code for the 3 tables into a
stored procedure and have each of the 3 table's triggers call the stored
procedure... Since you can't pass the inserted, deleted tables, you'll have
to pass each row to the sp ( which is a pain.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>
|||To clarify my question, I want to build something like a view, a combination
fields from 3 tables, and a trigger based on the view for insert and update.
How can I implement this? Thanks.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OHt18di0EHA.1420@.TK2MSFTNGP10.phx.gbl...
>I read your question a little differently from the others, so my answer
> differs also, but only slightly..
> You would have to have a trigger of EACH of the three tables, but you do
> not have to have 3 triggers on each table... You may consolidate the
> insert,update, delete triggers for a single table into a single trigger..
> A futher way to consolidate is to put common code for the 3 tables into a
> stored procedure and have each of the 3 table's triggers call the stored
> procedure... Since you can't pass the inserted, deleted tables, you'll
> have
> to pass each row to the sp ( which is a pain.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "supernova" <abc@.yahoo.com> wrote in message
> news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
>

3 triggers into one

Hi,
I want to synchronize some records in one destination table in a
SQL server from some records in 3 source tables in another SQL server.
Instead of building 3 triggers for each source table, how can I make one
trigger and work for 3? Thanks.supernova,
You need three triggers, one on each source table. I'm not entirely sure
what you're trying to do, but if you're just moving data, then consider
DTS or an INSERT..SELECT statement.
What are you trying to do exactly? Three triggers might not be appropriate.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
supernova wrote:
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||Hi
I'm afraid you cannot do that or perhaps I did not understand you.
A trigger fires per UPDATE/DELETE/INSERT statement.
If you have a trigger on tableA for INSERT for example and the user did
perform INSERT statement into this table so you don't have a problem to
insert the new rows into another table by server.database.dbo.table.But you
said that you have three tables so how do you want to identify what's rows
to be inserted into a destination server if let me say an INSERT was
performed only on one table?
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||I read your question a little differently from the others, so my answer
differs also, but only slightly..
You would have to have a trigger of EACH of the three tables, but you do
not have to have 3 triggers on each table... You may consolidate the
insert,update, delete triggers for a single table into a single trigger..
A futher way to consolidate is to put common code for the 3 tables into a
stored procedure and have each of the 3 table's triggers call the stored
procedure... Since you can't pass the inserted, deleted tables, you'll have
to pass each row to the sp ( which is a pain.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||To clarify my question, I want to build something like a view, a combination
fields from 3 tables, and a trigger based on the view for insert and update.
How can I implement this? Thanks.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OHt18di0EHA.1420@.TK2MSFTNGP10.phx.gbl...
>I read your question a little differently from the others, so my answer
> differs also, but only slightly..
> You would have to have a trigger of EACH of the three tables, but you do
> not have to have 3 triggers on each table... You may consolidate the
> insert,update, delete triggers for a single table into a single trigger..
> A futher way to consolidate is to put common code for the 3 tables into a
> stored procedure and have each of the 3 table's triggers call the stored
> procedure... Since you can't pass the inserted, deleted tables, you'll
> have
> to pass each row to the sp ( which is a pain.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "supernova" <abc@.yahoo.com> wrote in message
> news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
>

3 triggers into one

Hi,
I want to synchronize some records in one destination table in a
SQL server from some records in 3 source tables in another SQL server.
Instead of building 3 triggers for each source table, how can I make one
trigger and work for 3? Thanks.supernova,
You need three triggers, one on each source table. I'm not entirely sure
what you're trying to do, but if you're just moving data, then consider
DTS or an INSERT..SELECT statement.
What are you trying to do exactly? Three triggers might not be appropriate.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
supernova wrote:
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||Hi
I'm afraid you cannot do that or perhaps I did not understand you.
A trigger fires per UPDATE/DELETE/INSERT statement.
If you have a trigger on tableA for INSERT for example and the user did
perform INSERT statement into this table so you don't have a problem to
insert the new rows into another table by server.database.dbo.table.But you
said that you have three tables so how do you want to identify what's rows
to be inserted into a destination server if let me say an INSERT was
performed only on one table?
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||I read your question a little differently from the others, so my answer
differs also, but only slightly..
You would have to have a trigger of EACH of the three tables, but you do
not have to have 3 triggers on each table... You may consolidate the
insert,update, delete triggers for a single table into a single trigger..
A futher way to consolidate is to put common code for the 3 tables into a
stored procedure and have each of the 3 table's triggers call the stored
procedure... Since you can't pass the inserted, deleted tables, you'll have
to pass each row to the sp ( which is a pain.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"supernova" <abc@.yahoo.com> wrote in message
news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I want to synchronize some records in one destination table in a
> SQL server from some records in 3 source tables in another SQL server.
> Instead of building 3 triggers for each source table, how can I make one
> trigger and work for 3? Thanks.
>|||To clarify my question, I want to build something like a view, a combination
fields from 3 tables, and a trigger based on the view for insert and update.
How can I implement this? Thanks.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OHt18di0EHA.1420@.TK2MSFTNGP10.phx.gbl...
>I read your question a little differently from the others, so my answer
> differs also, but only slightly..
> You would have to have a trigger of EACH of the three tables, but you do
> not have to have 3 triggers on each table... You may consolidate the
> insert,update, delete triggers for a single table into a single trigger..
> A futher way to consolidate is to put common code for the 3 tables into a
> stored procedure and have each of the 3 table's triggers call the stored
> procedure... Since you can't pass the inserted, deleted tables, you'll
> have
> to pass each row to the sp ( which is a pain.)
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "supernova" <abc@.yahoo.com> wrote in message
> news:%23UOibIh0EHA.424@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I want to synchronize some records in one destination table in
>> a
>> SQL server from some records in 3 source tables in another SQL server.
>> Instead of building 3 triggers for each source table, how can I make one
>> trigger and work for 3? Thanks.
>>
>

3 tables wildcard searches

Hi,

I am writing a stored procedure to pull records from a table of personal data related to jobs applied for.

There are 3 tables involved, the jobs, the applicants and the applications.

I need to search on job title, job ref from the jobs table and on forename, surname and applicant ID from the applicants table.

There are some quirks here, if the user enters an applicant ID then we can simply scan the jobs table for that id where it also matches the job title wildcards, so that is quite easy to manage.

My own idea for the more complicated searches was to gather the unique ID's from the jobs table into a var, then do similar with the applicants and then search the applications table where both these ID's matched? I think that wouldn't work so well if using the 'WHERE IN()' clause for the main query?

So what approach would be best here to perform the second part of the SP if the client hasn't passed an ApplicantID?

Obviously the applications table has both JobID and ClientID's to relate back to the applicants table.

So can anyone help here, it seems a fairly complicated statement or set of statements would be required here.

Thanks in advance.Can you post the URL for this assignment, or at least scan in the page(s) you got in class if they aren't available online?

-PatP|||If I understand the question correctly, something like this might work:

create proc usp_search(@.searchstring varchar(100))
as
select * -- replace with required cols
from applicants
join applications on applicants.applicantid = applications.applicantid
join jobs on applications.jobid = jobs.jobid
where forename like @.searchstring + '%' -- Auto Wild Card (Begins With)
or surname like @.searchstring + '%'
or jobtitle like @.searchstring + '%'
or jobref = @.searchstring
or applicantid = @.searchstring

3 tables to join

hi,
I knew how to join 2 tables but i have a process to select 3 tables. I have a sample table and field below. I want to join Parts & Orders using field Prt_no and Supplier & Parts using field Sup _code

Parts table Orders Table Supplier Table
Prt_no Prt_no Sup_code
Prt_name Oh_qt Sup_name
Re_Level Or_no
Pri_amnt
Sup_code

Thanks...SELECT
p.Prt_no,
o.Prt_no,
s.Sup_code,
p.Sup_code
FROM
Parts p
INNER JOIN Orders o ON p.Prt_no = o.Prt_no
INNER JOIN Supplier s ON p.Sup_code = s.Sup_code

--I would invest in a good TSQL book if I were you. Teach Yourself Transact-SQL in 21 Days by Sam's Publishing is really good for beginners.|||Where I can get that book?

3 tables query

I hable that has a ProOnwerID and ProFinOwner, when I create the following
view I get NULL in my ProOwner and FinOwner.. If I delte de Fin Owner the
ProOwer shows up.. Any Idea why ?... is it because I have two ID for
Clients en same table ?
SELECT dbo.projects.*, dbo.employee.EmpName AS EmpName,
dbo.Clients.ClientName AS ProOwner, dbo.Clients.ClientName AS FinOwner
FROM dbo.projects LEFT OUTER JOIN
dbo.employee ON dbo.projects.ProPMID =
dbo.employee.EmpUserID LEFT OUTER JOIN
dbo.Clients ON dbo.projects.ProFinOwner =
dbo.Clients.ClientId AND dbo.projects.ProOwner = dbo.Clients.ClientId
thanksOn Tue, 8 Feb 2005 15:55:20 -0500, Carlos wrote:

>I hable that has a ProOnwerID and ProFinOwner, when I create the following
>view I get NULL in my ProOwner and FinOwner.. If I delte de Fin Owner the
>ProOwer shows up.. Any Idea why ?... is it because I have two ID for
>Clients en same table ?
Hi Carlos,
Your query tries to find ONE row in Clients that is equal to both the
ProFinOwner and the ProOwner. This will only succeed if ProOwner and
ProFinOwner are the same. If they are not, you'll get NULL (due to the
left join - with inner join, you'd not have gotten any rows at all).
I'm actually quite surprised that you did see the ProOwner when you
"deleted FinOwner" - but maybe I'm just misunderstanding what you actually
did.
Anyway, to show the name of the two owners, even if they are not the same,
you'll have to join in the client table twice:
SELECT p.Col01, p.Col02, ..., -- Better not to use SELECT *
e.EmpName AS EmpName,
o.ClientName AS ProOwner,
po.ClientName AS FinOwner
FROM dbo.projects AS p
LEFT OUTER JOIN dbo.employee AS e
ON p.ProPMID = e.EmpUserID
LEFT OUTER JOIN dbo.Clients AS po
ON p.ProOwner = po.ClientId
LEFT OUTER JOIN dbo.Clients AS fo
ON p.ProFinOwner = fo.ClientId
If I were you, I'd also check if you really need all these joins to be
outer joins. Inner joins are usually faster.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo that did it !!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:j8ai01lfqn0j5c0l8tig70ne1tu1dqg576@.
4ax.com...
> On Tue, 8 Feb 2005 15:55:20 -0500, Carlos wrote:
>
> Hi Carlos,
> Your query tries to find ONE row in Clients that is equal to both the
> ProFinOwner and the ProOwner. This will only succeed if ProOwner and
> ProFinOwner are the same. If they are not, you'll get NULL (due to the
> left join - with inner join, you'd not have gotten any rows at all).
> I'm actually quite surprised that you did see the ProOwner when you
> "deleted FinOwner" - but maybe I'm just misunderstanding what you actually
> did.
> Anyway, to show the name of the two owners, even if they are not the same,
> you'll have to join in the client table twice:
> SELECT p.Col01, p.Col02, ..., -- Better not to use SELECT *
> e.EmpName AS EmpName,
> o.ClientName AS ProOwner,
> po.ClientName AS FinOwner
> FROM dbo.projects AS p
> LEFT OUTER JOIN dbo.employee AS e
> ON p.ProPMID = e.EmpUserID
> LEFT OUTER JOIN dbo.Clients AS po
> ON p.ProOwner = po.ClientId
> LEFT OUTER JOIN dbo.Clients AS fo
> ON p.ProFinOwner = fo.ClientId
> If I were you, I'd also check if you really need all these joins to be
> outer joins. Inner joins are usually faster.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

3 tables on one report

Dear all
I want to display three tables on one report. This works fine as long I
place them on the same horizontal line (3 columns). If I use 2 columns (first
column table 1, second column tables 2 and 3) I have the problem, that table
3 starts where table 1 ends. Because table 1 is longer as table 2 I have a
lot of empty space between table 2 and 3.
How can I change this?
Thanks for your answer,
MarcWell, I have a workaround: I use a subreport.
Regards, Marc|||Marc,
I am not sure what you are trying to do, but I have several reports that
need multiple tables on one report. I have had to put them within nested
list boxes at times to get a particular order of the tables to appear (since
otherwise then tend to follow one after the other and I wanted them
interlaced) - In other cases I have used rectangles to enforce placement
within a page. There are other ways to handle without a subreport. If you
give a better idea of what you want then maybe I can suggest something.
Thanks
"Marc" wrote:
> Well, I have a workaround: I use a subreport.
> Regards, Marc|||Hi MJT
Thanks for your answer. Well I'm quite new to SQL Server 2005 Reporting
Services so I struggled with placing tables on a report and arranging them as
the requirement says. Like you proposed I useed the rectangle - this works
very well! What I did not understand is the List component. When I place
several tables inside a List, this component requires a dataset. So I guess
the List is used for other purposes.
Best regards,
Marc|||Hi Marc,
A list data region repeats with each group or row in the dataset. A list
can be used for free-form reports or in conjunction with other data regions.
You could refer the following article in the SQL Books Online:
Working with List Data Regions
http://msdn2.microsoft.com/en-us/library/ms159121(d=ide).aspx
Hope this will be helpful to you to understand the List Control. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

3 tables join

I have have 3 tables TableA, TableB and TableC. TableA holds the keys
to TableB and TableC. I need a query which will display the details
from TableB and TableC depending on the key in TableA.

For eg.

TableA - columns {id, relatedkey, recordType} ===recordType will hold
values like TableB or TableC
TableB - columns{id, column1}
TableC - columns{id, column1}

the query should match the related key to the id of TableA or table B
based on recordType and show the column1 value with the TabelA id so
output for this should be

id recordType column1

1 TableB value of TableB column1
2 TableC value of TableC column1

Please help.

Cheers
NickOn Nov 8, 3:29 pm, Nick <nachiket.shirwal...@.gmail.comwrote:

Quote:

Originally Posted by

I have have 3 tables TableA, TableB and TableC. TableA holds the keys
to TableB and TableC. I need a query which will display the details
from TableB and TableC depending on the key in TableA.
>
For eg.
>
TableA - columns {id, relatedkey, recordType} ===recordType will hold
values like TableB or TableC
TableB - columns{id, column1}
TableC - columns{id, column1}
>
the query should match the related key to the id of TableA or table B
based on recordType and show the column1 value with the TabelA id so
output for this should be
>
id recordType column1
>
1 TableB value of TableB column1
2 TableC value of TableC column1
>
Please help.
>
Cheers
Nick

Hi Nick,

Try:
SELECT a.id, a.recordType, CASE WHEN b.column1 IS NULL THEN c.column1
ELSE b.column1 END AS column1
FROM TableA a
LEFT OUTER JOIN TableB b
ON b.id = a.relatedkey
AND a.recordType = 'TableB'
LEFT OUTER JOIN TableC c
ON c.id = a.relatedkey
AND a.recordType = 'TableC'

Good luck!
J|||On Thu, 08 Nov 2007 07:29:06 -0800, Nick wrote:

Quote:

Originally Posted by

>I have have 3 tables TableA, TableB and TableC. TableA holds the keys
>to TableB and TableC. I need a query which will display the details
>from TableB and TableC depending on the key in TableA.
>
>For eg.
>
>TableA - columns {id, relatedkey, recordType} ===recordType will hold
>values like TableB or TableC
>TableB - columns{id, column1}
>TableC - columns{id, column1}
>
>the query should match the related key to the id of TableA or table B
>based on recordType and show the column1 value with the TabelA id so
>output for this should be
>
>
>id recordType column1
>
>1 TableB value of TableB column1
>2 TableC value of TableC column1
>
>Please help.

Hi Nick,

The solution jhofmeyr posted will work for you. But I think you should
question your design. If TableB and TableC are actually the same thing,
they should be a single table. And if they are different things, then
TableA should have two referencing columns plus a CHECK constraint to
ensure that mey not both be NOT NULL.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

3 tables ...

Hey
I have 3 tables :
Jobs table
jobID
jobname
JobQuestion
id
jobID
questionID
points
Question
questionID
questionText
I'd like to join this tables so that I have all names of jobs and points for
given question. It doesn't matter if the question has it's row in
jobQuestion table. I tried to use left / right joins but when I use WHERE
QuestionID = xxx I got only row where they exists in jobsQuestions Table.
Now it looks like this:
Select Job.Name, JobQuestion.Points
FROM Job Left Join Job.JobId = JobQuestion.JobId
WHERE JobQuestion.QuestionID = @.someParam
I don't use in this join 3 table but maybe I should but I don't know how to
join them so they work as I wrote above.
JarodCould you please so kind to provide us DDL for a better comprehension?
Thanks,
"Jarod" wrote:

> Hey
> I have 3 tables :
> Jobs table
> jobID
> jobname
> JobQuestion
> id
> jobID
> questionID
> points
> Question
> questionID
> questionText
> I'd like to join this tables so that I have all names of jobs and points f
or
> given question. It doesn't matter if the question has it's row in
> jobQuestion table. I tried to use left / right joins but when I use WHERE
> QuestionID = xxx I got only row where they exists in jobsQuestions Table.
> Now it looks like this:
> Select Job.Name, JobQuestion.Points
> FROM Job Left Join Job.JobId = JobQuestion.JobId
> WHERE JobQuestion.QuestionID = @.someParam
> I don't use in this join 3 table but maybe I should but I don't know how t
o
> join them so they work as I wrote above.
> Jarod
>|||> Could you please so kind to provide us DDL for a better comprehension?
> Thanks,
>
I wrote this in that way because the tables are a bit more complicated and
field names aren't written in English so let's stay with this form.
Jarod|||Is this what you're after...
select * from Question a
LEFT JOIN JobQuestion b on a.QuestionID = b.QuestionID
LEFT JOIN Jobs c on b.JobID = c.JobID
WHERE a.QuestionID = 1
HTH. Ryan
"Jarod" <blueice@.NOSPAM.gazeta.pl> wrote in message
news:%2308KwhRHGHA.2912@.tk2msftngp13.phx.gbl...
> I wrote this in that way because the tables are a bit more complicated and
> field names aren't written in English so let's stay with this form.
> Jarod|||> select * from Question a
> LEFT JOIN JobQuestion b on a.QuestionID = b.QuestionID
> LEFT JOIN Jobs c on b.JobID = c.JobID
> WHERE a.QuestionID = 1
>
And the problem is when I do somethink like this I got only one row which
exist in JobQuestion table. And what I wanted is all rows from Job table and
values for them from jobQuestion table. To be more precise Names of the Job
and Points ;)
Jarod|||"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uQaBhmRHGHA.2836@.TK2MSFTNGP14.phx.gbl...
> Is this what you're after...
> select * from Question a
> LEFT JOIN JobQuestion b on a.QuestionID = b.QuestionID
> LEFT JOIN Jobs c on b.JobID = c.JobID
> WHERE a.QuestionID = 1
OR a.Question IS NULL
When I added OR a.Question IS NULL it works ;)
Jarod|||Better move the a.Question = 1 to the join:
...
LEFT JOIN JobQuestion b on (a.QuestionID = b.QuestionID) and (a.Question = 1
)
...
There was a discussion on this issue a few days ago:
http://msdn.microsoft.com/newsgroup...2430&sloc=en-us
I believe you should be aware of potential issues.
ML
http://milambda.blogspot.com/

3 table query help

i have 3 tables member_info, subscription_info, exclude
member_info has 3 columns (login, fname, lname)
subscription_info has 5 columns
(login, subid, monthlypayment, startdate, enddate)
exclude has only one column (login)
I want to write a query that would return:
login, fname, lname, subid, monthlypayment
where login is not on of the logins from exclude table..
I tried this query:
select login, fname, lname, subid, monthlypayment
from member_info, subscription_info, exclude
where member_info.login=subscription_info.login and
member_info.login <> exclude.login
but that yeilds duplicate records, please helptry:
select m.login, m.fname, m.lname, s.subid, s.monthlypayment
from member_info m
inner join subscription_info s ON m.login = s.login
left outer join exclude e ON e.login = m.login
where e.login IS NULL
>--Original Message--
>i have 3 tables member_info, subscription_info, exclude
>member_info has 3 columns (login, fname, lname)
>subscription_info has 5 columns
>(login, subid, monthlypayment, startdate, enddate)
>exclude has only one column (login)
>I want to write a query that would return:
>login, fname, lname, subid, monthlypayment
>where login is not on of the logins from exclude table..
>I tried this query:
>select login, fname, lname, subid, monthlypayment
>from member_info, subscription_info, exclude
>where member_info.login=subscription_info.login and
>member_info.login <> exclude.login
>but that yeilds duplicate records, please help
>.
>

3 table left outer join

I am joining 3 tables. 1st table I want all rows; I also want all rows in table 1 not in table 2 or in Table 2 but have a blank journal id and all rows in table 1 not in table 3. HELP!

SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
FROM PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
AND D.INVOICE = A.INVOICE
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.INVOICE = C.ITEM
WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
AND B.JOURNAL_ID = ' 'could you post your query again, there seems to be a typo in it

AND A.INVOICE = C.ITEM
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B

the PS_BI_HDR D is wrong where it is

rudy|||SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM)
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
AND D.INVOICE = A.INVOICE
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.INVOICE = C.ITEM
WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
AND B.JOURNAL_ID = ' '

This is the query. Note: ITEM and INVOICE are on in the same on two different tables. I forgot the ().|||[QUOTE][SIZE=1]Originally posted by AMYC
SELECT A.BUSINESS_UNIT, A.INVOICE, A.BILL_STATUS, A.INVOICE_AMOUNT, A.ACCOUNTING_DT, B.JOURNAL_ID, B.JOURNAL_DATE, B.ACCOUNT,
B.MONETARY_AMOUNT, C.GROUP_ID, C.POST_DT, C.ENTRY_AMT
FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM)
PS_BI_HDR D LEFT OUTER JOIN PS_BI_ACCT_ENTRY B
ON D.BUSINESS_UNIT = B.BUSINESS_UNIT
AND D.INVOICE = B.INVOICE
WHERE A.BILL_STATUS = 'INV' (I REALIZE THIS IS MY PROBLEM)
AND B.JOURNAL_ID = ' '

Update; found one error ;)|||i still see a syntax error in the following snippet of code --

FROM (PS_BI_HDR A LEFT OUTER JOIN PS_PENDING_ITEM_FS C
ON A.BUSINESS_UNIT = C.GROUP_BU
AND A.INVOICE = C.ITEM)
PS_BI_HDR D LEFT OUTER JOIN ...

this is structurally the same as

FROM (derived table) foo bar LEFT OUTER JOIN ...

you can give the derived table one alias name but not two

rudy|||Thanks for your help but I feel like I have bigger issues than syntax errors. It's more structural. This is a new approach I am trying but I still get rows I should not ie rows from BI_ACCT_ENTRY that should not be returned. THanks!

SELECT A.BUSINESS_UNIT, A.INVOICE
FROM PS_BI_HDR A WHERE (NOT EXISTS (SELECT B.GROUP_BU, B.ITEM FROM PS_PENDING_ITEM_FS B
WHERE A.BUSINESS_UNIT = B.GROUP_BU AND A.INVOICE = B.ITEM)
AND NOT EXISTS(SELECT BUSINESS_UNIT, INVOICE FROM PS_BI_ACCT_ENTRY C
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.INVOICE = C.INVOICE))
OR EXISTS (SELECT BUSINESS_UNIT, INVOICE FROM PS_BI_ACCT_ENTRY C
WHERE JOURNAL_ID = ' ')|||i don't mean to criticize but it will help you if you get in the habit of formatting your code like this:

SELECT A.BUSINESS_UNIT
, A.INVOICE
FROM PS_BI_HDR A
WHERE (
NOT EXISTS
( SELECT B.GROUP_BU
, B.ITEM
FROM PS_PENDING_ITEM_FS B
WHERE A.BUSINESS_UNIT = B.GROUP_BU
AND A.INVOICE = B.ITEM )
AND NOT EXISTS
( SELECT BUSINESS_UNIT
, INVOICE
FROM PS_BI_ACCT_ENTRY C
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.INVOICE = C.INVOICE )
)
OR EXISTS
( SELECT BUSINESS_UNIT
, INVOICE
FROM PS_BI_ACCT_ENTRY C
WHERE JOURNAL_ID = ' ')

now you can plainly see that you will get PS_BI_HDR records if any PS_BI_ACCT_ENTRY entries exist with a blank JOURNAL_ID

was that your problem?

by the way, i would not alias the table in the 3rd subquery with C because you've already used that in the 2nd subquery -- not that it causes an error, but you never know...

rudy|||I do format my code but just copying and pasting here as it is a total pain to format it on here :) anyway, I fixed it. Thanks!

SELECT A.BUSINESS_UNIT, A.INVOICE
FROM PS_BI_HDR A WHERE
EXISTS (SELECT C.BUSINESS_UNIT, C.INVOICE FROM
PS_BI_ACCT_ENTRY C
WHERE JOURNAL_ID = ' '
AND NOT EXISTS (SELECT B.GROUP_BU, B.ITEM FROM
PS_PENDING_ITEM_FS B
WHERE A.BUSINESS_UNIT = B.GROUP_BU
AND A.INVOICE = B.ITEM)
OR NOT EXISTS(SELECT BUSINESS_UNIT,
INVOICE FROM PS_BI_ACCT_ENTRY C
WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT
AND A.INVOICE = C.INVOICE)
ORDER BY A.INVOICE|||it is a total pain to format it on here
not really

just wrap it in code tags -- i typically type them, but you can also use the number/hash sign # button (between the IMG and PHP buttons when you create a post)|||I didn't realize I could do that. Thanks!!sql

3 Table join question

3 Table join question.
Here are 3 tables.
T1
--
ID VAL
A 0
B 0
C 0
D 0
E 0
T2
--
ID METER
A 1
X 1
B 1
Y 1
T3
--
ID NUM
B 2
H 2
R 2
J 2
K 2
X 2
E 2
I was reading a tutorial that said you can join the three tables in these
ways:
T1 joins T2 which joins T3
T1 joins T2 and the Join of T1 and T2 joins T3
T1 joins T2 and T1 joins T3
The tutorial doesn't give any examples.
Which SQL statements would represent these three types of multiple table
joins?
GeoffGeoff,
T1 JOIN T2
ON T1.ID = T2.ID
JOIN T3
ON T2.ID = T3.ID
However note that the order in which the tables are joined will be
determined by SQL Server not the order in which the tables are listed in the
FROM clause unless the the SET FORCEPLAN statement is used.
HTH
J
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:3FA2D589-6EBF-414E-A4F9-FA37E41C0102@.microsoft.com...
>3 Table join question.
> Here are 3 tables.
>
> T1
> --
> ID VAL
> A 0
> B 0
> C 0
> D 0
> E 0
>
> T2
> --
> ID METER
> A 1
> X 1
> B 1
> Y 1
>
> T3
> --
> ID NUM
> B 2
> H 2
> R 2
> J 2
> K 2
> X 2
> E 2
>
> I was reading a tutorial that said you can join the three tables in these
> ways:
> T1 joins T2 which joins T3
> T1 joins T2 and the Join of T1 and T2 joins T3
> T1 joins T2 and T1 joins T3
> The tutorial doesn't give any examples.
> Which SQL statements would represent these three types of multiple table
> joins?
> Geoff|||I suppose your answer is an example of (1)?
(1) T1 joins T2 which joins T3
(2) T1 joins T2 and the Join of T1 and T2 joins T3
(3) T1 joins T2 and T1 joins T3
Geoff
"Jerry Spivey" wrote:

> Geoff,
> T1 JOIN T2
> ON T1.ID = T2.ID
> JOIN T3
> ON T2.ID = T3.ID
> However note that the order in which the tables are joined will be
> determined by SQL Server not the order in which the tables are listed in t
he
> FROM clause unless the the SET FORCEPLAN statement is used.
> HTH
> J
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:3FA2D589-6EBF-414E-A4F9-FA37E41C0102@.microsoft.com...
>
>|||Probably. Depends on how the optimizer chooses to join the tables. It may
join 1 to 2 and then that intermediate result set to 3 or 3 to 2 and than
intermediate result set to 1.
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:1181B891-8E78-4A9E-8CBA-A6E020A31A19@.microsoft.com...
>I suppose your answer is an example of (1)?
> (1) T1 joins T2 which joins T3
> (2) T1 joins T2 and the Join of T1 and T2 joins T3
> (3) T1 joins T2 and T1 joins T3
> Geoff
> "Jerry Spivey" wrote:
>|||O.K. I get it. Those three "ways" to join are the ways the RDBMS may have
implemented the JOIN for three tables. The results from each of the three
"ways" are the same.
I read the statement as "These are the three ways to join tables" and you
pick the type of join you want.
I was playing with this as an example of the (3)rd way
SELECT *
FROM (SELECT T1.ID, T1.VAL, T2.METER
FROM T1, T2
WHERE T1.ID = T2.ID) B INNER JOIN
(SELECT T3.ID, T3.NUM
FROM T1, T3
WHERE T1.ID = T3.ID) A ON B.ID = A.ID
I guess I missed the point of the statement in the tutorial.
Thanks for the clarification,
Geoff
"Jerry Spivey" wrote:

> Probably. Depends on how the optimizer chooses to join the tables. It ma
y
> join 1 to 2 and then that intermediate result set to 3 or 3 to 2 and than
> intermediate result set to 1.
>
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:1181B891-8E78-4A9E-8CBA-A6E020A31A19@.microsoft.com...
>
>|||An example of (2) is
(T1 JOIN T2 ON T1.ID = T2.ID) JOIN T3 ON T2.ID = T3.ID
By using parenthesis, you can force the order in which the tables are
joined, creating a scenario in which T1 joins T2 and T3 joins the join of T1
and T2.
An exapmle of (3) is
T1
JOIN T2 ON T1.ID = T2.ID
JOIN T3 ON T1.ID = T3.ID
I think that (1) and (3) are actually just different orders of the same JOIN
statement.
Chris
"Geoff" wrote:
> O.K. I get it. Those three "ways" to join are the ways the RDBMS may have
> implemented the JOIN for three tables. The results from each of the three
> "ways" are the same.
> I read the statement as "These are the three ways to join tables" and you
> pick the type of join you want.
> I was playing with this as an example of the (3)rd way
> SELECT *
> FROM (SELECT T1.ID, T1.VAL, T2.METER
> FROM T1, T2
> WHERE T1.ID = T2.ID) B INNER JOIN
> (SELECT T3.ID, T3.NUM
> FROM T1, T3
> WHERE T1.ID = T3.ID) A ON B.ID = A.ID
>
> I guess I missed the point of the statement in the tutorial.
> Thanks for the clarification,
> Geoff
>
> "Jerry Spivey" wrote:
>

3 small table database. Query: Return All Suppliers, Offering All Product, Excep

I have broken my question down into simpler terms using only 3 small tables, but the idea is the same. Well let me get to the problem (=.

Pretend we have just a small
database with 3 tables (Tb_Supplier, Tb_Product,
Tb_Offers)
Our problem is as follows:
Write an SQL statement which returns ALL Supplier Names who Offer ALL Products EXCEPT computers, cars, and tvs.

Does anyone have any advice how this might be accomplished? Here is our tables..and what I have tried/thought of so far.

CREATE TABLE Tb_Supplier (
Supp_ID [bigint] IDENTITY PRIMARY KEY,
Name [char] (10) NOT NULL ,
)

CREATE TABLE Tb_Product (
Prod_ID [bigint] IDENTITY PRIMARY KEY,
Name [char] (10) NOT NULL ,
)

CREATE TABLE Tb_Offers (
Supp_ID [bigint] REFERENCES Tb_Supplier(Supp_ID) ,
Prod_ID [bigint] REFERENCES Tb_Product(Prod_ID) ,
Quantity [decimal](18, 0) NULL ,
Price [money] NULL
)

The query I'm trying to solve is to return ALL
supplier names, who "offer" ALL products, EXCEPT cars, computers, and tvs. **Without creating any new tables ora dding columns.

Here is what I have tried/ my thoughts. I first tried breaking it
into parts and seeing if I could solve them. For instance, I wanted
to return all suppliers NOT offering computers, cars, or tvs. I
accomplished that with the following query.

SELECT Name
FROM Tb_Supplier
WHERE NOT EXISTS (SELECT *
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))

(also wrote it using the NOT IN statement)

SELECT Name
FROM Tb_Supplier
WHERE Supp_ID NOT IN
(SELECT DISTINCT Supp_ID
FROM Tb_Offers, Tb_Product
WHERE Tb_Offers.Prod_ID=Tb_Product.Prod_ID
AND (Tb_Product.Name='computer'
OR Tb_Product.Name='car'
OR Tb_Product.Name='tv'))

I don't know how to verify though that the suppliers have offered ALL products except those listed (computers, cars, tvs)

The only 3 tables that matter for this query are the Supplier, Product, and Offers Table. Here is what I need(in a small example).

Lets say we have 4 Suppliers. (Supp_ID's 111, 222, 333, and 444) (Names: Rick, Matt, Kelly, Steve) respectively

And we have 6 Products. (Prod_Id's 10, 11, 12, 13, 14, 15) (Names: cars, computers, tvs, soda, furniture, jewelry)

Our Offers Table contains the following

Supp_ID Prod_ID
111 10
111 13

222 11
222 13
222 14

333 13
333 14
333 15

444 14
444 15

I need to write a query which would return just those suppliers who are exactly like the Supplier (333). He offers ALL the products EXCEPT the computers, cars, and tvs.

I wouldn't want number 444, even though he doesn't offer computers, cars, or tvs...he still fails to offer all the other products by not offering #13 which is soda

I hope I am explaining this well. Any reply is greatly appreciated. Thanks!

(Oh and yes this is just Microsoft SQL Syntax)Try the following:

First create a query of the products you want to show.

SELECT Prod_ID, [Name]
FROM Tb_Products
WHERE Prod_ID NOT IN(10, 11, 12 [List of Prod_IDs not to include])

Once this query is created, create the new query using this query instead of Tb_Products|||this is a most interesting problem

let's assume that the primary key of Tb_Offers is (Supp_ID, Prod_ID)

in other words, a given supplier can offer a given product only once

(this is important because we'll be counting rows without using DISTINCT)

the number of products each supplier supplies is given by --
select Supp_ID, count(*)
from Tb_Offers
group by Supp_ID
the total number of products is --
select count(*) from Tb_Products
the suppliers which supply all products are --
select Supp_ID
from Tb_Offers
group by Supp_ID
having count(*) =
( select count(*) from Tb_Products )
now for the tricky part, excluding three certain products

first, let's figure out which product IDs they have --
select Prod_ID
from Tb_Product
where Name in ('computer','car','tv')
now if a supplier supplies one of these three "excluded" products,
let's count a 1, and for any of the other products, let's count a 0 --
select Supp_ID
, sum( case when Prod_ID
in (
select Prod_ID
from Tb_Product
where Name in ('computer','car','tv')
) then 1 else 0 end
) as excluded_product_count
from Tb_Offers
group by Supp_ID
notice how the subquery inside the CASE is not correlated,
which means that it will be extremely efficient

the suppliers we want are those with an "excluded product count" of 0

furthermore, the count of all the products they do supply has to be
3 less than the total number of products

so here is the final query --
select Supp_ID
from Tb_Offers
group by Supp_ID
having sum( case when Prod_ID
in (
select Prod_ID
from Tb_Product
where Name in ('computer' ,'car', 'tv')
) then 1 else 0 end
) = 0
and count(*) =
( select count(*) from Tb_Products ) - 3
i'm fairly confident in this, but have not tested it

please let me know how it works for you

rudy
http://r937.com/
http://rudy.ca/|||CreativeSoul,

What you are after is called relational division.

Celko has an article that you will find very helpful...

http://www.dbazine.com/celko1.html

Please read this first and then look at this query...There are several approaches to achieving this in SQL...but this is usually the easiest to understand....

SELECT S.Supp_ID
FROM Tb_Supplier S
INNER JOIN Tb_Offers O on O.SUPP_ID = S.SUPP_ID
WHERE O.Prod_ID NOT IN(10,11,12)
GROUP BY S.Supp_ID
HAVING COUNT(*) = (SELECT COUNT(*) FROM Tb_Product WHERE Prod_ID NOT IN(10,11,12))

3 quick queries

I want help with a couple of SQL queries.
I have two Tables Table A and Table B.
Both tables have the same two fields Name and Hobbies.
One Name can appear beside multiple hobbies in each table.

There are three queries I wish to run.
1) Find all the Hobbie and Name combinations in Table B not in Table A
only for Names that exist in Table A
2) Find all Hobbies and Name combinations in Table A not in Table B
3) Return all data in Table B that contains a Name that exists in Table
A

Regards,
CiarnLooks like homework to me. Have you looked up some example queries and
tried to adapt them to your application? What have you tried so far?

--
David Portas
SQL Server MVP
--|||No its not homework, what I need to do is slightly different, but
thought if I got the answers to these three I'd be able to figure it
out.
I've been playing with inner and outer joins, but can't figure out what
I need to do.

Regards,
Ciarn

BTW what does MVP stand for?|||On 4 Apr 2005 08:48:30 -0700, chudson007@.hotmail.com wrote:

>I want help with a couple of SQL queries.
>I have two Tables Table A and Table B.
>Both tables have the same two fields Name and Hobbies.
>One Name can appear beside multiple hobbies in each table.
>There are three queries I wish to run.
>1) Find all the Hobbie and Name combinations in Table B not in Table A
>only for Names that exist in Table A
>2) Find all Hobbies and Name combinations in Table A not in Table B
>3) Return all data in Table B that contains a Name that exists in Table
>A
>Regards,
>Ciarn

Hi Ciarn,

I guess the real question is why you store the same data in two tables.

Try if the following work. If not, then post table structure and sample
data as described here: www.aspfaq.com/5006.

1)
SELECT b.Hobbie, b.Name
FROM TableB AS b
WHERE EXISTS (SELECT *
FROM TableA AS a
WHERE a.Name = b.Name)

2)
SELECT a.Hobbie, a.Name
FROM TableA AS a
WHERE NOT EXISTS (SELECT *
FROM TableB AS b
WHERE b.Name = a.Name
AND b.Hobbie = a.Hobbie)

3) Same as 1.

Notes:
* 1 and 3 can also be done with an inner join, but you need some way to
prevent duplicates - either DISTINCT or a derived table
* 2 can also be done with an outer join.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||1 and 2:

SELECT A.name, A.hobby, B.name, B.hobby
FROM TableA AS A
FULL JOIN TableB AS B
ON A.name = B.name
AND A.hobby = B.hobby
WHERE A.name IS NULL
OR B.name IS NULL

3.
SELECT DISTINCT B.name, B.hobby
FROM TableA AS A
JOIN TableB AS B
ON A.name = B.name

Info on the Most Valuable Professional programme:
http://mvp.support.microsoft.com/

--
David Portas
SQL Server MVP
--|||I've tried those queries, but cannot get what I want.
Below are tables like the Tables I am using.

For Query1 I want to return
NameHobby
PhilBoxing
AndyBoxing
CiaranBoxing

For Query2 I want to return
NameHobby
PhilAthletics
AndyRugby
CiaranFootball

For Query3 I want to return
NameHobby
PhilFootball
PhilRugby
PhilAthletics
AndyFootball
AndyRugby
AndyAthletics
CiaranFootball
CiaranRugby
CiaranAthletics

TableA

NameHobby
PhilFootball
PhilRugby
AndyAthletics
AndyFootball
CiaranRugby
CiaranAthletics
PhilBoxing
AndyBoxing
CiaranBoxing

TableB
NameHobby
PhilFootball
PhilRugby
PhilAthletics
AndyFootball
AndyRugby
AndyAthletics
CiaranFootball
CiaranRugby
CiaranAthletics
MarkFootball
MarkRugby
MarkAthletics

Regards,
Ciarn|||I've tried those queries, but cannot get what I want.
Below are tables like the Tables I am using.

For Query1 I want to return
Name Hobby
Phil Boxing
Andy Boxing
Ciaran Boxing

For Query2 I want to return
Name Hobby
Phil Athletics
Andy Rugby
Ciaran Football

For Query3 I want to return
Name Hobby
Phil Football
Phil Rugby
Phil Athletics
Andy Football
Andy Rugby
Andy Athletics
Ciaran Football
Ciaran Rugby
Ciaran Athletics

TableA

Name Hobby
Phil Football
Phil Rugby
Andy Athletics
Andy Football
Ciaran Rugby
Ciaran Athletics
Phil Boxing
Andy Boxing
Ciaran Boxing

TableB
Name Hobby
Phil Football
Phil Rugby
Phil Athletics
Andy Football
Andy Rugby
Andy Athletics
Ciaran Football
Ciaran Rugby
Ciaran Athletics
Mark Football
Mark Rugby
Mark Athletics

Regards,
Ciarn|||I think you swapped the first two around from your original but thanks
for the examples. Try:

SELECT B.name, B.hobby
FROM TableB AS B
LEFT JOIN TableA AS A
ON A.name = B.name
AND A.hobby = B.hobby
WHERE A.hobby IS NULL
AND EXISTS
(SELECT *
FROM TableA
WHERE name = B.name)

SELECT A.name, A.hobby
FROM TableA AS A
LEFT JOIN TableB AS B
ON A.name = B.name
AND A.hobby = B.hobby
WHERE B.hobby IS NULL

My third query seems to produce the result you asked for (in a
different order maybe but you just need to add an ORDER BY clause if a
specific order is important). Try it again and let me know. Here's your
sample in code so that you can easily reproduce it. It's always best to
post your table structures and sample data as code so that others can
understand what your tables look like and test out possible solutions:

CREATE TABLE TableA (name VARCHAR(20) NOT NULL, hobby VARCHAR(20) NOT
NULL, PRIMARY KEY (name,hobby))

CREATE TABLE TableB (name VARCHAR(20) NOT NULL, hobby VARCHAR(20) NOT
NULL, PRIMARY KEY (name,hobby))

INSERT INTO TableA (name, hobby)
SELECT 'Phil', 'Football' UNION ALL
SELECT 'Phil', 'Rugby' UNION ALL
SELECT 'Andy', 'Athletics' UNION ALL
SELECT 'Andy', 'Football' UNION ALL
SELECT 'Ciaran', 'Rugby' UNION ALL
SELECT 'Ciaran', 'Athletics' UNION ALL
SELECT 'Phil', 'Boxing' UNION ALL
SELECT 'Andy', 'Boxing' UNION ALL
SELECT 'Ciaran', 'Boxing'

INSERT INTO TableB (name, hobby)
SELECT 'Phil', 'Football' UNION ALL
SELECT 'Phil', 'Rugby' UNION ALL
SELECT 'Phil', 'Athletics' UNION ALL
SELECT 'Andy', 'Football' UNION ALL
SELECT 'Andy', 'Rugby' UNION ALL
SELECT 'Andy', 'Athletics' UNION ALL
SELECT 'Ciaran', 'Football' UNION ALL
SELECT 'Ciaran', 'Rugby' UNION ALL
SELECT 'Ciaran', 'Athletics' UNION ALL
SELECT 'Mark', 'Football' UNION ALL
SELECT 'Mark', 'Rugby' UNION ALL
SELECT 'Mark', 'Athletics'

Hope this helps.

--
David Portas
SQL Server MVP
--|||Absolutely perfect.
Very much appreciated.
Thanks,
Ciarn

2-table join where only need one row of second table.

For discussion sake, I've got two tables: customers and orders. What I want
is a query that will return the most recent order for each customer. Let's
say the tables are like:

Customer: customerId, customerName
Orders: orderId, customerId, orderDate

The simple joins I've done give me back all of the orders for all of the
customers.

Any advice?Try

select * from customer c, orders o
where c.customerId = o.customerId
and c.orderId =
(
select max (o2.orderDate)
from orders o2
where o2.customerId = c.customerId
)

--
Regards Bagieta
~~~~~~~~~~~~~~~~~~~~~~~~~~~
dbDeveloper - Multiple databases editor
http://www.prominentus.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~

2nd Table on Report Shifts Down

Hi

I have created a report with in reporting services with 2 tables. One at the top of the page, and one at the bottom. The one at the top will contain payment details which grows depending on how many items(rows) are in my dataset and the table at the bottom contains a cheque layout - for ex. Pay Mr X, Date...etc. This table will fill out boxes on a cheque

My problem is that the table at the bottom(cheque) is being shifted down depending on what's in the top table(payment items). I need that the cheque table remain in the sample position, independent from what is in the payment table.

Has anybody ever encountered this problem and have a solution to it?

Thanks in advance.Hi

managed to solve this problem using Sub Reports. I put the top table into a sub report and it works fine.

The only problem is when adding another level of grouping in the Table within the sub report, the initial problem emerges again.

Any other ideas?

|||Try putting the table inside of a rectangle (invisible border). The table should "consume" the space in the containing rectangle before pushing the other table down. However, there is no guarantee that the bottom table will stay on the page if the top grows too much.

2nd Newbie question with Element-centric Mapping and Relational Tables

Actually, have another problem, it's appearing it's concatinating the items together. Here is the actual example. I think this is all being generated from VS.NET, so I'm not sure what some of the name spaces and attributes are for. Is there any way to make them not concatinate like the first example? Thanks a million.

DECLARE @.idoc int

DECLARE @.doc varchar(5000)

SET @.doc ='

<RS_DN_Objective xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<mDocType>2</mDocType>

<SectionID>O0</SectionID>

<IsLoaded>true</IsLoaded>

<sTreat>

<mArr>

<anyType xsi:type="xsdtring">Modality - IFC E-Stim</anyType>

<anyType xsi:type="xsdtring">Hamstring Curl Resisted (Sitting)</anyType>

<anyType xsi:type="xsdtring">Hip Abduction Adduction with Extended Knee (Sup</anyType>

<anyType xsi:type="xsdtring">Hip Abduction Adduction with Knee Flexion (Supine)</anyType>

</mArr>

</sTreat>

</RS_DN_Objective>

'

EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc

SELECT *

FROM OPENXML (@.idoc, 'RS_DN_Objective/sTreat/mArr',2)

WITH (anyType VARCHAR(100) '.')

EXEC sp_xml_removedocument @.idoc

My Results Are:

'

'anyType

'Modality - IFC E-Stim Hamstring Curl Resisted (Sitting) Hip Abduction Adduction with Extended Kn

'

Are you using SQL 2000 or SQL 2005?|||

Try changing your SELECT statement to something like:

Code Snippet

SELECT anyType

FROM OPENXML (@.idoc, 'RS_DN_Objective/sTreat/mArr/anyType',2)

WITH (anyType VARCHAR(100) '.')

/*

anyType

Modality - IFC E-Stim

Hamstring Curl Resisted (Sitting)

Hip Abduction Adduction with Extended Knee (Sup

Hip Abduction Adduction with Knee Flexion (Supine)

*/

|||Sweet, this is a totally cool feature, this worked perfect on 2005. Thanks again for your help, I don't think I'd have gotten it.|||

Another alternative is something like:

Code Snippet

declare @.doc xml

SET @.doc ='

<RS_DN_Objective xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<mDocType>2</mDocType>

<SectionID>O0</SectionID>

<IsLoaded>true</IsLoaded>

<sTreat>

<mArr>

<anyType xsi:type="xsdtring">Modality - IFC E-Stim</anyType>

<anyType xsi:type="xsdtring">Hamstring Curl Resisted (Sitting)</anyType>

<anyType xsi:type="xsdtring">Hip Abduction Adduction with Extended Knee (Sup</anyType>

<anyType xsi:type="xsdtring">Hip Abduction Adduction with Knee Flexion (Supine)</anyType>

</mArr>

</sTreat>

</RS_DN_Objective>

'

select t.value('.', 'varchar(100)') as anyType

from @.doc.nodes('RS_DN_Objective/sTreat/mArr/anyType') as x(t)

/*

anyType

Modality - IFC E-Stim

Hamstring Curl Resisted (Sitting)

Hip Abduction Adduction with Extended Knee (Sup

Hip Abduction Adduction with Knee Flexion (Supine)

*/

2nd lowest with tie

I am working with 3 tables: STUDENTS, GRADES, COURSES

Students GRADES COURSES

Jean 88 Algebra I

Rachel 55 Chemistry

Charlie 75 History

Bud 90 Algebra I

JD 62 Chemistry

Alton 96 History

Jean 81 History

Rachel 40 Algebra I

Jean 79 Chemistry

Charlie 64 Chemistry

Alton 83 Algebra I

Bud 70 Chemistry

I need to find only the second lowest grade among the scores where the student did not take enrolled for History.

thank you

Any comment is welcome

3 tables or 3 columns ?|||declare @.table table
(
Students varchar(10),
GRADES int,
COURSES varchar(10)
)
insert into @.table
select 'Jean', 88, 'Algebra I' union all
select 'Rachel', 55, 'Chemistry' union all
select 'Charlie', 75, 'History' union all
select 'Bud', 90, 'Algebra I' union all
select 'JD', 62, 'Chemistry' union all
select 'Alton', 96, 'History' union all
select 'Jean', 81, 'History' union all
select 'Rachel', 40, 'Algebra I' union all
select 'Jean', 79, 'Chemistry' union all
select 'Charlie', 64, 'Chemistry' union all
select 'Alton', 83, 'Algebra I' union all
select 'Bud', 70, 'Chemistry'

select top 1 *
from
(
select top 2 *
from @.table t
where not exists (select * from @.table x where x.COURSES = 'History' and x.Students = t.Students)
order by GRADES
) a
order by GRADES desc|||

I apologize and should have specify the tables structure. You may disregard my previous post. I am working with 3 tables: INVOICE, TELEVISION, INV_LINE_ITEM.

INVOICE Table TELEVISION Table INV_LINE_ITEM Table

InvoiceDt Type InvoiceNbrFK

InvoiceNbrPK Manufacturer SerialNumberFK

EmpIDFK size Quantity

TotalPrice SerialNumberPK LineNbr

I just want to find out only the invoice number that contains the second lowest total price among the invoices and where the type is not HDTV.

I wrote this query here but I am not so sure about it. Help

SELECT top 1 Invoicenbr, CONVERT(CHAR(25), invoicedt)AS 'Invoice Date',

CAST(min(totalprice) as char(10)) as 'Total Price'

FROM invoice

WHERE totalprice >

(SELECT min(totalprice)

FROM invoice

WHERE invoicenbr in

(SELECT invoicenbrFK

FROM inv_line_item

WHERE serialnumberFK not in

(SELECT serialnumber

FROM Television

WHERE serialnumber = serialnumberFK and type< >'hdtv')))

GROUP by totalprice, invoicenbr, invoicedt;;

|||

Am I right in thinking that you need the invoice with the second lowest price which does not include a line for a television of type HDTV.

In which case the variation on K H Tan's example is I believe:

SELECT TOP 1 InvoiceNbr,
CONVERT(CHAR(25), invoicedt)AS 'Invoice Date',
CAST(min(totalprice) as char(10)) as 'Total Price'
FROM (
SELECT TOP 2 *
FROM INVOICE
WHERE InvoiceNbr NOT IN (
SELECT LIN.InvoiceNbr
FROM INV_LINE_ITEM LIN
INNER JOIN TELEVISION TV
ON (LIN.SerialNumber = TV.SerialNumber ) AND
(TV.Type = 'hdtv')
)
ORDER BY TotalPrice
) A
ORDER BY TotalPrice DESC

I did not use the PK and FK suffixes as you did not have them in your sample SQL.

|||mermaid,

can you post the table structure with sample data and the result that you want similar to my 1st post it will be easier for us to help you|||

Hi,

Your example was a very great help. I added a few changes and I got the projection expected. Here is my final query:

SELECT TOP 1 I.InvoiceNbr,
CONVERT(CHAR(25), I.invoicedt)AS 'Invoice Date',
CAST(min(totalprice) as char(10)) as 'Total Price'
FROM (
SELECT TOP 2 *
FROM INVOICE
WHERE InvoiceNbr NOT IN (
SELECT LIN.InvoiceNbr
FROM INV_LINE_ITEM LIN
JOIN TELEVISION TV
ON (LIN.SerialNumber = TV.SerialNumber ) AND
(TV.Type = 'hdtv')
)
ORDER BY TotalPrice
) I
ORDER BY TotalPrice DESC

Thank you so much.

|||

Hi,

I work on the example posted by one of the user and got the right result. Thank you K H and I do appreciate your help.

2nd INSERT INTO statement in a Button click event

Hi everyone.
I am trying to write 2 INSERT INTO statements into a Button click event. Both statements go to the same database but differnet tables. The first statement works fine but the 2nd causes an error with the Try, Catch, Finally statement. When I remove the ExecuteNonQuery from the 2nd statement, the 2nd INSERT INTO statement fails to work. Any help would be brilliant. Thanks!

Private Sub btnInsertChange_Click(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.CommandEventArgs) Handles btnInsertChange.Command

'Insert Guide changes into ChangesReport table in sql server
Me.SqlCommandThemeTest.Connection = Me.SqlConnection1

Dim Name As String
Dim values As String
Dim sSQL As String
Name = "Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText ContentManager"
values = "lstTheme, lstGuideName, lstGuidePage, lstPageType, lstChangeCategory, lstChangeFrom, txtChangeFrom, lstChangeTo, txtChangeTo, Label1"
SqlCommandThemeTest.CommandText = "INSERT INTO dbo.ChangesReport (Theme, Guide, GuidePage, PageType, ChangeCategory, ChangeFrom, ChangeFromText, ChangeTo, ChangeToText, ContentManager) VALUES (@.themeValue, @.guideValue, @.guidepageValue, @.pagetypeValue, @.changecategoryValue, @.changefromValue, @.changefromtextValue, @.changetoValue, @.changetotextValue, @.contentmanagerValue)"

SqlCommandThemeTest.Parameters.Add("@.themeValue", lstTheme.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.guideValue", lstGuideName.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.guidepageValue", lstGuidePage.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.pagetypeValue", lstPageType.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changecategoryValue", lstChangeCategory.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changefromValue", lstChangeFrom.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changefromtextValue", txtChangeFrom.Text)
SqlCommandThemeTest.Parameters.Add("@.changetoValue", lstChangeTo.SelectedItem.Text)
SqlCommandThemeTest.Parameters.Add("@.changetotextValue", txtChangeTo.Text)
SqlCommandThemeTest.Parameters.Add("@.contentmanagerValue", Label1.Text)

Try
Me.SqlConnection1.Open()
Me.SqlCommandThemeTest.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Me.SqlConnection1.Close()
End Try

'Insert textbox to ChangeLogFrom ddl
Me.CmdDDLFromUpdate.Connection = Me.SqlConnection1

Name = "ChangeFromText"
values = "txtChangeFrom"
sSQL = "INSERT INTO dbo.Change Log From (ChangeLogFrom) VALUES (@.changelogfromValue)"
Me.CmdDDLFromUpdate.Parameters.Add("@.changelogfromValue", txtChangeFrom.Text)

Try
Me.SqlConnection1.Open()
Me.CmdDDLFromUpdate.ExecuteNonQuery()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Me.SqlConnection1.Close()
End Try

I have tried various different things with these 2 statements but can still only get the first to insert.

Any advice would be great. Thanks

|||

It's hard to tell since you've only cut and pasted certain parts of the code. For example, I don't see where you've set the commandtext of the second sqlcommand. I see the sSQL string being set, but that isn't the same thing.

You might also want the keep the sqlconnection open for both updates, no use in closing it then reopening it.

|||Why dont you pass all the parameters to a stored proc and do both the inserts there. That will save you a round trip and your code will be at one place.

2-Dimensional reports.

I have a number of reports to implement that have the following
characteristics:
1) column headers come from database tables - these are normally 4 to 10
items. These tables consist of 1 text field and a record-id field.
2) rows come from other tables according to specified selection criteria.
3) Row summaries based on some calculations are included in a last column
4) group and final calculations are required.
I've implemented one report using a multi-join SQL statement with a GROUP BY
statement and a CUBE statement, putting the results into a Grid report. This
worked except that the final total also shows details that I don't want on
the report. I've been able to blank out the rows for these entries, but
wasn't able to get rid of the rows themselves in the report, so that the
Final total follows a number of blank lines after the last Group-level
subtotal.
Is there any technique that would allow me to use a Table-report and fill in
the column headers from one DataSet in an extensible manner, and then fill in
the row data from a different dataset?You should be able to use aggregates from another data set in the headers of
a table. You say =First(Fields!Header.Value, "headerdataset").
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Altair" <Altair@.discussions.microsoft.com> wrote in message
news:6CA5C7DE-E2D2-4968-A070-9B47D2354518@.microsoft.com...
>I have a number of reports to implement that have the following
> characteristics:
> 1) column headers come from database tables - these are normally 4 to 10
> items. These tables consist of 1 text field and a record-id field.
> 2) rows come from other tables according to specified selection criteria.
> 3) Row summaries based on some calculations are included in a last column
> 4) group and final calculations are required.
> I've implemented one report using a multi-join SQL statement with a GROUP
> BY
> statement and a CUBE statement, putting the results into a Grid report.
> This
> worked except that the final total also shows details that I don't want on
> the report. I've been able to blank out the rows for these entries, but
> wasn't able to get rid of the rows themselves in the report, so that the
> Final total follows a number of blank lines after the last Group-level
> subtotal.
> Is there any technique that would allow me to use a Table-report and fill
> in
> the column headers from one DataSet in an extensible manner, and then fill
> in
> the row data from a different dataset?|||In trying your suggestion, I only get the first element from the dataset.
I've not been successfull, in trying to populate the rest of the column
headers with successive values from the header dataset. From what I've found
in the documentation, there are First(), and a Last() aggregate functions,
but nothing to allow you to iterate through the values.
"Brian Welcker [MSFT]" wrote:
> You should be able to use aggregates from another data set in the headers of
> a table. You say =First(Fields!Header.Value, "headerdataset").
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Altair" <Altair@.discussions.microsoft.com> wrote in message
> news:6CA5C7DE-E2D2-4968-A070-9B47D2354518@.microsoft.com...
> >I have a number of reports to implement that have the following
> > characteristics:
> > 1) column headers come from database tables - these are normally 4 to 10
> > items. These tables consist of 1 text field and a record-id field.
> > 2) rows come from other tables according to specified selection criteria.
> > 3) Row summaries based on some calculations are included in a last column
> > 4) group and final calculations are required.
> >
> > I've implemented one report using a multi-join SQL statement with a GROUP
> > BY
> > statement and a CUBE statement, putting the results into a Grid report.
> > This
> > worked except that the final total also shows details that I don't want on
> > the report. I've been able to blank out the rows for these entries, but
> > wasn't able to get rid of the rows themselves in the report, so that the
> > Final total follows a number of blank lines after the last Group-level
> > subtotal.
> >
> > Is there any technique that would allow me to use a Table-report and fill
> > in
> > the column headers from one DataSet in an extensible manner, and then fill
> > in
> > the row data from a different dataset?
>
>