Showing posts with label return. Show all posts
Showing posts with label return. Show all posts

Wednesday, March 21, 2012

joining two queries ?

How can I return the results of two different queries ?
So that the recordset returned has all the fields from query A, plus the
results from query B ?
Both return the same columns.
ALookup UNION in Books Online.
David Portas
SQL Server MVP
--

Joining tables

Hi,
I have a table A like:
id
--
1
2
3
and a table B like:
id type
-- --
1 lion
1 tyger
2 donkey
3 lion
3 tiger
3 monkey
I'd like to return a table C like:
1 lion
2 donkey
3 lion
i.e., join A and B but only get the first row of multi rows. I don't want to
use cursors if I can. Is there anyway to manipulate joins and nested selects
to do this?
Thx, MohebOn Thu, 10 Mar 2005 14:31:11 -0800, Moheb wrote:
(snip)
>I'd like to return a table C like:
>1 lion
>2 donkey
>3 lion
>i.e., join A and B but only get the first row of multi rows.
(snip)
Hi Moheb,
Try
SELECT A.id, MIN(B.type)
FROM A
INNER JOIN B
ON B.id = A.id
GROUP BY A.id
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||No, because in SQL, rows are not intrinsically ordered. To get the "First"
record, in a group, you have to have some way of defining what you mean by
"first". The database doesn't know (Unless you store a created datetime in
each row) when the row was created, so it doesn't have any way of sequencing
them, except based on the data in the rows...
So you could get the lowest (or highest) alphabetical record for each ID,
but that's not what you want, I think. (coincidentally, the order happens t
o
be alphabetical as you have it listed...)
To get what you want, you need to add a Created datetime to the second
table, and then query
Select id, type From TableB B
Where CreatedDT =
(Select Min(CreatedDT)
From TableB
Where ID = B.ID)
"Moheb" wrote:

> Hi,
> I have a table A like:
> id
> --
> 1
> 2
> 3
> and a table B like:
> id type
> -- --
> 1 lion
> 1 tyger
> 2 donkey
> 3 lion
> 3 tiger
> 3 monkey
> I'd like to return a table C like:
> 1 lion
> 2 donkey
> 3 lion
> i.e., join A and B but only get the first row of multi rows. I don't want
to
> use cursors if I can. Is there anyway to manipulate joins and nested selec
ts
> to do this?
> Thx, Moheb
>|||Hugo,
Thanks for your reply. It almost solves it however your Min clause finds the
minimum "alphabetically" in the type column. How can I get the first row of
multi rows returned by a.id = b.id? Thx, Moheb
"Hugo Kornelis" wrote:

> On Thu, 10 Mar 2005 14:31:11 -0800, Moheb wrote:
> (snip)
> (snip)
> Hi Moheb,
> Try
> SELECT A.id, MIN(B.type)
> FROM A
> INNER JOIN B
> ON B.id = A.id
> GROUP BY A.id
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Thu, 10 Mar 2005 14:59:05 -0800, Moheb wrote:

>Hugo,
>Thanks for your reply. It almost solves it however your Min clause finds th
e
>minimum "alphabetically" in the type column. How can I get the first row of
>multi rows returned by a.id = b.id? Thx, Moheb
Hi Moheb,
A table in a relational database is, by definition, an UNordered set of
rows. There is no concept of a "first" row, only a concept of "first row
when ordered by ...". The sample you posted indicated you wanted the
first when alphabetically ordered by type.
Check if CBretana's post helps you further. If it doesn't then please
indicate in a reply how we can determine which row is "first".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||You and Hugo are both right. To tell the truth, I had done something like
this before and wasn't able to locate it. Upon close examination, it appeare
d
that the columns I wrote for were "sortable" (number, date, alphabetical,
etc.). Thx for the replies.
"CBretana" wrote:
> No, because in SQL, rows are not intrinsically ordered. To get the "Firs
t"
> record, in a group, you have to have some way of defining what you mean by
> "first". The database doesn't know (Unless you store a created datetime i
n
> each row) when the row was created, so it doesn't have any way of sequenci
ng
> them, except based on the data in the rows...
> So you could get the lowest (or highest) alphabetical record for each ID,
> but that's not what you want, I think. (coincidentally, the order happens
to
> be alphabetical as you have it listed...)
> To get what you want, you need to add a Created datetime to the second
> table, and then query
> Select id, type From TableB B
> Where CreatedDT =
> (Select Min(CreatedDT)
> From TableB
> Where ID = B.ID)
> "Moheb" wrote:
>

