Multiple Join On Same Table

Jan 1, 2014

I am struggling with joining on the same table and cannot seem to get my head around the solution.

I have three tables as follows:

Patients Samples Results
UniqueID UniqueID
Surname LabNumber LabNumber
Forename (etc.) Sampled Code
Result

The code is the name of the test and the result is just that. Not all patients will have the same set of results. What I'd now like to do is pull out a CSV file of all results from 2013 including where the result is null. The format I am looking for is along the following lines:

Sex, Age, AKIN2, AKIN7, RIFLE,

The AKIN2, AKIN7 and RIFLE are the codes in the result table. I have tried OUTER JOINS but it seems to only pull out those records that exist (example below). I need to specify the result codes I am interested in otherwise the output could be enormous but this then does not pull out null values (a NULL value is an important as an actual value).

SELECT
Samples.Patient_ID AS ID,
Samples.LabNo As LabNo,
Patients.Sex As Sex,
DATEDIFF(year, Patients.DoB, Samples.Sampled) AS Age,
Samples.Source as Source,

[Code] ....

View 2 Replies

How To Search Multiple Table Which Table Name Is Store In Another Table And Join The Result Together?

Dec 1, 2006

I have one control table to store all related table name
Table ID                   TableName
     1                           TableA
     2                           TableB

In Table A:
RecordID                Value
     1                         1
     2                         2
     3                         3

In Table B:
RecordID             Value
    1                         1
    2                         2
    3                         3
How can I get the result by select the Table list first and then combine the data in table A and table B?

Thank you!

View 1 Replies View Related

Multiple Table Join

Oct 10, 2006

Ok, this is what I am trying to do:,
1) I am trying to get the number of employees that has completed all their
online training within 10 days of hire
2) All the employees that are has no exception(no pre-service training) and
has completed their checklist within 10 days
3) all the exception(pre-service) employees that has completed their
training within 70 days.

I have these tables, hipaa2006, hipaa101, hipaa201_inputs,
domesticviolence, securityawareness, securityawareness2006,
civilrights_input, and people_first_data.
People_first_data contains all the employees we have in our database, it
has empl_pfid col, but does not have last_modified col which all the other
tables has.

I have this queries:

1) select distinct(count(empl_pfid))
from people_first_data
left outer join tbl_domesticviolence on people_first_data.empl_pfid =
tbl_domesticviolence.employee_pfid
left outer join tbl_hipaa101 on
people_first_data.empl_pfid=tbl_hipaa101.employee_pfid
left outer join tbl_hipaa201_input on people_first_data.empl_pfid=
tbl_hipaa201_input.employee_pfid
left outer join tbl_hipaa2006 on
people_first_data.empl_pfid=tbl_hipaa2006.employee_pfid
left outer join tbl_securityawareness on
people_first_data.empl_pfid=tbl_securityawareness.employee_pfid
left outer join tbl_securityawareness2006 on people_first_data.empl_pfid=
tbl_securityawareness2006.employee_pfid
where people_first_data.empl_pfid = '639846'
and tbl_domesticviolence.last_modified is not null
and tbl_hipaa101.last_modified is not null
and tbl_hipaa201_input.last_modified is not null
and tbl_hipaa2006.last_modified is not null
and tbl_securityawareness.last_modified is not null
and tbl_securityawareness2006.last_modified is not null

2) ( I tried union, but i only wanted one number)
select count(last_modified) as date1 from tbl_hipaa2006
union
select count(last_modified) as date2 from tbl_hipaa101

Your help will be highly appreciated.

View 1 Replies View Related

Join Table Key To Multiple Table Names

Nov 5, 2004

Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:

I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.

That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.

Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.

Thx

View 12 Replies View Related

Using Outer Join In Multiple Table Query

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

SQL Server 2012 :: Multiple Level Join On Same Table?

Oct 10, 2015

