How To Join Tables And Get Desired Data...

May 16, 2005

I have a problem...I have two tables, patient_tran and patient_plan. Patient_tran has case_entry_date and patient_id,patient_plan has patient_id, plan_Id, plan_eff_date and plan_term_date. I need to join these two tables and get the patient_id, plan_id but the plan_id should be the plan_id where the entry_date falls between plan_eff_date and plan_term_date. If doesn't match the criteria then pick up the plan_id where the plan_term_date is null, if there's no null plan_term_date then pick up the plan_id with the most recent plan_term_date. In patient_plan table, there's could be more than one plan per patient. How can I do this? Can anyone please help, will be most appreciated.

Thanks in advance!!

View 6 Replies


ADVERTISEMENT

Join Not Getting Desired Result?

Jun 16, 2015

I have a right join query where i have a table where all employees attendance entry are registered and another table where pay master table where al employees details.

I need a query where i get all employee names from right side (pay master table) and the employees in out timings for all employees who punched or not punched their card.

means for selected date i need all the employees details suppose 10 employees who punched or not with empty values even he resigned.

I used right or left join it gives all dates value but when i select particular date i get only the punched details.

View 5 Replies View Related

Data In Desired Format

May 5, 2008

Query:
Select convert(CHAR(45),surname+','+fname) Name from beneficiary
Output from the above query:
Name
---------------------------------------------
BICKFORD ,ROSA
KOCH ,ERIC

I am desired Results as follows: Please help
Name
---------------
BICKFORD,ROSA
KOCH,ERIC

View 8 Replies View Related

Join Data From 2 Tables

Nov 22, 2007



I have 2 tables , one is Broker and Other is Booking, Booking table has 4 brokers codes as foreign keys with other columns, like

Booking

id Broker1 Broker2 Broker3 Broker4
1 100 101 102 101
2 101 102

brokers can bu null in booking (means no broker)

Brokers

BrokersCode BrokerName
100 John
101 muller
102 ABC

Now my requirement is to show broker Name (John, Muller, ABC) against their codes in table Booking.
i tried with left outer join, but this is not working after join in result it shows more rows in booking table as original.
(No of records in booking table should be same after join as before, the thing is only to show brokers names) .

Any help?
Thanks

View 4 Replies View Related

How To Insert Data Into Sql Table In Desired Format

Jan 25, 2008

 Hello,I have a problem the scenario is :I have data in an excel file and now I am reading data from that file and insert that data into sql database. this is well.but the problem is that I have few fields with date time data in excel sheet. In my database I have varchar type data type for these data columns.I want to read these data columns from the excel sheet and insert only time into the data base.how can I do this I am using the following line of code for selelcting only time from the excel file. string qry = "Select CONVERT(CHAR(5),datetime,114) from [" + objStr[0] + "];";this gives me error message.help me to read the data from excel file and insert it  into the sql table in desired format. Thanks in advance, junior 

View 1 Replies View Related

How To JOIN Tables To Produce Contiguous Data

Aug 6, 2015

I have a SQL database that has multiple tables I need to combine for an analytics presentation. The tables are PRODUCTS (contains product information and UPC), STORES (Store number, name and address), DATES (List of dates that data is published on), and DATA (quantity, by store, by UPC, by date). I am trying to combine these so that i have one table with a row for each UPC on each day at each store. Also, I would like to lookup values for year ago quantities at the same store for the same UPC. Is there a way to structure a query to provide all of this information? I know it will be a lot of rows, but I will filter by product prior to pulling the data.

I think the solution requires a subquery in the FROM portion of the statement, but I am not sure how to structure it. Here are the Fields:

Products.UPC, Data.UPC, Data.Store_Num, Stores.StoreNum, Data.StartDate, Dates.DateKey, and finally the reason we are here Data.Qty

I am using an Azure SQL Database.

View 2 Replies View Related

Join Tables With Almost Identical Column Data

Oct 29, 2014

Selecting the data from these two tables? The columns 'host_ext_id' have the same data BUT in 'adrmst' all data is preceeded by A0000.

table is 'adrmst'