Monday, March 19, 2012

Joining one database table with other database table in stroed procedures.

Hi All,
I need to perform a join on table1 of database1 with table2 or database2, in a stored procedure and return to my web application.. For this I'm providing execute permission on stored procedure, in database1 and "SELECT" permission on database2 table 2, to my database webuser.
Is there any way ( Another stored procedure in database2), i can get join on two tables without SELECT permission right on table2 ( or table1).
Purely using stored procedures. If so how?Normally you only need to grant execute permission on a stored procedure not select permission on the underlying tables.

Past that I would setup a sp to on database1 to return the data and make sure the webuser has an a security path from database1 to database2. How do yo uhave security setup for this user on databse1 and database2?|||Hi Paul,
Thanks, What is meant by security path? I didnot get ur point.
User have only public permission on both databases. Is it not enough? I do have execute permission on sp1 (Stored Procedure) in database1.
but in sp1 I have a select statement which is a join on database2 table-'table2'. When I tried to execute sp1 from web application only with execute permission on sp1-
I got an error as Webuser does not have 'SELECT' permission on table2 of database2. So I provided it and sp1 worked fine. But I don't want to use a SELECT permission- What is alternate, for the join.
sp1 has simple
SELECT *
FROM database1.dbo.table1 t1
INNER JOIN database2.dbo.table2 t2
ON (t1.col1 = t2.col1)

Originally posted by Paul Young
Normally you only need to grant execute permission on a stored procedure not select permission on the underlying tables.

Past that I would setup a sp to on database1 to return the data and make sure the webuser has an a security path from database1 to database2. How do yo uhave security setup for this user on databse1 and database2?|||by security path I was refering to how a user gets authenticated on server2 when making a connection from server1.

You might try using OPENQUERY to call a stored procedure on server2 and use the results to join to a table on server1. I haven't had the need for this in the past so I am working on theory here. Check BOL for usage on OPENQUERY, they have some good examples.|||When calling a stored proc from database 1, you need execute privs on the proc. You do not need to give the user privs on the table itself as long as it is in database 1, too. If database 2 is owned by the same user that is the owner of database 1, you do not need explicit permissions on the object in table 2 accessed by the proc. However, if the database owners are different for the two databases, the user calling the proc must have explicit permissions in the second database.

If need be, you can change the database owner by:

EXEC sp_changedbowner 'username'

Execute this in the database you want to change.|||Well Both databases are on same server. Is OPENQUERY solve in this case too?|||Originally posted by soumyag
Well Both databases are on same server.

It doesn't matter what server they are on, the owner of the databases is what matters. You can have multiple databases on the same server with different owners. In query analyzer, run:

sp_helpdb

It will list the owner for each of the databases. If the owner is different, run sp_changedbowner to set them the same.|||Hi,
Both are on same server, but owned by different users. And I don't have any right to change the dbowners But how it will help in writing a join on tables. What is other alternative way to solve this problem.
Thanks.

Originally posted by bglass

It doesn't matter what server they are on, the owner of the databases is what matters. You can have multiple databases on the same server with different owners. In query analyzer, run:

sp_helpdb

It will list the owner for each of the databases. If the owner is different, run sp_changedbowner to set them the same.

Monday, March 12, 2012

Joining a query (inner/outer)

