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
ADVERTISEMENT
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',Â
@profile_name =
'SQL SMTP',
  Â
[code]....
View 3 Replies
View Related
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
Aug 18, 2015
I am tying to join tables to get the result but it is not showing any data,i have shipping address column in both tables I want to show data in single column I don't know how to display.
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress ,rs.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
inner join
ReSaleorReStock1Table rs on r1.ProductID=rs.ProductID
where r1.HazMat='No' order by ProductID
If I join two tables it is showing data
select r1.ProductID,r1.ProductName,r1.PMNO ,r.ShippingInfo,r.ShippingAddress from R2InventoryTable r1 inner join RecycleComponents1Table r on r1.ProductID=r.ProductID
where r1.HazMat='No' order by ProductID
View 9 Replies
View Related
Dec 3, 2015
I have 3 tables:
Â
TABLE [dbo].[Tbl_Products](
[Product_ID] [int] IDENTITY(1,1) NOT NULL,
[Product_Name] [nvarchar](50) NOT NULL,
[Catagory_ID] [int] NOT NULL,
[Entry_Date] [date] NOT NULL,
[Code] ....
I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )
But am getting a multiple result if the order purchase has more than 1 item :
SELECT DISTINCT B.Product_Name,A.AllPieceBoxes,
A.BuyPrice,A.TotalPrice,A.BuyPrice
FROM
Tbl_Products B INNER JOIN Tbl_PurchaseHeader C
ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A
ON A.PurchaseOrder=C.purchaseOrder
WHERE A.PurchaseOrder=3
View 5 Replies
View Related
Jun 23, 2015
Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.
Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?
Information: I'm using SQL server 2012
create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into datatable values
[Code] .....
View 2 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 13, 2015
I have to join two tables and i need to fetch All records from @tab2 and only max date record from @tab1 that ID is present in Tab2
1.) @Tab1 have multiple records for each ID
2.) @Tab2 also have multiple records for each ID
3.) Kind of Lef Outer join those tables with ID and take all records from @tab2 and only Max of date from @tab1 and order by ID and Date
Note: @Tab1 always have lesser dates than @tab2 for each ID
Tables looks like as followsÂ
declare @tab1 table (id varchar(3), effDt Date, rate int)
insert into @tab1 values ('101','2013-12-01',5)
insert into @tab1 values ('101','2013-12-02',2)
insert into @tab1 values ('101','2013-12-03',52)
[code]....
In the given ex, ID 103 should not come as it is not present in @tab2, ID 104 should come even it is not present in @tab1 as we ahve to use left outer join Result should like follows.
View 3 Replies
View Related
Jul 23, 2005
Hello group!i have a table "group_code" wich relates the names ofnt-(domain)-groups to codes. now i want use the stored procedurexp_logininfo (asking for the group-membership of the current user) tojoin the result to "group_code". then i must use the new result (thecode) to join against other tables.i know now, that i cant join results of SPs against tables. may be thata UDF with a table result is the correct approach. but i have no ideahow to wrap the xp_logininfo in a UDF.a other way can be to do in a UDF the same thing like the xp_logininfo.then this UDF should deliver at least the nt-(domain)-groups in wichthe current-user is a member.Is there anybody who can give me the code for that?many thanx in advance.Karl
View 1 Replies
View Related
Mar 24, 2002
Hi all,
I faced a problem, I have two tables - part and partmaster
part : part_no, part_qty (no key)
partmaster : part_no, part_description (primary key : part_no )
I want to select table part.* and partmaster.part_description.
(run on mssql 2k)
select a.*, b.part_description
from part a, partmaster b where a.part_no *= b.part_no
I want to and expect to have the result order like table "part". However, after the join, the order is different. I try to run it on mssql 7.0, the order is ok.
Then I modify and run the statement select a.* from part a, partmaster b where a.part_no *= b.part_no on 2k again. The result order is ok.
can anyone tell me the reason?
Now I try to fix this problem is adding a sequence field "part_seq" into table "part" and run the statement by adding a order by part_seq.
It does work!
Regards,
Simon
View 1 Replies
View Related
Feb 22, 2007
SELECT DISTINCT Image.ImageID, Image.JobID, Image.Filename, Tag.Name,
Tag.SortOrder, TagCat.name
FROM Image, JobTag, Tag, Job, Tagcat
WHERE Tag.TagID = JobTag.TagID
AND Image.JobID = Job.JobID
AND Job.JobID = JobTag.JobID
AND TagCat.TagCatID = Tag.TagCatID
ORDER BY Image.ImageID, Tag.SortOrder
I'd like to replace the query above with a some kind of a join, but I'm not sure how to do it. The reason I want to do this is to only get the category (TagCat.name) one time for each set of tags that go under that category for that particular job. Any thoughts on how I could do this? Would I need to create more than one query or do you think this can be one with just one query?
View 1 Replies
View Related
May 7, 2012
I am using SLQ Server 2008 R2. The database was designed by another company.
I have two tables: Client and Client_Location. In the Client table the pk is Client_ID. There is also a unique key: sys_Client_ID. Both the Client_ID and the sys_Client_ID fields exist as a foreign keys in the Client_Location table. However, the fields are not noted as unique in the Client_Location table. There are two fields in the Client_Location table that determine when the address was effective. They are from_dt and end_dt.
Multiple records have been loaded into the Client_Location table to track old as well as current addresses of clients.
I'm trying to run a report that will pull clients with a plan_id constraint from the Client table and join the Client_Location table to retrieve the current address of these clients.
My SQL is:
select distinct (a.client_id), a.cli_last AS Last_Name,
a.cli_first AS First_Name, a.cli_middle AS Mid_Init,
b.city AS City, b.county AS County, b.state AS State
from ECBH.dbo.tbl_Client a inner join ECBH.dbo.tbl_Client_Location b
on a.client_id = b.client_id
inner join ECBH.dbo.tbl_client_insurance c
on a.client_id = c.client_id
inner join ECBH_TEST.dbo.tbl_GEF_County d
on b.county = d.COUNTY_NAME
where c.plan_id = 4
order by a.cli_last, a.cli_first
Because multiple records exist in the Client_Location table, the result set has duplicates. How can I pull only the results where the from_dt is most recent?
View 5 Replies
View Related
Aug 27, 2014
So if an item is a bottle, it will have a unique BottleKey and Null for CaseKey and if an item is a Case, it will have a unique CaseKey and Null for BottleKey. However, the PrimaryIDs are the same for both the bottle and case. I get the results to look like this:
Bottlekey CaseKey PrimaryID
4754 NULL ABC-234
NULL 5465 ABC-234
.... .... .......
Is there a way to get the result sorted by Primary ID?
So that the rows are halved from what we have in the table above and the results look like
PrimaryID CaseKey BottleKey
ABC-234 5465 4754
View 1 Replies
View Related
Aug 25, 2005
Here is the situation
Table 1 : tbl_documents
docIDdocName
1aaa
2bbb
3ccc
Table 2 : tbl_Rating
ratIDratingdocID
131
251
321
432
The queary I need is to display the result in this form. must be like this
docIDdocName Avaragerating
1aaa3
2bbb3
3ccc0
NOTE : For getting the average I used this queary “SELECT SUM(rating) As
RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID =
tbl_documents.docID”
PLs help me ?
Thx
View 3 Replies
View Related
Jun 1, 2015
Below is the query for my procedureÂ
ALTER PROC [dbo].[sp_GetInvitationStatusTest]
(
@invited_by NVARCHAR (50)
)
AS
BEGIN
-- SELECT * FROM dbo.Merck_Acronym_Invitations WHERE invited_by=@invited_by
select distinct t1.invited_isid as 'ISID', t1.invited_name as 'NAME',t1.invitation_status as 'STATUS',
[Code] ....
If you look at the where clause i have invited by , i get the desired output if i just provide 1 name in the execution such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj' But my requirement is to make the procedure work with more than one input variable such as exec [dbo].[sp_GetInvitationStatusTest] 'marfilj','sujith' now i should get the output for 2 people but if i run this i receive the following error Procedure or function sp_GetInvitationStatusTest has too many arguments specified.
how to make my procedure work with more than 1 input variable?
View 4 Replies
View Related
Jun 7, 2015
Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do
exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for
exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'.
USE [merck_acronyms]
GO
[code]....
View 2 Replies
View Related
Apr 9, 2008
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
View 21 Replies
View Related
Jul 20, 2005
Hi,I have a table like this :TimeIDApplicationLoginState1App1login101App2login211App3login311App1login401App1login511App4login102App1login102App2login202App3login312App1login402App1login512App4login103App1login103App2login213App3login313App1login403App1login513App4login10And I want a result like thisTimeID (state=0) (state=1)1 3 32 4 23 3 3I use this code :set nocount ondeclare @timeid1 intdeclare @timeid2 intdeclare @timeid intdeclare @sessionstate intdeclare @count1 intdeclare @count2 intdeclare @count intdeclare @aux intdeclare txt_cursor cursor forselect timeid, sessionstate , count(login) from metromasterwhere sessionstate = 0group by timeid, sessionstateUNIONselect timeid, sessionstate , count(login) from metromasterwhere sessionstate = 1group by timeid, sessionstateorder by timeidopen txt_cursorselect @aux = 0fetch next from txt_cursor into @timeid, @sessionstate, @countwhile (@@fetch_status = 0)beginif @aux = 0beginselect @timeid1 = @timeidselect @count1 = @countselect @aux =1endelsebeginselect @timeid2 = @timeidselect @count2 = @countselect @aux = 2endif @aux = 2begin--select @timeid1, @count1, @sessionstate, @count2, @timeid2select @timeid1, @count1, @count2select @aux = 0endfetch next from txt_cursor into@timeid, @sessionstate, @countendclose txt_cursordeallocate txt_cursorset nocount offBut it create a lot of blank row and field header.Does anyone know an other methode ??Thanks.
View 1 Replies
View Related
Aug 15, 2015
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[Â Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:-
GO
ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details]
@Class_Id int
AS BEGIN
[code] ....
View 9 Replies
View Related
Apr 7, 2013
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp
Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
View 17 Replies
View Related
Jul 14, 2015
I have a table like this :
reg.no |Â Â description |Â start_date |Â end_date |Â Â load_date
I want to join this table with itself on reg. no. But not all the rows in table must be joined.
But for example, rows with load_date 01-07-2015 to be joined with rows with load_date 02-07-2015. And the rest of the rows should not used in join (for example, rows having other load_dates)
Is this possible ? and how?
View 4 Replies
View Related
Sep 10, 2015
I have the following 2 tables:
Location:
Policy:
1 Location can be attached to many policies.
I need to create a query to get 1 row per location and get the minimum PolicyBookingDt and RowUpdateDt from the policy table. All the attributes from the Location table should also be from the Policy that has the minimum PolicyBookingDt.
 So from the above example, i need to get the following:
Getting a little confused on how to create the syntax for this.
View 4 Replies
View Related
Sep 7, 2015
I have table A with colums ID and Product. I have table B with ID (same as table A) and revenue of product.
I need to update the field Product of table A with the revenue of table B.
I'm confuse with the update joining both tables. I tried this, but obviously has an error:
Update A set Product=B.Revenue where A.ID=B.ID
View 6 Replies
View Related
Jul 21, 2015
I create a script like below:
GO
SET ANSI_PADDING ON
GO
DECLARE
@ProjectIDÂ AS NVARCHAR(128),
@TableName AS NVARCHAR(128);
SET @ProjectID = N'EPA';
[code]....
The table created, but T-SQL created a table called @TableName.not like "EPA_SweetChargeCodeAssignees'
how to debug?
View 2 Replies
View Related
Aug 7, 2015
I would like to compare values in the same table and get the single record with different values in the multiple columns.For table tab1, ID is my key column. If type1 is active (A) then i need to update X else blank on Code1 column and if type2 is active (A) then i need to update X else blank on code2 column. Both type1 and type2 comes from same table for same ID..Below is the example to understand my scenario clearly....
declare @tab1 table (ID varchar(20), dt date, status varchar(1), type varchar(10))
insert into @tab1 values ('55A', '2015-07-30', 'A', 'type1')
insert into @tab1 values ('55A', '2015-07-30', 'C', 'type2')
insert into @tab1 values ('55B', '2015-07-30', 'C', 'type1')
insert into @tab1 values ('55B', '2015-07-30', 'A', 'type2')
[code]....
View 4 Replies
View Related
Jun 30, 2015
I have two linked tables from two different databases, there is a column "product" on each table however the product on one table has a Prefix so not a direct match. How can I join these tables ? In the query I have used product2: Replace([scheme_pos.product],"-B","") then tried Joining on product2 but it says JOIN not supported.Â
View 2 Replies
View Related
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
View Related
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
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT Â
 PE.PropertyExternalReferenceValue  [BAReferenceNumber]
, PA.DescriptionCode
  [PSDCode]
, PV.ValuationEffectiveDate
  [EffectiveDate]
, PV.PropertyListAlterationDate
  [ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set  tables multiple timesÂ
View 11 Replies
View Related
Aug 4, 2015
I have a table called ADSCHL which contains the school_code as Primary key and other two table as
RGDEGR(common field as SCHOOl_code) and RGENRl( Original_school_code) which are refrencing the ADSCHL. if a school_code will be updated both the table RGDEGR (school_code) and RGERNL ( original_schoolcode) has to be updated as well. I have been provided a new data that i have imported to SQL server using SSIS with table name as TESTCEP which has a column name school_code. I have been assigned a task to update the old school_code vale ( ADSCHL) with new school_code ( TESTCEP) and make sure the changes happen across all 3 tables.
I tried using Merge Update function not sure if this is going to work.
Update dbo.ADSCHL
SET dbo.ADSCHL.SCHOOL_CODE = FD.SCHOOL_Code
FROM dbo.ADSCHL AD
INNER JOIN TESTCEP FD
ON AD.SCHOOL_NAME = FD.School_Name
View 10 Replies
View Related
Nov 3, 2015
I have a function which accepts table parmeter. i have created a view and i need to use this function.
How can i do it. how can i pass dim table into the @table variable
select * from dim cross apply fnc_user(@table variable)
View 4 Replies
View Related
Sep 13, 2015
I am still new to SQL and I am having trouble obtaining the results I need from a query. I have worked on this command for some time and searched the internet but cannot seem to still get it correct.
I have a table called Patient. It's primary key is pat_id.
I have a second table called Coverage. It has no primary key. The foreign keys are pat_id, coverage_plan_id, and hosp_status.
I have a third table called Coverage_History.  It has a primary key consisting of pat_id, hosp_status, copay_priority, and effective_from.Â
I want to get the pat_id and all the coverage information that is current.  The coverage table contains specific insurance policy information.  The coverage_history table will indicate the effective dates for the coverage. So the tables could contain something like this:
Patient (pat_id and lname)
P123Â Monto
P124Â Minto
P125Â Dento
P126Â Donto
Coverage (pat_id, coverage_plan_id, hosp_status, policy_num)
P123Â Â Â Â MED1Â Â OPÂ Â A1499
P123Â Â Â Â ACT4Â Â OPÂ Â H39BÂ
P124Â Â Â Â MED1Â Â OPÂ Â C90009
P124Â Â Â Â RACÂ Â Â OPÂ Â 99KKKK
P124Â Â Â Â RACÂ Â Â OPÂ Â 99KKKK
P124Â Â Â Â MED1Â Â OPÂ Â C90009
P125Â Â Â Â ARPÂ Â Â OPÂ Â G190
P126Â Â Â Â BCBÂ Â Â OPÂ Â H88
Coverage_History (pat_id, hosp_status, copay_priority, effective_from, coverage_plan_id, effective_to)
P123Â Â OPÂ Â 1Â Â 20150102Â MED1Â Â Â NULL
P123Â Â OPÂ Â 2Â Â 20150102Â ACT4Â Â Â NULL
P124Â Â OPÂ Â 1Â Â 20150203Â RACÂ Â Â Â 20150430
P124Â Â OPÂ Â 2Â Â 20150203Â MED1Â Â Â 20150430
P124Â Â OPÂ Â 1Â Â 20150501Â MED1Â Â Â NULL
P124Â Â OPÂ Â 2Â Â 20150501Â RACÂ Â Â Â NULL
P125Â Â OPÂ Â 1Â Â 20150801Â ARPÂ Â Â Â NULLÂ
P126Â Â OPÂ Â 1Â Â 20150801Â BCBÂ Â Â Â 20160101
select p.pat_id, p.lname, ch.coverage_plan_id, ch.hosp_status, ch.effective_from, ch.effective_to, ch.copay_priority,
      from patient p  Â
    left join
  ( coverage_history ch left join coverage c on ch.coverage_plan_id = c.coverage_plan_id and ch.patient_id = c.patient_id and
                                  (ch.effective_to is NULL or ch.effective_to >= getdate()
)
        ) on ch.patient_id = p.patient_id
       Â
   where ( ch.effective_to is NULL or ch.effective_to >= getdate() )
  Â
So I want to see:
P123 Monto MED1 OP  20150102   NULL      1
P123 Monto ACT4 OP  20150102   NULL      2
P124 Minto MED1 OP  20150501   NULL      1
P124 Minto RAC  OP  20150501   NULL      2
P125 Dento ARP  OP  20150801   NULL      1
P126 Donto BCB  OP  20150801   20160101   1
View 6 Replies
View Related
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