Host External IDAddress NameAddress Line 1Address Line 2City StatePostal Code
A000042401 T-3803VC 1530401 00 WENGER STTOPEKA KS66609
A000042402 CO INC 960 PP TOMLIN MILL RDSTATESVILLENC286258332
A000042403 CO INC 1420 PP BLVD GARYSBURGNC278319748
A000042405 CO INC 1419 PP BROWN RD KISSIMMEEFL347463415
A000042405 CO INC 962 PP COMMERCE DRVALDOSTAGA316011206

table is 'shipment'

Shipment IDHost External IDcar_move_idP_DEST_LOC_ID
42401 42401
42402 42402 SDQD_00862TAGSDQD
42403 42403 SDQD_00863TAGSDQD
42404 42404 SDQD_00863TAGSDQD
42405 42405 SDQD_00863TAGSDQD

View 4 Replies View Related

Unable To Join Two Tables Together On Same Field Except Different Data Types

Sep 30, 2013

I am trying to join two tables together, on the same field except they have different data types, see the properties below

Code:
TableCOLUMN_NAMEDATA_TYPECHARACTER MAXIMUM LENGTHCHARACTER OCTET LENGTHCHARACTER SET NAMECOLLATION NAME
1itemClassnvarchar 512 1024 UNICODE Latin1_General_CI_AI
2PGCode varchar 3 3 iso_1 Latin1_General_CI_AS
in the code for the join,

Code:
left join common.dbo.qryPRDGroupDets on CAST(qryData_GB1_ByColumn.itemclass as varchar(3)) = Cast(common.dbo.qryPRDGroupDets.PGCode as varchar(3))

I have tried using the CAST function on one side of the join then on both, to no avail...

View 6 Replies View Related

SQL Server 2008 :: Join Two Tables With Specific Data

Jul 15, 2015

I have two queries from two different tables ex ABC and BCD. For table ABC, according to my query, I got 11 records ; for table BCD I only got 9 records.

Bottom line: I would like to see only 11 records from Table ABC including certain data from table BCD after I joined this two tables.

However, no matter what I did I always got 99 records when I joined.

View 3 Replies View Related

Pulling Data Back From 2 Tables - Join On Top 1 Record

Nov 7, 2013

I have sql pulling back data from 2 tables (Ticket, Assignment) matching on an ID.

however the Assignment table can have more than 1 record for a matching ID in the Ticket table so is bringing back rows for each of these entries HOWEVER i only want 1 row returned matching on the FIRST record in Assignment table (on earliest DateAssigned field)

Here's my code

SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t
INNER JOIN Assignment a ON (SELECT TOP 1 TicketID FROM Assignment
WHERE [TicketID] = t.TicketID
ORDER BY DateAssigned ASC) = t.TicketID