I have this current sql query which works great, but it only returns
the rows that exist in all tables.
I need to return all rows from po_cfg_Budget B and simply fill in 0 if
there are no totals.
Any help would be greatly appreciated.
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
(B.Budget - NotApproved - Approved) as AmountRemaining,
(B.Budget - Approved) as AmountApprovedRemaining
from BudgetStatus S,
(select * from po_cfg_budget) B,
(Select * from po_cfg_dept) D
where S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
and S.DeptID = D.BudgetDeptID
order by B.DeptIDThis might work for you (passes syntax check but not actually
tested)...
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
(B.Budget - NotApproved - Approved) as AmountRemaining,
(B.Budget - Approved) as AmountApprovedRemaining
from BudgetStatus S
left outer join po_cfg_budget B on S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
left outer join po_cfg_dept D on S.DeptID = D.BudgetDeptID
order by B.DeptID
If you have null dollar amounts you can wrap those in the ISNULL or
COALESCE function.
Bryce|||Not entirely sure what you are looking for but try this.
select S.*,
b.Budget,
B.Description,
D.DeptID as DeptDescription,
coalesce((B.Budget - NotApproved - Approved), 0) as AmountRemaining,
coalesce((B.Budget - Approved),0) as AmountApprovedRemaining
from po_cfg_budget B
left outer join BudgetStatus S
on S.Years = B.BudgetYear
and S.Months = B.BudgetMonth
and S.DeptID = B.DeptID
and S.AccountID = B.AccountID
left outer join po_cfg_dept D
on D.BudgetDeptID = S.DeptID
order by B.DeptID
The inline views that you were using were redundant.
"Dave" wrote:

> I have this current sql query which works great, but it only returns
> the rows that exist in all tables.
> I need to return all rows from po_cfg_Budget B and simply fill in 0 if
> there are no totals.
> Any help would be greatly appreciated.
> select S.*,
> b.Budget,
> B.Description,
> D.DeptID as DeptDescription,
> (B.Budget - NotApproved - Approved) as AmountRemaining,
> (B.Budget - Approved) as AmountApprovedRemaining
> from BudgetStatus S,
> (select * from po_cfg_budget) B,
> (Select * from po_cfg_dept) D
> where S.Years = B.BudgetYear
> and S.Months = B.BudgetMonth
> and S.DeptID = B.DeptID
> and S.AccountID = B.AccountID
> and S.DeptID = D.BudgetDeptID
> order by B.DeptID
>

Joining 2 queries in one table

I am trying to join 2 queries within one table, but as the results return 2 columns and 12 rows for each query, I am having problems working out how to join them together. (This is actually part of a far larger join of many queries, but this is the bit I am stuck on!)

Any help would be appreciated.

Thanks
Smile

SELECT CONVERT(nvarchar,MONTH(Job.job_cdate))+'/'+CONVERT(nvarchar,YEAR(Job.job_cdate)) AS 'Month',COUNT(*) AS 'TotalJobsIn Year' FROM Job
WHERE Job.job_cdate BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Job.job_cdate),YEAR(Job.job_cdate)

SELECT CONVERT(nvarchar,MONTH(Appointment.actual_end_date))+'/'+CONVERT(nvarchar,YEAR(Appointment.actual_end_date)) AS 'Month',COUNT(*) AS 'TotalAppsPerMonth' FROM Appointment
WHERE Appointment.actual_end_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Appointment.actual_end_date),YEAR(Appointment.actual_end_date)

You want to UNION them from what I can see.

SELECT CONVERT(nvarchar,MONTH(Job.job_cdate))+'/'+CONVERT(nvarchar,YEAR(Job.job_cdate)) AS 'Month',COUNT(*) AS 'TotalJobsIn Year' FROM Job
WHERE Job.job_cdate BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Job.job_cdate),YEAR(Job.job_cdate)
UNION ALL
SELECT CONVERT(nvarchar,MONTH(Appointment.actual_end_date))+'/'+CONVERT(nvarchar,YEAR(Appointment.actual_end_date)) AS 'Month',COUNT(*) AS 'TotalAppsPerMonth' FROM Appointment
WHERE Appointment.actual_end_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Appointment.actual_end_date),YEAR(Appointment.actual_end_date)

Adamus

|||

Here You Go...

Code Snippet

Select

Isnull(JobData.[Month],AppointmentData.[Month]) as [Month]

,JobData.[TotalJobsIn Month]

,AppointmentData.[TotalAppsPer Month]

From

(

SELECT

CONVERT(nvarchar,MONTH(Job.job_cdate))+'/'+CONVERT(nvarchar,YEAR(Job.job_cdate)) AS 'Month'

,COUNT(*) AS 'TotalJobsIn Month'

FROM

Job

WHERE

Job.job_cdate BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)

AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))

GROUP BY

MONTH(Job.job_cdate),YEAR(Job.job_cdate)

) as JobData

FULL OUTER Join

(

SELECT

CONVERT(nvarchar,MONTH(Appointment.actual_end_date))+'/'+CONVERT(nvarchar,YEAR(Appointment.actual_end_date)) AS 'Month'

,COUNT(*) AS 'TotalAppsPer Month'

FROM

Appointment

WHERE

Appointment.actual_end_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)

AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))

GROUP BY

MONTH(Appointment.actual_end_date),YEAR(Appointment.actual_end_date)

) as AppointmentData On JobData.[Month] = AppointmentData.[Month]

|||Thanks Manivannan. That is exactly what I needed - I knew I couldn't use the union clause as I wanted the results next to each other.
Thanks so much
K Smile

Friday, March 9, 2012

Join with Subselect and Sum

I have an appointment table (appts) and a charge table (charge).
For the day the query is run, I want to return anyone with an appointment
that has a balance on the charge table.
I am looking at doing a subselect but I'm having a syntax problem.
select APPTS.ACCOUNT
from APPTS
where APPTS.DATE = getdate()
and company = 'main'
INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
FROM CHARGE
WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
What am I doing incorrectly on the subselect?
THANKS,
MEGyou are missing criteria to justify your first where clause
where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0) = [whatever][/colo
r]
"MEG" wrote:
> I have an appointment table (appts) and a charge table (charge).
> For the day the query is run, I want to return anyone with an appointment
> that has a balance on the charge table.
> I am looking at doing a subselect but I'm having a syntax problem.
> select APPTS.ACCOUNT
> from APPTS
> where APPTS.DATE = getdate()
> and company = 'main'
> INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
> where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
> What am I doing incorrectly on the subselect?
> THANKS,
> MEG|||Try,
select
a.ACCOUNT
from
APPTS as a
INNER JOIN
CHARGE as c
on a.COMPANY = c.COMPANY
and a.ACCOUNT = c.ACCOUNT
where
a.[DATE] = convert(char(8), getdate(), 112)
and a.company = 'main'
group by
a.ACCOUNT
having
SUM(c.CHGAMOUNT - c.PAYINS1 - c.PAYINS2 - c.PAYGUAR - c.WRITEOFF - c.ADJUST
+ c.DEDUCTIBLE - c.ONACCT) > 0
AMB
"MEG" wrote:

> I have an appointment table (appts) and a charge table (charge).
> For the day the query is run, I want to return anyone with an appointment
> that has a balance on the charge table.
> I am looking at doing a subselect but I'm having a syntax problem.
> select APPTS.ACCOUNT
> from APPTS
> where APPTS.DATE = getdate()
> and company = 'main'
> INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
> where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
> SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
> FROM CHARGE
> WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
> What am I doing incorrectly on the subselect?
> THANKS,
> MEG|||On Wed, 7 Sep 2005 12:16:03 -0700, MEG wrote:

>I have an appointment table (appts) and a charge table (charge).
>For the day the query is run, I want to return anyone with an appointment
>that has a balance on the charge table.
>I am looking at doing a subselect but I'm having a syntax problem.
>select APPTS.ACCOUNT
>from APPTS
>where APPTS.DATE = getdate()
>and company = 'main'
>INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
>where ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
>SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) - SUM(ONACCT)
>FROM CHARGE
>WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) > 0)
>What am I doing incorrectly on the subselect?
Hi MEG,
The subselect in itself looks okay. But the order of the phrases in the
complete query is not quite right. Try:
select APPTS.ACCOUNT
from APPTS
INNER JOIN CHARGE on CHARGE.ACCOUNT=APPTS.ACCOUNT
where APPTS.DATE = getdate()
and company = 'main'
and ((SELECT SUM(CHGAMOUNT) - SUM(PAYINS1) - SUM(PAYINS2) -
SUM(PAYGUAR) - SUM(WRITEOFF) - SUM(ADJUST) + SUM(DEDUCTIBLE) -
SUM(ONACCT)
FROM CHARGE
WHERE CHARGE.ACCOUNT=APPTS.ACCOUNT AND CHARGE.COMPANY=APPTS.COMPANY) >
0)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

