Outer Join Syntax Problems (Multiple Tables)
Sep 13, 2005
Hello all--
I'm trying to run a SELECT on 3 tables: Class, Enrolled, Waiting.
I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.
My current query...
SELECT Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
GROUP BY Class.Name
...results in identical counts for enrolled and waiting, which I know
to be incorrect. Furthermore, it appears that the counts are being
multiplied together (in one instance, enrolled should be 14, waiting
should be 2, but both numbers come back as 28).
If I run this query without one of the joined tables, the counts are
accurate. The problem only occurs when I try to pull counts from both
the tables.
Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?
Thanks very much for your time,
--Jeremy
View 2 Replies
ADVERTISEMENT
Nov 2, 2005
I'm trying to join 3 tables in an outer join since I am loosing records that need to be included if I only use an inner join. I am pulling data from an MSDE database using the microsoft query tool.
The problem is that I get the message that I can't use an outer join on a query with more than 2 tables, but that can't be right can it?
I'm a SQL code novice so any help would be greatly appreciated!
SELECT
Article.articleId
, Article.articleName
, Article.articleStatus
, Articlegroup_2.ArticlegroupId
, Articlegroup_2.g2_key
, Articlegroup_2.g2_name
, articleGroup.articleGroupId
FROM
HIP.dbo.Article Article, HIP.dbo.articleGroup articleGroup, HIP.dbo.Articlegroup_2 Articlegroup_2
WHERE
articleGroup.articleGroupId = Article.articleGroupId AND
Article.articleGroupId2 = Articlegroup_2.Articlegroup_2_Id
View 5 Replies
View Related
Dec 14, 2007
I'm trying to join 2 tables in an outer join, but MS Query won't let me do this because I have another 2 tables included in an inner join ("only two tables are allowed in an outer join"). I am pulling data from an MSDE database using the microsoft query tool.
I'm a SQL code novice so any help would be greatly appreciated!
Here is my existing SQL query (without the new outer join table):
SELECT
Lead_.Country, Lead_.Company_Name, Employee.Full_Name, Lead_.Rn_Create_Date, Lead_.Marketing_Project_Name, Employee_1.Full_Name, Lead_.Comments
FROM
ProductionED.dbo.Employee Employee, ProductionED.dbo.Employee Employee_1, ProductionED.dbo.Lead_ Lead_
WHERE
Employee.Employee_Id = Lead_.Account_Manager_Id AND Employee_1.Employee_Id = Lead_.Created_By_Employee_Id AND ((Lead_.Market_Segment='new'))
View 5 Replies
View Related
Jul 20, 2005
I am trying to select specific columns from multiple tables based on acommon identifier found in each table.For example, the three tables:PUBACC_ACPUBACC_AMPUBACC_ANeach have a common column:PUBACC_AC.unique_system_identifierPUBACC_AM.unique_system_identifierPUBACC_AN.unique_system_identifierWhat I am trying to select, for example:PUBACC_AC.namePUBACC_AM.phone_numberPUBACC_AN.zipwhere the TABLE.unique_system_identifier is common.For example:----------------------------------------------PUBACC_AC=========unique_system_identifier name1234 JONES----------------------------------------------PUBACC_AM=========unique_system_identifier phone_number1234 555-1212----------------------------------------------PUBACC_AN=========unique_system_identifier zip1234 90210When I run my query, I would like to see the following returned as oneblob, rather than the separate tables:-------------------------------------------------------------------unique_system_identifier name phone_number zip1234 JONES 555-1212 90210-------------------------------------------------------------------I think this is an OUTER JOIN? I see examples on the net using a plussign, with mention of Oracle. I'm not running Oracle...I am usingMicrosoft SQL Server 2000.Help, please?P. S. Will this work with several tables? I actually have about 15tables in this mess, but I tried to keep it simple (!??!) for the aboveexample.Thanks in advance for your help!NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAILADDRESS.Who actually BUYS the cr@p that the spammers advertise, anyhow???!!!(Rhetorical question only.)
View 1 Replies
View Related
Aug 19, 2002
Hi,
Can anyone please tell me how can i convert the following query in
the old join syntax to the new join syntax so that it returns the same
results? I tried converting it, but it returns different reuslts.
please help me as it is urgent.
OLD Syntax:
===========
SELECT
....................
..<column_list>......
FROM
dbo.ASSET_BOOK AL1,
dbo.ASSET_BOOK AL2,
dbo.ASSET_HEAD AL3,
dbo.ASSET_BOOK AL4,
dbo.ASSET_BOOK AL5
WHERE
(AL1.U##ASSETNO =* AL3.U##ASSETNO
AND AL3.U##ASSETNO *= AL2.U##ASSETNO
AND AL4.U##ASSETNO =* AL3.U##ASSETNO
AND AL3.U##ASSETNO *= AL5.U##ASSETNO)
AND
(AL1.U##BOOKCODE = 'USGAAP'
AND AL2.U##BOOKCODE = 'CONSUSD'
AND AL4.U##BOOKCODE = 'ICP'
AND AL5.U##BOOKCODE = 'STETC'
)
New Syntax:
===========
SELECT
COUNT(*)
FROM dbo.ASSET_BOOK AL1
RIGHT JOIN dbo.ASSET_HEAD AL3
on AL1.U##ASSETNO = AL3.U##ASSETNO
LEFT JOIN dbo.ASSET_BOOK AL2
on AL3.U##ASSETNO = AL2.U##ASSETNO
RIGHT JOIN dbo.ASSET_BOOK AL4
on AL4.U##ASSETNO = AL3.U##ASSETNO
LEFT JOIN dbo.ASSET_BOOK AL5
on AL3.U##ASSETNO = AL5.U##ASSETNO
AND
AL1.U##BOOKCODE = 'USGAAP'
AND AL2.U##BOOKCODE = 'CONSUSD'
AND AL4.U##BOOKCODE = 'ICP'
AND AL5.U##BOOKCODE = 'STETC'
Thanks in advance.
Rafi
View 2 Replies
View Related
Jul 20, 2005
I am now working on SQL Server 2000 having had previous experience ona different database. Both of the OUTER JOIN syntaxes is differentfrom what I am used to and I am finding it slightly confusing.For example, given two tables :wipm_tbl_miwipm_tbl_wi (which may not have data in it for a specific record thatexists in the first table.)If I use the old style syntax :SELECT mi.workitemid, wi.datavalueFROM wipm_tbl_mi mi , wipm_tbl_wi wiWHERE mi.workitemid *= wi.workitemidAND mi.workitemid = 1AND wi.dataname = 'XXX'I get back1,NULLwhen there is no matching record in wipm_tbl_wi, which is what Iexpect.However, if I try to use the SQL-92 syntaxSELECT mi.workitemid, wi.datavalueFROM wipm_tbl_mi miLEFT OUTER JOIN wipm_tbl_wi wiON mi.workitemid = wi.workitemidWHERE mi.workitemid = 1AND wi.dataname = 'XXX'I don't get anything back. Please can someone help me understand whatis wrong with the bottom query.Thank you,Martin
View 2 Replies
View Related
Jul 20, 2005
I apologize if this has been asked before- I searched google but couldnot find a concrete answer.I recently inherited a database whose t-sql code is written in a formatthat I find difficult to read (versus the format I have used foryears).I have tested the queries below using the SQL Profiler, and both haveidentical costs. Is there any advantage of one format over the other?Format 1:---------SELECT *FROM Customers c, Orders oWHERE c.CustomerID = o.CustomerIDFormat 2:---------SELECT *FROM Customers cINNER JOIN Orders AS o ON c.CustomerID = o.CustomerIDIs it just a matter of personal preference? Does the same hold true forusing OUTER JOIN versus the old *= or =* ?Thanks,Matt
View 3 Replies
View Related
Aug 24, 2006
Hi guys,
How can I translate this join into new syntax?
SELECT S.Dr_Code
FROM GJCSite S , GJCJob_Det JD, GJCSub_Job SJ
WHERE S.Com_Code = SJ.Com_Code (+)
AND JD.Sub_Job_Code = SJ.Sub_Job_Code (+)
AND JD.Job_Code = SJ.Job_Code (+)
Cheers,
View 5 Replies
View Related
Aug 22, 2015
I'm a Power Builder (PB) developer. I've migrated PB from 10 .5 to 12.5, and now I need to migrate the stored procedures from SQL Server 2005 to 2012 as well.I get a invalid expression error when I run the procedures in Power Builder. We have previously been using a lower database compatibility model in 2005, which allowed the procedures to work there. I have learned that =* is the old way to write right outer joins.
For example:
select *
from A
right outer join
B
[code]...
Is my approach correct, or do I need to add a WHERE condition to it? I don't have access to the production database to check. The development database is in SQL Server 2012 too, sO I will not be able to run the old version there to check.
View 8 Replies
View Related
Apr 16, 2008
Anyone know why using
SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of
SELECT *
FROM a LEFT JOIN b
ON a.id = b.id
generates a different execution plan?
My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".
Any enlightenment is very appreciated.
Thanks
View 5 Replies
View Related
Jun 7, 2007
Is it possible to do a full outer join on more than 2 tables, i've been looking for the syntax online but can't seem to find it, so far i have
FROM dbo.TRNEWL_RTENTN_DETL RR
FULL OUTER JOIN VBVT_FIN_DIV_TRANSF
ON RR.X_POLICY_NO = VBVT_FIN_DIV_TRANSF.X_POLICY_NO and
RR.X_POLICY_EFCTV_DT= VBVT_FIN_DIV_TRANSF.X_POLICY_EFCTV_DT and
RR.X_ASCO_CD = VBVT_FIN_DIV_TRANSF.X_ASCO_CD and
RR.PRODUCT_RENWL_ABBR = VBVT_FIN_DIV_TRANSF.PRODUCT_RENWL_ABBR
Now i need to do another Full outer join to RR with table VBVT_FIN_RGN_TRANSF, is that possible?
View 4 Replies
View Related
Dec 16, 2004
What is the best way to use a left join in a SQL statement with multiple tables (more than 2)? I have a query that uses 7 tables, where most of the joins are inner joins, except for one, which needs to be a left join. The current SQL statement looks something like this:
SELECT [table1].[field1], [table2].[field1], [table3].[field1], [table4].[field1], [table5].[field1], [table6].[field1], [table7].[field1]
FROM [table1],[table2],[table3],[table4],[table5],[table6],[table7]
WHERE
[table4].[field2]=[table1.field2]{this is an inner join}
[table4].[field2]=[table2.field2]{this is an inner join}
[table4].[field2]=[table3.field2]{this is an inner join}
[table4].[field2]=[table5.field2]{this is an inner join}
[table5].[field3]=[table6.field2]{this is an inner join}
[table5].[field4]=[table7.field2]{this is needs to be a left join}
As it stands now, the last line in the WHERE clause is an INNER JOIN and limits the number of rows in my result. I need to select rows from [table7].[field2] whether or not a matching record exists in [table5].[field4]. The other INNER JOINS in the SQL statement must have matching records. Please advise.
View 2 Replies
View Related
Nov 8, 2007
Hi all,
I need a small help in creating a join query in sql.
My two tables are A and B. A has got columns id, dname, counter and
date. B has got two columns link and date. The primary key of A
is the column Id whereas in table B, both are primary keys(composite key).
I need to get the records (id,dname,counter) from A which has no
corresponding link in table B.
To be precise, If the table A has fields (id,dname,counter) (1,abc,2000)
and if table B has no record for abc, this row should be returned from A.I hope using left outer join will help me in getting my desired result.I hope someone will be able to help me out.
Thanking all in advance for your valuable time.
View 1 Replies
View Related
Oct 29, 2007
Hello,I'm trying to link two tables... one for Employees and the other forTimecardsI need to get a list of employees that do not have timecards on anSPECIFIC DATEI tried the follonwingSELECT Employess.EmployeeIDFROM Employees LEFT OUTER JOIN Timecards on Employees.EmployeeID =Timecards.lmpEmployeeIDWHERE lmpEmployeeID is NULL and lmpTimecardDate = '10/24/2007'But it doesn't work. However, when I comment the date condition out(lmpTimecardDate = '10/24/2007') it works all right but It's not whatI needAnother interesting point... if I use the following query... it worksall rightSELECT Employess.EmployeeIDFROM EmployeesWHERE Employees.EmployeeID not in (select Timecards.EmployeeID fromTimecardswhere TimecardDate = '10/24/2007')I'd like to be able to use the Left Outer Join option.... Am I doingsomething wrong?... or is it that if It doesn't like the condition I'musgin in the WHERE clause (TimecardDate = '10/24/2007')Thanks for your helpPablo
View 3 Replies
View Related
Apr 26, 2002
Take the following scenario:
We have two tables that have somewhat of a parent-child relationship. We are trying to use a SQL-92 outer join that returns the same results as a TSQL *= outer join. The difficulty we are having is that some of the parent records do not have any corresponding child records, but we still want to see those parent records with 0 (zero) for the count. How can we accomplish this with a SQL-92 compliant join (if it is even possible)? In the query results below, we would like the first set of results.
Thanks in advance for any help.
-David Edelman
Test script below, followed by results
===========================================
create table parent (p_id int NOT NULL)
go
create table child (p_id int NOT NULL, c_type varchar(6) NULL)
go
insert parent values (1)
insert parent values (2)
insert parent values (3)
insert parent values (4)
insert parent values (5)
insert parent values (6)
insert parent values (7)
insert parent values (8)
insert parent values (9)
insert parent values (10)
go
insert child values (1, 'AAA')
insert child values (1, 'BBB')
insert child values (1, 'CCC')
insert child values (2, 'AAA')
insert child values (4, 'AAA')
insert child values (4, 'DEF')
insert child values (4, 'AAA')
insert child values (4, 'BBB')
insert child values (5, 'AAA')
insert child values (5, 'AAA')
insert child values (6, 'AAA')
insert child values (7, 'AAA')
insert child values (7, 'BBB')
insert child values (7, 'CCC')
insert child values (7, 'DDD')
insert child values (10, 'AAA')
insert child values (10, 'CCC')
go
select p.p_id, count(c.p_id) as num_rows
from parent p, child c
where p.p_id *= c.p_id
and c.c_type in ('AAA', 'BBB')
group by p.p_id
select p.p_id, count(c.p_id) as num_rows
from parent p left outer join child c on p.p_id = c.p_id
where c.c_type in ('AAA', 'BBB')
group by p.p_id
=========================================
Results:
(T-SQL *= outer join)
p_id num_rows
----------- -----------
1 2
2 1
3 0
4 3
5 2
6 1
7 2
8 0
9 0
10 1
(SQL-92 outer join)
Warning: Null value eliminated from aggregate.
p_id num_rows
----------- -----------
1 2
2 1
4 3
5 2
6 1
7 2
10 1
View 1 Replies
View Related
Mar 15, 2006
I'm new to ms-sqlserver ( 2000 ) and need to get an OUTER JOIN workingon a three table query.Assumptions:-- I have events in the Event table.-- Each event CAN have one Transaction, but it's not guaranteed-- Each transaction, ir present, will have one or more Amount recordsThis would be the pseudo-query without any special joins:-----------------------------------------SELECTa.Name,SUM( c.amount ) as TotalFROMEvent a,Transaction b,Amounts cWHEREa.EventID = b.EventIDANDb.TransID = c.TransID-----------------------------------------This is fine if there is a Transaction for the Event. But, if there'sno transaction for an event, no record is pulled of course.What I need is for a record to come back for each event regardless ofthe presence of a Transaction. If there's no transaction, then the"Total" column should be 0.How would I get an OUTER JOIN to work on this so that each Event gets arecord?TIA-BEP
View 4 Replies
View Related
Jun 17, 2015
I want to use multiple datasets in a table and wants to do the full outer join on the two datasets in the same table. For example, my two datasets are:
I want to display a ssrs table like:
Both the datasets are coming from different sources. So I cannot integrate them at sql query level.
View 4 Replies
View Related
Mar 3, 2008
I'm trying to convert this statement to use left outer joins so it will be 2005 compatible.
select
convert(varchar(8),a.order_no),
a.order_ext,
a.line_no,
c.part_no,
a.order_seq,
a.customer_code,
b.date_shipped,
0,
a.seq_descript
from fm_orderline a, orders b, ord_list c, fm_ordhist d
where c.order_no = a.order_no
and c.order_ext = a.order_ext
and c.line_no = a.line_no
and c.order_no *= d.order_no
and c.order_ext *= d.order_ext
and c.line_no *= d.line_no
and b.order_no = c.order_no
and b.ext = c.order_ext
and d.line_no <> 999
and c.status = 'T'
group by
d.order_no,
convert(varchar(8),a.order_no),
a.order_ext,
a.line_no,
c.part_no,
a.order_seq,
a.customer_code,
b.date_shipped,
a.seq_descript
having d.order_no is null
I've tried this:
select
convert(varchar(8),a.order_no) as order_no,
a.order_ext,
a.line_no,
c.part_no,
a.order_seq,
a.customer_code,
b.date_shipped,
0,
a.seq_descript
from fm_orderline a, orders b,
ord_list c
left outer join fm_ordhist AS d on (c.order_no = d.order_no)
left outer join fm_ordhist AS d2 ON (c.order_ext = d2.order_ext)
left outer join fm_ordhist AS d3 ON (c.line_no = d3.line_no)
where c.order_no = a.order_no
and c.order_ext = a.order_ext
and c.line_no = a.line_no
and b.order_no = c.order_no
and b.ext = c.order_ext
and d.line_no <> 999
and c.status = 'T'
And various derivations of it(adding d2.line_no <> 999 and d3.line_no <> 999 to the where clause.) But it came up with different results than what I received when it was a *= query.
Can any gurus on here help me out?
View 4 Replies
View Related
May 10, 2010
what is difference between outer apply and outer join ,both return rows from left input as well as right input . isnt it?
View 3 Replies
View Related
Apr 30, 2008
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
View 1 Replies
View Related
Nov 4, 2003
Hello
I've a table with these values:
Cod_Lingua - Des_Lingua
------------------------------
ITA Italian
GER German
ENG English
FRA French
and another table with product/description
ProductID - Cod_Lingua - Description
-------------------------------------------
1 ITA Mia Descrizione
1 ENG My Description
I've this SELECT:
SELECT Tab_Lingue.Cod_Lingua, Descrizioni_Lingua.Description
FROM Descrizioni_Lingua RIGHT OUTER JOIN Tab_Lingue ON Tab_Lingue.Cod_Lingua=Descrizioni_Lingua.Cod_Lingua
WHERE Descrizioni_Lingua.ProductID=1
I get these results:
ITA - Mia Descrizione
ENG - My Description
I don't want this. I'd like to have this:
ITA - Mia Descrizione
ENG - My Description
GER - (null)
FRA - (null)
How can I get the second result set?
Thanks for your support.
View 3 Replies
View Related
May 23, 2008
Hello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services?
Thanks
View 3 Replies
View Related
Oct 8, 2015
I was writing a query using both left outer join and inner join. And the query was ....
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
INNER JOIN Production.Categories AS C
[code]....
However ,the result that i got was correct.But when i did the same query using the left outer join in both the cases
i.e..
SELECT
S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';
The result i got was same,i.e
supplier country productid productname unitprice categorynameSupplier QOVFD Japan 9 Product AOZBW 97.00 Meat/PoultrySupplier QOVFD Japan 10 Product YHXGE 31.00 SeafoodSupplier QOVFD Japan 74 Product BKAZJ 10.00 ProduceSupplier QWUSF Japan 13 Product POXFU 6.00 SeafoodSupplier QWUSF Japan 14 Product PWCJB 23.25 ProduceSupplier QWUSF Japan 15 Product KSZOI 15.50 CondimentsSupplier XYZ Japan NULL NULL NULL NULLSupplier XYZ Japan NULL NULL NULL NULL
and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.
View 5 Replies
View Related
Aug 10, 2007
Scenario:
OLEDB source 1
SELECT ...
,[MANUAL DCD ID] <-- this column set to sort order = 1
...
FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC
OLEDB source 2
SELECT ...
,[Bo Tkt Num] <-- this column set to sort order = 1
...
FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC
These two tasks are followed immediately by a MERGE JOIN
All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)
result of source1 (..dcd column)
...
4-400-8000119
4-400-8000120
4-400-8000121
4-400-8000122 <--row not joining
4-400-8000123
4-400-8000124
...
result of source2 (..tkt num column)
...
4-400-1000118
4-400-1000119
4-400-1000120
4-400-1000121
4-400-1000122 <--row not joining
4-400-1000123
4-400-1000124
4-400-1000125
...
All other rows are joining as expected.
Why is it failing for this one row?
View 1 Replies
View Related
Nov 13, 2011
When I run:
select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
scheme.opheadm.customer, qty_received
from scheme.opheadm join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%'))
where
effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'
It gives me the 5 rows that I need to work with, one column is customer (which is giving me customer code) that I want to replace with customer name from another table
So I tried:
select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
scheme.jcmastm.name, qty_received
from scheme.opheadm
join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%'))
join scheme.jcmastm on scheme.opheadm.customer = scheme.opheadm.customer
here
effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'
this works with the same 5 rows that i need but loops them through every customer from the table scheme.jcmastm giving me a total of 960 rows not just the 5 that i want to work with. why this is looping?
View 1 Replies
View Related
Feb 6, 2007
hi i user this join and i have the answer like this"
select u.userid,
u.user_name,
u.password,
c.code_description as role_code,
convert(varchar, u.expiry_date,101) as expiry_date,
u.created_date,
u.active
from [usermaster] u inner join [codeMaster] c
on 'SP'=c.code
where u.userid = '2'
result:
userid user_name password role_code expiry_date
2billgatesbill Supervisor02/06/2007
created_date active
2007-02-06 00:00:00.000 0
so i have to join one more table which has the following records;
select * from HRUser_developerlog
result:
insserted_id user_date table operation userid
101/24/2007 11:47:54 AM usermasterinsert1
11/24/2007 1:02:18 PM usermasterinsert1
111/25/2007 9:26:12 AM usermasterinsert1
122/5/2007 9:56:48 AM usermasterupdate1
122/5/2007 10:23:01 AM usermasterinsert1
122/5/2007 10:23:38 AM usermasterupdate1
122/5/2007 4:10:11 PM usermasterupdate1
22/6/2007 8:53:37 AM usermasterinsert1
22/6/2007 9:48:24 AM usermasterdelete1
so i need to take the user_date using inserted_id and operation so i need the output as follows:
userid user_name password role_code expiry_date user_date
2billgatesbill Supervisor02/06/2007 2/6/2007 9:48:24 AM
for this i tried the following query:
select u.userid,
u.user_name,
u.password,
c.code_description as role_code,
convert(varchar, u.expiry_date,101) as expiry_date,
u.created_date,
u.active,
v.user_date
from [usermaster] u inner join [codeMaster] c inner join [HRUser_developerlog] v
on 'SP'=c.code or u.userid=v.inserted_id and v.operation='delete'
where u.userid = '2'
but i am getting error.can any onre please help me and please give me query please
View 2 Replies
View Related
Aug 4, 2006
Hi,
I have two tables, let's say "Main" and "Dictionary".
The Main table has several fields that point to records in the same dictionary table. Because of the multiple joins I couldn't get any results if I use an expression like:
SELECT Main.ID, Dictionary.Text AS Data1, Dictionary.Text AS Data2
FROM Main, Dictionary
WHERE Main.Data1 = Dictionary.ID AND Main.Data2 = Dictionary.ID
What kind of join expression should I use? I have to generate this expression programmatically, so it's quite important to keep it as simple as possible!
Thx!
Örs
View 6 Replies
View Related
Oct 13, 2006
I'm trying to join 3 tables:
EMPLOYEE - empid
SKILL - empid, skillid, skill
SKILLOPTIONS - skillid, option
An EMPLOYEE will always have at least 1 SKILL but each SKILL may or may not have any SKILLOPTIONS. I do an INNER JOIN:
EMPLOYEE->SKILL->SKILLOPTIONS but I only get a record if there is actually a SKILLOPTION. I want a record with EMPLOYEE and SKILL even if there are no SKILLOPTIONS. In Oracle it is the (+) symbol in the WHERE statement in conjunction with the JOIN. Am new to this so I'm sure the answer is simple.
View 2 Replies
View Related
Jun 5, 2006
I have a merge join (full outer join) task in a data flow. The left input comes from a flat file source and then a script transformation which does some custom grouping. The right input comes from an oledb source. The script transformation output is asynchronous (SynchronousInputID=0). The left input has many more rows (200,000+) than the right input (2,500). I run it from VS 2005 by right-click/execute on the data flow task. The merge join remains yellow and the task never finishes. I do see a row count above the flat file destination that reaches a certain number and seems to get stuck there. When I test with a smaller file on the left it works OK. Any suggestions?
View 3 Replies
View Related
Jul 24, 2012
I have three tables, Users, DocType and Docs. In the DocType table there are multiple entries for allowed document types, the descriptions and other pertinent data. In the Docs table, there are all manner of documents. In the User table are the users.
The DocType and Docs tables are relational. DocType.ID = Docs.tID
The Users and Docs tables are relational. Users.ID = Docs.uID
Every user is allowed to have exactly one document of each type. Therefore if there are 10 document types in the DocType table, there may be as many as 10 matching documents in the Docs table.
What I need is a single record for each user returning a boolean for each document type, whether or not there is a matching record in the Docs table.
For example, there are 5 document types defined in the DocType table (types 1 - 5), so the DocType table has 5 rows. In the Docs table, there are 23 rows, and in the User table there are 10 rows. Given that each user may have only one of each DocType, there could be a maximum of 50 rows in the Docs table, but there are 23, meaning that on the average each user is missing one document.Now the challenge is to return a table of all the users (10 rows) with a boolean value for each of the rows in DocType (as columns) based on whether there is a value in the Docs table that matches both the DocType and User.
View 2 Replies
View Related
May 12, 2014
I have 3 tables , Customer , Sales Cost Charge and Sales Price , i have join the customer table to the sales price table with a left outer join into a new table.
i now need to join the data in the new table to sales cost charge. However please note that there is data that is in the sales price table that is not in the sales cost charge table and there is data in the sales cost charge table that is not in the sales price table ,but i need to get all the data. e.g. if on our application it shows 15 records , the sales price table will maybe have 7 records and the sales cost charge table will have 8 which makes it 15 records
I am struggling to match the records , i have also tried a left outer join to the sales cost charge table however i only get the 7 records which is in the sales price table. see code below
SELECT
a.[No_],
a.[Name],
a.[Currency Code],
a.[Salesperson Code],
b.[Sales Code],
[code]....
View 4 Replies
View Related
Oct 14, 2013
I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.
Here is table a:
naics ust_code port all_qty_1_yr all_qty_2_yr all_val_yr all_air_val_yr all_air_wgt_yr all_ves_val_yr all_ves_wgt_yr all_cnt_val_yr all_cnt_wgt_yr all_border_val_yr
11111000 2010 2002 8070569.14298579 0 2335641254.30021 0 0 2335641254.30021 8156408492.66667 0 0 0
11111000 2230 2010 280841.478063446 0 84622385.9133129 0 0 84622385.9133129 299600780.773355 0 0 0
11111000 2410 1401 25735 0 12305667 0 0 12305667 25719794 0 0 0
[Code] ....
and here is table b:
naics ust_code port stat_month Cum_qty_1_mo Cum_qty_2_mo Cum_all_val_mo Cum_air_val_mo Cum_air_wgt_mo Cum_ves_val_mo
11111000 1220 0106 01 2 0 3440 0 0 0
11111000 1220 0107 03 14 0 3442 0 0 0
11111000 1220 0108 09 0 0 0 0 0 0
[Code] ....
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’
/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]
[Code] ....
Output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like
* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)
View 1 Replies
View Related
Sep 22, 2014
I am trying to pull in columns from multiple tables but am getting an error when I run the code:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.BOC" could not be bound.
I am guessing that my syntax is completely off.
SELECT
b.[PBCat]
,c.[VISN] --- I am trying to pull in the Column [VISN] from the Table [DIMSTA]. Current Status: --Failure
,a.[Station]
,a.[Facility]
,a.[CC]
,a.[Office]
[Code] ....
View 2 Replies
View Related