WHERE (CreatedByUserID NOT IN (SELECT u.UserID FROM Users u
INNER JOIN Profiles p ON u.UserID = p.UserID

[Code] ....

View 4 Replies View Related

Transact SQL :: Join Tables To Get Result - No Data Showing

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

Join Two Tables And Only Return The Latest Data For The Child Table

Sep 24, 2007

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,

View 4 Replies View Related

How To Join 3 Tables Using Left Or Right Join Keyword?

Aug 17, 2007

Hi guys,

I'll appreciate any help with the following problem:

I need to retrieve data from 3 tables. 2 master tables and 1 transaction table.

1. Master table TBLOC contain 2 records :
rcd 1. S01
rcd 2. S02

2. Master table TBCODE contain 5 records:

rcd 1. C1
rcd 2. C2
rcd 3. C3
rcd 4. C4
rcd 5. C5

3. Transaction table TBITEM contain 4 records which link to 2 master table:
rcd 1. S01, C1, CAR

rcd 2. S01, C4, TOY
rcd 3. S01, C5, KEY
rcd 4. S02, C2, CAR



I use Left Join & Right Join to retrieve result below (using non-ASNI method) but it doesn't work.

Right Join method:


SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A RIGHT JOIN TBCODE B ON A.CODE = B.CODE

RIGHT JOIN TBLOC C ON A.LOC = C.LOC

GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE



When I use Non-ASNI method it work:



SELECT C.LOC, B.CODE, A.ITEM FROM TBITEM A, TBCODE B, TBLOC C

WHERE A.CODE =* B.CODE AND A.LOC =* C.LOC

GROUP BY C.LOC, B.CODE, A.ITEM ORDER BY C.LOC, B.CODE

Result:

LOC CODE ITEM
-----------------------------
S01 C1 NULL
S01 C2 NULL
S01 C3 CAR
S01 C4 TOY
S01 C5 KEY
S02 C1 NULL
S02 C2 CAR
S02 C3 NULL
S02 C4 NULL
S02 C5 NULL


Please Help.

Thanks.






View 3 Replies View Related

Data Warehousing :: Copy Data From Staging Tables To Other Instance Master Tables?

Aug 14, 2015

I need to copy data from warehouse tables to master tables of different SQL instances. Refresh need to done once in an hour. What is the best way to do this? SQL agent jobs or SSIS packages?

View 3 Replies View Related

How To Get Desired Row Number

Apr 18, 2015

I have a question with a row number. Below I have 2 columns that I want to Put a row number with.

ID. Quantity DesiredRowNumber

123 1. 1
123. 1 1
123. 1. 1
123. 3 2
123. 3. 2

[code]...

How would I get the desired row number.

View 1 Replies View Related

Help Me To Get Desired Output

Mar 8, 2007

Dear Friends, I've created one table, with the following attributes.
everything is ok, but while retrieving data, the question numbers should be starting from 1,2.......... but i'm not getting that result. please suggest me to the output like that.

--create table question_code_level (qno int,code varchar(10),level varchar(10),question varchar(500),option1 varchar(200),option2 varchar(200),option3 varchar(200),option4 varchar(200),option5 varchar(200))

select * from question_code_level

1htmlbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
2c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
3c#expertwhat is clr?somethingsomethingsomethingsomethingsomething
4c#intermediawhat is clr?somethingsomethingsomethingsomethingsomething
5c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
6vbbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
7htmlexpertwhat is clr?somethingsomethingsomethingsomethingsomething
8sqlserverintermediawhat is clr?somethingsomethingsomethingsomethingsomething
10oraclebeginerwhat is clr?somethingsomethingsomethingsomethingsomething
11javabeginerwhat is clr?somethingsomethingsomethingsomethingsomething
12javaexpertwhat is clr?somethingsomethingsomethingsomethingsomething
13sqlserverbeginerwhat is clr?somethingsomethingsomethingsomethingsomething

--select * from question_code_level where code='c#' and level='beginer'

2c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
5c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething



Vinod

View 7 Replies View Related

Query Help Desired!

Mar 25, 2008

Hi all,

The query below, as it stands, does a search for strings like 'Fema', that exist in the word 'Female', and returns 'female'. Now, if the word 'Acc' stands on it's own, I want to know how to return it as 'Accessories' in the result set. And yes, I've tried the obvious (When 'Acc' Then 'Accessories') but that returns a Null instead of 'Accessories'!!

Here's the query:

SELECT CategoryID, Category, CASE substring(category, 1, 4)

when 'fema' then 'female' WHEN 'Male' THEN 'Male' WHEN 'Gift' THEN 'Female' END AS CategoryGroup

FROM dbo.InventoryCategory


Thanks all,



Jaybee.

View 4 Replies View Related

Desired Results In One Row

May 6, 2008

Hello,
I am trying to get results in one row from the following function but all records does not come from the following function.
I have 9 records of the same empid but results not showing all records. Can anybody help me to get all records.

CREATE FUNCTION dbo.GetBenefString5
(
@Empid INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @ret VARCHAR(8000)
SELECT @ret = ''
SELECT
@ret = @ret + CASE WHEN LEN(@ret) > 0 THEN ',' ELSE '' END + FName + ' ' + Benefittype + ' ' + BenefitPercentage
From Beneficiary

Where Empid = @Empid
RETURN @ret
END

SELECT
Empid,
dbo.GetBenefString5(Empid)
FROM pf25eaton_work.dbo.eaton_chr_benef_05052008
where EmployeeNumber='4500498'
GROUP BY Empid


Result from the above function query:
Empid ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1773 CHARLENE OLIF 33 ,CHARLENE EADD 33 ,CHARLENE ELIF 33 ,TIMOTHY EADD 33 ,TIMOTHY

(


Records are in the table:
FName Benefittype BenefitPercentage
---------------------------------------- ----------- -----------------
CHARLENE OLIF 33
CHARLENE EADD 33
CHARLENE ELIF 33
TIMOTHY EADD 33
TIMOTHY ELIF 33
BRADLEY ELIF 33
TIMOTHY OLIF 33
BRADLEY OLIF 33
BRADLEY EADD 33

Desired Result in one row:

Charlene OLIF 33, Charlene EADD 33, Charlene ELIF 33, TIMOTHY OLIF 33, TIMOTHY EADD 33, TIMOTHY ELIF 33, BRADLEY OLIF 33, BRADLEY EADD 33, BRADLEY ELIF 33

View 10 Replies View Related

JOIN TABLES...help!

Dec 21, 2006

hi,
this are my tables...






student




stud_num
pk2
pk3
stud_group

1234
11        
22   
1

2147
88        
66   
2

2222
22        
22   
2

5432
55        
44   
1

9876
99        
77   
1






student1






stud_num
pk2
pk3
age
grade
pass

1234
11        
22   
21
77
0

5432
55        
44   
23
90
1

9876
99        
77   
23
90
1






student2
 
 
 
 
 

stud_num
pk2
pk3
age
grade
pass

2147
88        
66   
18
78
0

2222
22        
22   
15
90
1
and this is what I want to retrieve from these 3 tables






stud_num
pk2
pk3
age

5432
55        
44   
23

9876
99        
77   
23

2222
22        
22   
15
this my query....
SELECT          student.stud_num, student.pk2, student.pk3, student1.age,                       student2.age AS Expr1FROM            student INNER JOIN                      student1 ON student.stud_num = student1.stud_num AND                       student.pk2 = student1.pk2 AND student.pk3 = student1.pk3 INNER JOIN                      student2 ON student.stud_num = student2.stud_num AND                       student.pk2 = student2.pk2 AND student.pk3 = student2.pk3WHERE           (student1.grade = '90') AND (student1.pass = '1') AND (student2.grade = '90')                       AND (student2.pass = '1')
however...it doesnt return any results...
can anyone correct my code?
 
thanx!
 
Sheila 
 

View 5 Replies View Related

How To Join Two Tables

Jun 23, 2007

I have two tables in sql server database. Tables are
Authors
AuthorName varchar (primary key), AuthorImage varchar
Threads
ThreadID int Primary Key, ThreadAuthor varchar, ForumID int (Foreign Key), ThreadReplyID int, ThreadPostedDate int
I have this query:
"SELECT ThreadAuthor, ThreadSubject, ThreadPost, ThreadPostedDate , ForumID FROM Threads WHERE ThreadID = " + threadId + " OR ThreadReplyID = " + threadId + " ORDER BY ThreadPostedDate ASC"
but i want to display AuthorImage on my webpage. How to modify the existing query to get the Author's image also.
Plz help me out anyone there.
Thanks

View 3 Replies View Related

If I Want To Join Two Sql Tables ...

Aug 22, 2007

do they have to have a common primary key?

View 13 Replies View Related

How To Join This Tables

Sep 6, 2007

i've two tables.
such as tableA and table B
table A has a column named CreatedBY and table B the same column..
now i need to get only the rows which match with table A's createdby column..
suppose tableA has only 2 values. when i make the join with tableB it shows as 4 values

View 8 Replies View Related

A Join On Six Tables

Sep 7, 2007

I need to perform a join on six different tables to produce a report. The biggest join I have ever done has been four tables. The way I did that query was to join three tables and then to perform the final select on the three table join.
Can anyone give me some advice. Should I use the same approach? Or can I just continue to use the keyword INNER JOIN to perform the joins?
 

View 1 Replies View Related

Join Of Tables

Apr 11, 2008

How to join two tables in database(sqlserver2005)(i gave primary key for each table)

View 1 Replies View Related

What Happens When I Join Tables ?

Apr 22, 2006

I am curios about what really happens when I join two tables?

Does a SQL server  create a runtime table and write that table to disk is there is not sufficent space in RAM?

View 5 Replies View Related

Join Tables

Jun 11, 2001

I have three tables:

1) Orders - order_id, orderdate
2) ordersdetails - order_id, partnumber, qty
3) products - partnumber

I need an output in following format

Date Partnumber qty


Following query gives the desired results.

select left(orders.orderdate,11) as date, orderdetails.partnumber, SUM(orderdetails.qty) as total
from orderdetails, orders
where orderdetails.partnumber is not null
and orderdetails.order_id = orders.order_id
and orders.orderdate >= {ts'2001-05-01 00:00:00'} and orders.orderdate <= {ts'2001-05-31 23:59:59'}
group by orders.orderdate, orderdetails.partnumber
order by orders.orderdate, total



But I do not have part that have null orders. My goal is to get

1) Get all part numbers, qty in the month of May.
2) at the end attach all partnumbers that have null ordered values to see which one have no orders.