join two tables returning unlike data

How do you return unlike data between two identical tables.
IE : Table1 , column1,column2,colum3,column4
Table2 , column1,column2,colum3,column4
I need the rows that are not in Table2.
Thanks for the Help
pixel696SELECT a.* FROM myTableA a LEFT JOIN myTableB b
WHERE a.myKey = b.myKey WHERE b.myKey IS NULL

is one way...|||brett, i think you put one too many where clauses. the first one should be replaced with on.

join two tables and only return the latest data for the child table

I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as:

select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID

, all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this?

I tried to create a function to get the latest sentence for a chargeID like the following:

select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it?

thanks,

if you are on 2005, you can use row_number(), as follows:

select * from(
select c.*, s.*, row_number() over(partition by s.ChargeID order by DateCreated desc) as rn
from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID
) t
where rn=1
|||

Your suggestion works, thanks.

But when I modified the query to:

select c.*, s.*, row_number() over(partition by s.ChargeID order by DateCreated desc) as rn
from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID

where rn=1

the query fails with the error "Invalid column name 'rn'.", why?

|||Because Where applies to the source fields, not the result fields, so your alias "as rn" hasn't been applied yet.

That's why Akuz nested it inside another Select. The outer Select/Where can use the alias.

Join Three tables?

hello,

I'm need some help with some sql to return some data...obviously ;)

I have 3 tables. tblMgr, tblSup, and tblRep. I'd like to get all the
names of the reps that are assigned to a particular manager.

Here is a simplified exmaple of table setup:

tblRep
---
repid repname supid
-------
1 joe 500
2 ann 501

tblSup
---
supid supname mgrid
-------
500 bill 1000
502 tom 1001

tblMgr
---
mgrid mgrname
-----
1000 andy
1001 roger

Thanks."MEM" <mmaxsom@.citlink.net> wrote in message news:9403d191.0310271531.75aa7bf4@.posting.google.c om...
> hello,
> I'm need some help with some sql to return some data...obviously ;)
> I have 3 tables. tblMgr, tblSup, and tblRep. I'd like to get all the
> names of the reps that are assigned to a particular manager.
> Here is a simplified exmaple of table setup:
> tblRep
> ---
> repid repname supid
> -------
> 1 joe 500
> 2 ann 501
>
> tblSup
> ---
> supid supname mgrid
> -------
> 500 bill 1000
> 502 tom 1001
>
> tblMgr
> ---
> mgrid mgrname
> -----
> 1000 andy
> 1001 roger
> Thanks.

CREATE VIEW RepsManagers (repid, repname, mgrid, mgrname)
AS
SELECT R.repid, R.repname, M.mgrid, M.mgrname
FROM tblRep AS R
INNER JOIN
tblSup AS S
ON R.supid = S.supid
INNER JOIN
tblMgr AS M
ON S.mgrid = M.mgrid

-- For example
SELECT *
FROM RepsManagers
WHERE mgrname = 'andy'

Regards,
jag|||"MEM" <mmaxsom@.citlink.net> wrote in message
news:9403d191.0310271531.75aa7bf4@.posting.google.c om...
> hello,
> I'm need some help with some sql to return some data...obviously ;)
> I have 3 tables. tblMgr, tblSup, and tblRep. I'd like to get all the
> names of the reps that are assigned to a particular manager.

You didn't specify if you wanted all managers (even if they don't have any
reps assigned)... If this is the case, you'll need to use an outer join...
This script creates tables in tempdb... Hope this helps...

use tempdb
go
drop table tblRep
drop table tblSup
drop table tblMgr

go
create table tblRep (repid int, repname char(5), supid int)
create table tblSup (supid int, supname char(5), mgrid int)
create table tblMgr (mgrid int, mgrname char(5))
go

insert into tblRep values (1,'name1', 1)
insert into tblRep values (2,'name2', 1)

insert into tblSup values (1,'sup1',10)
insert into tblSup values (2,'sup2',10)
insert into tblSup values (4,'sup4',20)

insert into tblMgr values (1, 'mgr1')
insert into tblMgr values (10, 'mgr10')
insert into tblMgr values (20, 'mgr20')
go

select
m.mgrid, m.mgrname, r.repid, r.repname
from tblMgr m
left outer join tblSup s on s.mgrid = m.mgrid
left outer join tblRep r on r.supid = s.supid
go|||"MEM" <mmaxsom@.citlink.net> wrote in message
news:9403d191.0310271531.75aa7bf4@.posting.google.c om...
> hello,
> I'm need some help with some sql to return some data...obviously ;)
> I have 3 tables. tblMgr, tblSup, and tblRep. I'd like to get all the
> names of the reps that are assigned to a particular manager.

You didn't specify if you wanted all managers (even if they don't have any
reps assigned)... If this is the case, you'll need to use an outer join...
This script creates tables in tempdb... Hope this helps...

use tempdb
go
drop table tblRep
drop table tblSup
drop table tblMgr

go
create table tblRep (repid int, repname char(5), supid int)
create table tblSup (supid int, supname char(5), mgrid int)
create table tblMgr (mgrid int, mgrname char(5))
go

insert into tblRep values (1,'name1', 1)
insert into tblRep values (2,'name2', 1)

insert into tblSup values (1,'sup1',10)
insert into tblSup values (2,'sup2',10)
insert into tblSup values (4,'sup4',20)

insert into tblMgr values (1, 'mgr1')
insert into tblMgr values (10, 'mgr10')
insert into tblMgr values (20, 'mgr20')
go

select
m.mgrid, m.mgrname, r.repid, r.repname
from tblMgr m
left outer join tblSup s on s.mgrid = m.mgrid
left outer join tblRep r on r.supid = s.supid
go

Wednesday, March 7, 2012

Join syntax?

I'm not sure if this is a join problem or even if it can be done.
The english part of the query will be requesting cagegoires="Cat2"
and return the matching records in table1, plus return all the other
categories that belong to the set in table 2.
should return two records..
1 document1 Cat1 Cat2 Cat3
2 document2 Cat1 Cat2
table 1
ID int DOC char(20)
1 document1
2 document2
table 2
ID int Category char(4)
1 Cat1
1 Cat2
1 Cat3
2 Cat1
2 Cat2
Thanks,
Don
table 1
1 do
On Mon, 29 Aug 2005 13:56:36 -0700, DonSQL2222 wrote:

>I'm not sure if this is a join problem or even if it can be done.
>The english part of the query will be requesting cagegoires="Cat2"
>and return the matching records in table1, plus return all the other
>categories that belong to the set in table 2.
>should return two records..
>1 document1 Cat1 Cat2 Cat3
>2 document2 Cat1 Cat2
(snip)
Hi DonSQL2222,
To get the basic information, but not in the format you specified above:
SELECT t1.ID, t1.DOC, t2.Category
FROM table1 AS t1
INNER JOIN table2 AS t2
ON t2.ID = t1.ID
ORDER BY t1.ID, t2.Category
Modifying the format to your desired form should be done at the front
end. Presentation is not a task for the database in a tiered
architecture.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, February 24, 2012

Join Problems

Hi,
The following query has failed to return all the records.
SELECT b.Account_desc, b.Account,
IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
where a.source_type = 'LY01'
There are total 143 records in Actual_account_Tbl. But the above query
returned only 135 records i.e., only those records satisfy the condition
"a.Account_desc = b.Account_desc" are returned.
As per right outerjoin in the above statement I suppose to get all the
records from table 'b', and blank data from table 'a' if it doesn't
satisfy the condition.
Why it is not consistant?
Pls help me.
Thanks and Regards.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
On Thu, 27 May 2004 23:47:15 -0700, k k wrote:

>Hi,
>
>The following query has failed to return all the records.
>SELECT b.Account_desc, b.Account,
>IIf(a.source_type = 'LY01', a.CSPL_CSPL,0), IIf(a.source_type = 'LY01',
>a.CSPL_CMS,0), IIf(a.source_type = 'LY01', a.CSPL_CMM,0),
>IIf(a.source_type = 'LY01', a.CSPL_CMT,0) from Actual_data_final a right
>outer join Actual_account_Tbl b on a.Account_desc = b.Account_desc
>where a.source_type = 'LY01'
>There are total 143 records in Actual_account_Tbl. But the above query
>returned only 135 records i.e., only those records satisfy the condition
>"a.Account_desc = b.Account_desc" are returned.
>As per right outerjoin in the above statement I suppose to get all the
>records from table 'b', and blank data from table 'a' if it doesn't
>satisfy the condition.
>Why it is not consistant?
>Pls help me.
>Thanks and Regards.
Hi k k,
See my reply in comp.databases.ms-sqlserver.
Please don't crosspost!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Monday, February 20, 2012

Join Issue

I have below 2 tables......

T1
COL1 COL2
1 2
5 6

T2
COL1 COL2
1 2
8 9

I need a query which will return me the data from T1 and the resultset should be......

COL1 COL2
5 6

Thanks,
Rahul JhaWhy should it be that? What is the significance of T2?|||select * from T1 where COL1=5|||http://w3schools.com/sql/sql_join.asp

Have a thorough read of the above link and give it a try.
Post your attempts back here regardless of if you are successful or not.

-George|||http://w3schools.com/sql/sql_join.asp

Have a thorough read of the above link and give it a try.
Post your attempts back here regardless of if you are successful or not.

-George

What exactly you wanted to proove from the above link is not know to me GEORGE. I like staraight talks......... hence would expect you to write the direct comment......... If you want me to understand what excatly the joins are, then you are the wrong person for this thread (Which I have mentioned even in my earlier threads)..... hence request you to keep out of it......

I was asked this question in an interview a long back. All of a sudden it came in my mind and gave a try. But didn't get through it......... Hence posted it to you guys......... :):) :)|||my question is same.. What is the significance of T2???
and if u know that what r the data in the table then

select * from t1 where col1='5'|||George, request you to read and understand the THREAD QUESTION. Don't just read the thread TITLE...........

Thanks,
Rahul Jha|||my question is same.. What is the significance of T2???

Don't know what is the significance of T2...... As said that I was asked this question a long back in an interview......

and if u know that what r the data in the table then

select * from t1 where col1='5'

Even I said the same answer...... but he wanted this through IF EXISTS and LEFt OUTER JOIN...... I didn't get any way to write the type of query that was been asked......

Thanks,
Rahul Jha|||Your question doesn't make sense
Therefore LoztInSpace and jezemine's answers are perfectly correct
What's the point in knowing the answer without knowing how you got there?
The link provided explains the most commonly used joins with very simple examples of how to use them.
Here's a clue... The join type starts with an "L" and without clarification of your questions I'm going to guess at you needing an "AND" clause as well.

Oh and if you were interviewing and got an SQL question - wouldn't it be nice to learn the answer yourself, so you could answer it in future interviews?

And you don't like straight talks, you like the answer on the plate - and you're not getting it on this one.|||And Rahul, I did read the entire thread and supplied a very useful answer (if I do say so myself). I'm trying to help you to help yourself - but clearly my advice is not appreciated so I am going to hold back on giving it to you from now on. I don't think you appreciate how many of your previous questions I have helped you on.|||actually my initial answer was wrong. try this:

select 5 as COL1, 6 as COL2

It's bulletproof because it will work even if the tables don't exist, and it works on any database.|||And Rahul, I did read the entire thread and supplied a very useful answer (if I do say so myself). I'm trying to help you to help yourself - but clearly my advice is not appreciated so I am going to hold back on giving it to you from now on. I don't think you appreciate how many of your previous questions I have helped you on.

why should he bother learning the intricacies of relational databases to do his job when you are always here to do his job for him. it is a lot more efficient to just ask you.

this is why, and you may have noticed, I write very little code for posters. I tend to nudge people in certain directions and give advice so that they will be more apt to figure things out on their own because that is the only way to learn how to program.