i have below queries each select is fetching records at one level. Is there a way i can write single query to get to nth level (recursion) instead joining same table 10 times (i don't know in some cases there is may be next level) I stopped at 10th level now. In below example i gave only two levels.

SELECT Distinct
a.Col1 AS EmpID,
a.Col1 AS EmpID,
a.Col2 AS Emp_guid,
a.Col2 AS Emp_guid,
case

[code].....

View 1 Replies View Related

Returning Average Of Multiple Rows In A Table Join

Jul 23, 2005

Hi,I'm am looking for a little help. I need to create a SQL view whichjoins a few tables, and I need to return an average for a particularcolumn where a second duplicate ID exists...Heres an example of how the results could be returned...ID | Name | Order No. | Value---+------+-----------+---------5 | test | 1234 | 35 | test2| 1234 | 45 | test3| 1234 | 35 | void | 1235 | 55 | void2| 1235 | 65 | void3| 1235 | 55 | void4| 1235 | 7ID is my main join which joins the tablesName is a unique nameOrder No is the same for the different names, I only need to return onerow with this order no, and the first name (the rest are irrelevant)Value is the field which I wish to return as an average of all 3, 4 orhowever many rows is returned and share the same order no. This iswhere I get totally lost as I am pretty new to SQL. Can anyone provideany help on how I would go about limiting this query to the uniqueorder no's and returning the average of the value field, and I can takeit from there with my own tables.Thanks for your helpstr8

View 3 Replies View Related

SQL Server Max Record, Multiple Table Join Problems

Jul 20, 2005

Thanks in advance for you help.SQL Server 2000I have a complex join, but a simple example will do the trick so...table1 (placement records, possibly many per case,highest ID beingmost recent)----------placementID(pk) * CaseID(fk) * OrganizationID(fk) * Name * Numbertable2 (Organizations Table, Many Placements can be at 1 organization)----------OrganizationID(pk) * OrgName * OrgTypetable 3(Case Table, each Case can have many placements)----------CaseID(pk) * StaffName * CreationDatenow my query...SELECT t1.placementID,t1.caseID,t2.OrgNameFROM table1 as t1INNER JOIN table2 as t2 on t2.OrganizationID = t1.OrganizationIDWHERE exists (select distinct max(placementID),CaseIDfrom t1 group by caseID)GROUP BY t2.OrgName,t1.PlacementID,t1.CaseIDmy results-------------placementID CaseID OrgName1 1 oneOrg2 1 two org3 1 three org4 2 another org5 3 yet another orgmy desired results------------------PlacementID CaseID OrgName3 1 three org4 2 another org5 3 yet another orgas you can see i get all records, but what i want is to see only thelast placementID for each case so i dont want duplicate caseID but Ido need the orgName, and yes the query works correctly without the orgname but as soon as i add orgName to the select statement I getduplicate CaseID's, How do i eliminate duplicate CaseID's and get onlythe MAX(placementID) for each Case and the OrgNameplease advise, getting desperate.thanks again so much for the help

View 1 Replies View Related

Transact SQL :: Get Result On Two Table Join With Multiple Parameter?

Jun 1, 2015

I have a criteria where i want to join table 1 with table 2 , table 1 consists of products which were given to salesman to sell and table 2 has the sales data which salesman has sold out. Now i want to know left over products of each sales with join .Below is my data, here is what i am trying to do, but it return only salesman 1 data.

CREATE TABLE Salesman_Product
(
SalesManID int,
ProductID int
)
INSERT INTO Salesman_Product (SalesManID,ProductID) Values (1,1),(1,2),(1,3),(1,4)
INSERT INTO Salesman_Product (SalesManID,ProductID) Values (2,1),(2,2),(2,3),(2,4)

[code]....

View 6 Replies View Related

Reporting Services :: Using Multiple Datasets In A Table - Outer Join In SSRS

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

JOIN Multiple Tables From Multiple Databases

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

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

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

Multi-table JOIN Query With More Than One JOIN Statement

Apr 14, 2015

I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.

For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.

The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.

The number of rows returned should be the same as the number of rows in OrderDetails.

View 2 Replies View Related

JOIN With Multiple Where

Jun 20, 2008

View 5 Replies View Related

Multiple Join Queries?

Mar 3, 2008

I get a wo_ID and want to query company data. I have the following tables, work_orders (has wo_ID, wo_name and install_id), install_ids (has install_id, comp_id) and customers (comp_id, company_name). The query process goes like this: I get a wo_ID and I need to find the install_id that corresponds with that wo_ID in that same work_orders table. Then take that install_id and find out the comp_id that corresponds with that install_id from the install_ids table. Finally take that customer_id and link it to comp_id in the customers table to find out company data. I need to join it multiple times to get my final data. I have a simple one that works right now with one join and using the install_id, but I don't know how to expand it with multiple joins. Thanks.

View 4 Replies View Related

Multiple Join Clause

Feb 12, 2004

I have a table "Users" like this:

GroupId
CompanyId
UserId

I need to query the users getting the company's and group's names, but I only know how to join one table. Example:

Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name
From Users JOIN Groups ON Users.GroupId = Groups.Id

Hon can I add the companies table in the Join ?

Thanks,
Moshe

View 2 Replies View Related

Multiple Column Join

Aug 16, 1999

I have two tables, one of which is a key table with a subaccount number and a set of attributes that define that subaccount. I am trying to join this key table with the table with all the attributes and come up with one table of subaccounts. The Subaccounts should only lookup the attributes associated with them, not all of the attributes, so I put OR [attribute] IS NULL in the WHERE clause so it only matched on the appropriate columns. This worked great in an initial test with two attributes but when I put all 9 attributes in it crashed with this message "Msg 415, Level 16, State 1
The current query would require an index on a work table to be built with 15 keys. The maximum allowable number of keys is 16"
like this
CREATE VIEW subaccounts
(Subacct_no, balance)
AS SELECT
(s.Account_No + "." + r.Subacct_Ext, S.Balance)
FROM Acct_Rcd r, Subacct_Key s
WHERE
(s.attr1 = r.attr1 OR s.attr1 IS NULL)
AND (s.attr2 = r.attr2 OR s.attr2 IS NULL)

View 3 Replies View Related

INNER JOIN On On Multiple Columns

Jan 11, 2006

I'm trying to eliminate all records that do not have one of two conditions. I'm using INNER JOIN on a derived "table", not a table in my database. The code below summarizes what I'm trying to do. Please note that this is an extremely simplified query.

---------------------------

SELECT * FROM jobs
INNER JOIN
(
SELECT contact_id FROM contacts WHERE deleted = 0
)AS ValidContacts
ON (jobs.owner = ValidContacts.contact_id OR jobs.assignee = ValidContacts.contact_id)

---------------------------

This works fine when the the "SELECT contact_id FROM contacts WHERE deleted = 0" part returns a small number of records, however when that part returns a very large number of records, the query hangs and never completes. If I remove one of the conditions for the JOIN, it works fine, but I need both. Why doesn't this work?

Another possible solution is if I were to use "WHERE/IN" like this:

---------------------------

SELECT * FROM jobs
WHERE owner IN (SELECT contact_id FROM contacts WHERE deleted = 0)
OR assignee IN (SELECT contact_id FROM contacts WHERE deleted = 0)

---------------------------

This would work fine, but I don't want to have to run the "SELECT contact_id FROM contacts WHERE deleted = 0" part twice (since in my real code, it is much more complicated and performance is a big issue". Any help would be greatly appreceated.

I'm using SQL Server 2000 on Windows XP Pro.

View 6 Replies View Related

How To Join Multiple Tables

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

Join On Multiple Columns

Apr 13, 2012

I have 2 tables, Licenses and Prices as below

Licenses
User Tariff Licensetype
User1 Tariff1 License1
User2 Tariff2 License1
User3 Tariff1 License2
User4 Tariff2 License2
etc

Prices
Tariff LicenseType Cost
Tariff1 License1 £1
Tariff1 License2 £2
Tariff2 License1 £1.50
Tariff2 License2 £2.50

I need to create a query that will produce a table as follows:

User LicenseType Cost

I have tried the following:

Select Licenses.User, Licenses.LicenseType, Prices.Cost
from Licenses
inner join Prices
on Licenses.Tariff = Prices.Tariff
and Licenses.LicenseType = Prices.LicenseType

[Code] ....

But all three output multiple lines for each user.

View 1 Replies View Related

How To Join Multiple Tables

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

Multiple Join Between Two Tables

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

Join Multiple Tables

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

MULTIPLE LEFT JOIN Problem

Apr 4, 2006

I have a left Join problem - Appreciate any suggestions
This is the error message
C:InetpubwwwrootFTDecAdminFinishedAdminBeta2.aspx(47) : error BC30201: Expression expected. "tered] FROM [Colleges] "& _ ~ C:InetpubwwwrootFTDecAdminFinishedAdminBeta2.aspx(49) : error BC30035: Syntax error. " Left Join [PIDO] ON ([Colleges].[CollegeID] = ([PIDO].[CollegeID]) "& _ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ C:InetpubwwwrootFTDecAdminFinishedAdminBeta2.aspx(53) : error BC30451: Name 'queryString' is not declared. dbCommand.CommandText = queryString ~~~~~~~~~~~This is the Code
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
FROM [Colleges] "& _ '" Left Join [PIDO] ON ([Colleges].[CollegeID] = ([PIDO].[CollegeID]) "& _ " left join [GroupPA] ON ([Colleges].[CollegeID] = [GroupPA].[CollegeID])ORDER BY "& sortBy
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

View 5 Replies View Related

JOIN Performace Vs. Multiple RersultSets

Jun 13, 2006

Can anyone give me some guidelines as to when to chose JOINS over returning multiple resultsets in a strored procedure..
For eample, I have two tables, Orders and OrderDetails, which are linked by a primary key field. There can be orders w/o a corresponding record in orderdetails.
1.) I can return all orders and their details using a stored preocedure that has:
   SELECT o.order_id as OrderId, o.customername, od.order_id, od.orderdate FROM orders AS o LEFT OUTER JOIN orderdetails AS od ON (o.order_id=od.order_id)
2.) I can do the same by returning two results sets in a different stored procedure:
   SELECT order_id, customername FROM orders
   SELECT order_id, orderdate FROM orderdetails
I think the client processing time for the second option will be slightly less, because the resultset I need to filter will only be as big as the orederdetails table (it will not include records for orders that have no details). Regardless, I think this would only make for a small performance gain, so if Option 1 is better in Database performace, I would probably go with that.
I assume the method to choose also depends on table size and # of JOINS. Any guidance would be appreciated. Thanks,
Al

View 12 Replies View Related

Multiple Record Delete Via Join

Feb 25, 2008

Hi,

I have a table with a large number of records that I need to delete, before attempt to perform the delete I also archived the records to another table.

So I need to delete all of these selected records stored in the archive table from the main table. I can now reference all the records that qualify for the delete in the main table by performing a join on the archive table like so:

select * from my_main_table a
join my_archive_table b
on a.distinct_id=b.distinct_id
and a.surrogate_key=b.surrogate_key
and a.identifier=b.indentifier

So all the records check out to be the ones I'd like to perform a delete on but I just can't figure out how to perform a delete of the records with little or no change to the existing query.

Obviously something like this won't work:

delete from my_main_table a
join my_archive_table b
on a.distinct_id=b.distinct_id
and a.surrogate_key=b.surrogate_key
and a.identifier=b.indentifier

Though it would be nice if it did.:D

So my question is how would I use the existing query with some modification to delete only the records that this query returns. I've tried selection of records in the main table based on the existing records in the archive table but it can return a higher number of records than what I know is expected. I actually need the join specified to be in place to do it.

Can anyone render any assistance on this one???

I would certainly appreciate it.

Thanks.

View 1 Replies View Related

Join And Pivot Multiple Tables?

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

SQL 2012 :: Join Multiple Tables

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

Join Tables On Multiple Criteria

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

Join Data From Multiple Cubes

Aug 6, 2007

I am trying to build a report that access data from multiple cubes. Is it possible to join multiple cubes based on their shared dimensions? Or is it possible for report to display data from multiple cubes properly aligned based on the dimension.

Environment:
SQL Server Analysis Services 2005 and Reporting Services 2005

Example:
We have multiple cubes with “Year�? as a shared dimension and each one has different Rate info. The cubes have other set of shared dimensions which I am planning to set it up as a parameter. I would like to display the report as
Year
Rate1
Rate2
Rate3…

I really appreciate any suggestion.

Thanks
Haseeb

View 2 Replies View Related

Problem With A Join Due To Multiple Nulls

Mar 29, 2006

I want to join 2 tables by a unique ID field, but the ID field also hasmultiple NULLS which I do not want to ignore and I fear they will causeduplication.Using TableA and TableB below i will demonstrate the problem.TableATableA.ID Field1 Field21 Paul 1Null John 12 John 1TableBTableB.ID Field3 Field41 25 1Null 32 1Null 23 12 26 1The Table I want isTableA.ID TableB.ID Field1 Field2 Field3 Field41 1 Paul 1 25 12 2 John 1 26 1Null Null John 1 Null NullNull Null Null Null 32 1Null Null Null Null 26 1I think a select distcinct statement with a full outer join may do whatI want, but I'm not certain so want to check.Regards,Ciar�n

View 10 Replies View Related

Join Multiple Records Into One Field

May 4, 2006

Is there a way to create one field from multiple records using sql.For exampleTable 1John 18Peter 18David 18Now I want an sql query that when executed will return a field thatlooks like thisQuery1John Peter DavidSo basically it will return one record with all the name in one field

View 4 Replies View Related

Multiple Inputs In Merge Join?

Nov 16, 2007

hi guys,
just wondering if there's a SSIS component out there some what similar to Merge join but can take up more than two inputs to join.

basically i have a big package with data sources coming from everywhere, they all have a unique column i can join on, so right now, i use merge join for every two sources, then join the output of that to another source so on and so forth. it would be easier if i can just join all of the sources in one component rather than putting a merge join for every single join. is there such a component out there, custom built maybe?

cheers

View 6 Replies View Related