View 1 Replies View Related

Join Tables

May 10, 2000

I have tables (tab#1, tab#2, tab#3, tab#4) that made of the orginal table(tab#0)

What I want to do is to join all tables(tab#1 through tab#4) to tab#0.

Tables look like;

1)tab#0
key_id, code
2)tab#1
id, code#1
3)tab#2
id, code#2
4)tab#3
id, code#3
5)tab#4
id, code#4

Any idea?

View 1 Replies View Related

Join Tables

Jan 11, 2001

Hi,

I have 2 tables. One with col1,col2 and second one with
col1,col2,col3. My requirement is to compare col1,col2
of t1 with t2 and update only changed records in t2.
How to implement? Please advise.

Thanks

Sam

View 1 Replies View Related

Join 3 Tables

Jul 21, 2006

Thank you, Thank you for reading my question!!! :)


I have 3 tables that need to be joined.
(Not necessarily needed info but might help: using Coldfusion with ODBC to Foxpro database)

Table 1: Timecard
--------
loginname date wrknum time
----------- --------- --------- ------
afrank 7/17/06 1 3
afrank 7/17/06 2 2
afrank 7/17/06 3 3
afrank 5/20/06 1 3
rjohn 7/17/06 1 3
.....

Table 2: Defaults
-----------
loginname wrknum
----------- ---------
afrank 1
afrank 2
afrank 3
afrank 4
afrank 5
....

Table 3: Workorders
wrknum description
---------- ---------------
1 descrip1
2 descrip2
.....


The result needs to be for the user afrank, date=7/20
and would look like:

Resultant Table:
loginname wrknum time description
----------- ---------- ------ ------------
afrank 1 3 descrip1
afrank 2 2 descrip2
afrank 3 3 descrip3
afrank 4 NULL descrip4
afrank 5 NULL descrip5


I've tried an outer left join but cannot get the right data. Basically everything from the timecard table and the leftovers from the default table with descriptions for all.

View 3 Replies View Related

Join Two Tables Using Sum And Max

Nov 30, 2004

I've got two tables, one called clientsharedeals and the clientorderdeals. In the first table, I have four fields (Rundate, Accno, Dealid, Nominal) that I need to sum(Nominal), grouping by dealid.

Once I've done this, I need to join to clientorderdeals, also having the same fields plus one extra (Rundate, Accno, Dealid, Nominal and Dealseq). Because of Dealseq, I can have more than one row in the table, matching (Rundate, Accno, Dealid, Nominal) of the first table. However, Dealseq increments, so I need to select max(Dealseq).

My query is doubling up on nominal because in my select statement, I am only using one account number, so I know what the value is for nominal and there are two rows in clientorderdeals - and it is not selecting max(dealseq) but both.

Can someone please cast some pearls my way ?

Thanks

View 1 Replies View Related

Join Tables Help

Dec 20, 2004

Could somebody please explain to me how do we join a table onto itself as that is what I was advised to do but I can't quite get where I want to go.

What I want to do is list values from a table,but those values can be just a quote (what would cost if they decided to go for that option) or those values can represent what was spent and invoiced, what is confusing me is that all of that gets saved in the same table and in same columns, so what was quoted for example for AirFares and what was spent gets saved in the same record but when it is "quoted amount" then ID = 1 but when it is invoiced ID = -1 and that is how we know what was quoted and what was invoiced.

But I need to split that one field into two columns one showing AirFareQuoted and one AirFareInvoiced and i have no idea how to achieve this.

I hope this makes sense and somebody can help me

View 14 Replies View Related

3 Tables To Join

Dec 28, 2004

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



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

Thanks...

View 2 Replies View Related

Join Two Tables From Different Dbs

Apr 28, 2006

Hi,

have to tables t1 and t2
t1 is in database db1
t2 is in db db2

Is it possible to join these two tables with an sql statement. or do i have to join my two datasets in my c#- Code

Greetz

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved