Join Between Two Tables Where Asset Tags Are Present

Dec 20, 2012

There are two tables A and B where asset tags are present, but in one table in rows and in another in column wise.

for eg
ASSet Tag
SR-062009-00032966
SR-062009-00032962
SR-072009-00020572
SR-072009-00020571
SR-072009-00020585
HH-092009-00038342

Table B
field 1 -->Asset TAG
Record 1-->SR-072009-00020572,SR-072009-00020571,SR-062009-00020685,SR-072009-00001592,SR-072009-00001376,SR-062009-00020683,SR-092009-00001617

field 2 --> Material code
REcord 1-->121
REcord 2-->123

What is the query so that asset tag of A matches with each and every asset tag table of B and output comes as

Output
Asset TAg -------- MAterial Code
SR-062009-00032966
SR-062009-00032962
SR-072009-00020572 ------121
SR-072009-00020571 -------121
SR-072009-00020585

View 5 Replies


ADVERTISEMENT

Full Text Search Indexing HTML - Does The Filter Expect Certain Tags To Be Present As Standard?

Jul 10, 2007

Hi, I was wondering if any SQL Server gurus out there could help me...I
have a table which contains text resources for my application. The text
resources are multi-lingual so I've read that if I add a html language
indicator meta tag e.g.<META NAME="MS.LOCALE" CONTENT="ES">and
store the text in a varbinary column with a supporting Document Type
column containing ".html" of varchar(5) then the full text index
service should be intelligent about the language word breakers it
applies when indexing the text. (I hope this is correct technique for
best multi-lingual support in a single table?)However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.DECLARE @SearchWord nvarchar(256)SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column (just plain text, no html).Does the filter used for full text indexing of html expect certain tags to be present as standard? E.g. <html> and <body> tags? At present the data I have stored might look like this (no html or body wrapping tags):Example record 1 data: <META NAME="MS.LOCALE" CONTENT="EN">Search for keywords:Example record 2 data: <META NAME="MS.LOCALE" CONTENT="EN">Sorry no results were found for your search.etc.Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.UPDATE: I have tried wrapping the text in more usual html tags and re-built the full text index but I still never get any rows returned for my query results. Example of content wrapping tried - <HTML><HEAD><META NAME="MS.LOCALE" CONTENT="EN"></HEAD><BODY>Test text.</BODY></HTML>I've also tried stripping all html tags from the content and set the Document Type column = .txt but I still get no rows returned?!? 

View 1 Replies View Related

Full Text Search Indexing HTML - Does The Filter Expect Certain Tags To Be Present As Standard?

Jul 11, 2007

Hi, I was wondering if any SQL Server gurus out there could help me...

I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.
<META NAME="MS.LOCALE" CONTENT="ES">
and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)

However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.
DECLARE @SearchWord nvarchar(256)
SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.
SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)

I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column (just plain text, no html).

Does the filter used for full text indexing of html expect certain tags to be present as standard? E.g. <html> and <body> tags? At present the data I have stored might look like this (no html or body wrapping tags):

Example record 1 data: <META NAME="MS.LOCALE" CONTENT="EN">Search for keywords:

Example record 2 data: <META NAME="MS.LOCALE" CONTENT="EN">Sorry no results were found for your search.

etc.

Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.

UPDATE: I have tried wrapping the text in more usual html tags and re-built the full text index but I still never get any rows returned for my query results. Example of content wrapping tried - <HTML><HEAD><META NAME="MS.LOCALE" CONTENT="EN"></HEAD><BODY>Test text.</BODY></HTML>

I've also tried stripping all html tags from the content and set the Document Type column = .txt but I still get no rows returned?!?

View 1 Replies View Related

How To Replace Div Tags With P Tags In A Column

May 6, 2015

I want to replace div tags with p tags in a column in sql.

<div style: bold> abc </abc>
<div> efgh></div>

required output:
<p>abc</p>
<p>efgh</p>

View 1 Replies View Related

SQL Server 2012 :: How To Get Primary Key Value If Not Present In Foreign Key Via Join

May 4, 2014

I've following query which display the result as required, but I need to get the missing Primary Key Values which are not available in the result:

SELECTA.SignedByUserID, B.FullName, COUNT(A.OutletID) AS TotalSignups, DATENAME(Month, A.SignupDate) AS Month
FROMdbo.tblMer_Outlet AS A LEFT OUTER JOIN dbo.tblGen_Users AS B ON A.SignedByUserID = B.UserID
WHERE(A.SignupDate >= '2014-04-01 00:00:00' AND A.SignupDate <= '2014-04-30 23:59:59')
GROUP BY A.SignedByUserID, B.FullName, DATENAME(Month, A.SignupDate)

This Query returns the following result:

SignedByUserID FullName TotalSignups Month
--------------------------------------------------------
9 Babu Raj 16 April
11 Faheem 19 April
39 Fasil Abbas 16 April
29 Hafiz Suleman 10 April

[code]....

which does not have a signup for the month of April, but I need it to be available for this or any upcoming month. I need this orr all users, which does not exists in the context needs to be displayed in the result.

View 9 Replies View Related

How To Write One Stored Procedure For A (same Column )present In 20 Tables

Mar 8, 2006

hi,i am a learner of ms -sql server 2000, i had a doubt in storedproceduressuppose i have a data base having 20 tables, all the tables have acolumn named--DATEcan we write a store procdure to find out the data ---i mean i wantthe data enteredbetween two days ---- if i call the stored procedure in any one of thetable i need to get the answerpls help me how to write the stored proceduresatishkumar.g

View 2 Replies View Related

Stored Procedure To Generate Custom ID For Each Asset

Jan 7, 2012

I need creating a store procedure which generates custom IDs for each asset. I am programming for Fixed Assets in VB6 with SQL Server 2005. For example, when a new Asset is added ,I need to auto generate the ID based on existing IDs. New ID should not exist in tblAssets table.

Table Name : tblAssets
Fields : AssetID > Int,Primary Key,this is internal ID (identity seed)
AssetExtID >nvarchar(50),this is external ID, need to generate/user entered.

Below is the example of data in tblAssets :

AssetID AssetExtID ProjectID ItemName Qty UOM UnitCost .....
1 PROSP-00001 PROSPERITY SPLIT-AC 2 NOS $200
2 PROSP-00002 PROSPERITY LAPTOP 1 NOS $500
3 UNIII-00001 UNION III LAPTOP 5 NOS $400
4 UNIII-00002 UNION III RECEIVER 2 NOS $312

The AssetExtID depends on the ProjectID which is in tblProjects.

I will take care of the first 5 characters to generate. But the number part I need to generate by checking existing data. The AssetExtID should not be duplicate. Its unique for each asset.

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

SQL Server 2008 :: Show Single Placement Dates As Start And End Date For Asset

May 24, 2015

I have a table called 'AssetPlacements' that shows the dates when certain objects (AssID) were placed at certain locations (LocID).

ID AssID LocID PlacementDate
1112015-05-01
2122015-05-06
3132015-05-09
4212015-05-03
5222015-05-07
6232015-05-11

I'd like to show the assets with a start date and end date for the placement of the asset.

The start date to be the placement date and the end date to be the next placement date of the asset.

Where there is no next placement date to then show the end date as the current date, so hopefully the table will show as the following.

ID AssID LocID StartDate EndDate
1112015-05-01 2015-05-06
2122015-05-06 2015-05-09
3132015-05-09 [GetDate()]
4212015-05-03 2015-05-07
5222015-05-07 2015-05-11
6232015-05-11 [GetDate()

I'm guessing some sort of recursion is required here to produce this.

View 7 Replies View Related

Setting Up Sharepoint Asset Inventory Tool On Windows Internal Database Server

Apr 4, 2008

I have a sharepoint on a windows internal database server (some version of sql express 2005). When I run the app, it says that I need SQL server reporting services 2005. I dont think that this component is bundled with "windows internal database server". Is there anyway I can download this component or otherwise make this work?

Seems a bit silly that they would make a tool requiring a component that doesnt need to be installed for sharepoint to run.

View 1 Replies View Related

Stripping .rtf Tags Out

Sep 25, 2007

Does anyone know how to get rid of rtf tags that are stored in the table? I need to filter out the data and wondering if there is a utility on the SQL Server that can do it.

View 16 Replies View Related

Strip Those RTF Tags Away

Sep 26, 2007

This algorithm can be used to strip out HTML tags too.
With reference to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89973
and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90000CREATE FUNCTIONdbo.fnParseRTF
(
@rtf VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE@Stage TABLE
(
Chr CHAR(1),
Pos INT
)

INSERT@Stage
(
Chr,
Pos
)
SELECTSUBSTRING(@rtf, Number, 1),
Number
FROMmaster..spt_values
WHEREType = 'p'
AND SUBSTRING(@rtf, Number, 1) IN ('{', '}')

DECLARE@Pos1 INT,
@Pos2 INT

SELECT@Pos1 = MIN(Pos),
@Pos2 = MAX(Pos)
FROM@Stage

DELETE
FROM@Stage
WHEREPos IN (@Pos1, @Pos2)

WHILE 1 = 1
BEGIN
SELECT TOP 1@Pos1 = s1.Pos,
@Pos2 = s2.Pos
FROM@Stage AS s1
INNER JOIN@Stage AS s2 ON s2.Pos > s1.Pos
WHEREs1.Chr = '{'
AND s2.Chr = '}'
ORDER BYs2.Pos - s1.Pos

IF @@ROWCOUNT = 0
BREAK

DELETE
FROM@Stage
WHEREPos IN (@Pos1, @Pos2)

UPDATE@Stage
SETPos = Pos - @Pos2 + @Pos1 - 1
WHEREPos > @Pos2

SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, '')
END

SET@Pos1 = PATINDEX('%cf[0123456789][0123456789 ]%', @rtf)

WHILE @Pos1 > 0
SELECT@Pos2 = CHARINDEX(' ', @rtf, @Pos1 + 1),
@rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, ''),
@Pos1 = PATINDEX('%cf[0123456789][0123456789 ]%', @rtf)

SELECT@rtf = REPLACE(@rtf, 'pard', ''),
@rtf = REPLACE(@rtf, 'par', ''),
@rtf = LEFT(@rtf, LEN(@rtf) - 1)

SELECT@rtf = REPLACE(@rtf, '0 ', ''),
@rtf = REPLACE(@rtf, ' ', '')

SELECT@rtf = STUFF(@rtf, 1, CHARINDEX(' ', @rtf), '')

RETURN@rtf
ENDE 12°55'05.25"
N 56°04'39.16"

View 10 Replies View Related

Cleaning Html Tags.

May 5, 2004

does any one has any sql server function that passes some text and returns a string without html tags.

example:

nice day
should return nice day

or if other html tags strip them off.


thanks for your help.

-Fr

View 2 Replies View Related

Remove Html Tags From A String!!!

Feb 13, 2008



I have a column of string which has html tags attached to it. How can I remove them..other than manually going and doing it? Any funtions?

Thanks!!

Tanya

View 9 Replies View Related

Sql Language Help To Generate Tags From Database

Oct 11, 2007

I have generated a database for my website, I intend on using software that will convert the database into static web pages.
Big problem I have I am not a programmer, but I know a tiny bit about tags etc. for search engines.
The meta tag description is what I want to create using a field in this database.
The software I am about to use has a sql builder is there anyway it could be done be highlighting the relavent field and using sql language.
PLEASE someone Help
This problem has been driving me around the twist.

View 1 Replies View Related

How To Remove Html Tags From Varchar Value

May 20, 2008

Hi !
i have a function written in c# which removes all html tags from the provide string like

public static string RemoveHTML(string HTML)
{
return Regex.Replace(HTML, "<(.|)*?>", "");
}

how can i apply such functionality to varchar field which removes all the html tags from it in stored procedure

Regards,
DiL

View 12 Replies View Related

SQL Server 2008 :: Strip HTML Tags

Oct 28, 2011

I have a table with a column that has html text. The column with html text is pretty big datatye varchar(max)... I wanted to check if any of you have any function that I can use to Strip out the HTML tags... I saw couple of version online, but it was running too slow..

This is the one I used: [URL] .....

View 9 Replies View Related

Remove ROOT Tags From SQL Task XML Results

Dec 19, 2007

Hi All,

I'm currently using an Execute SQL Task to return XML data from a query into an SSIS string variable. In my FOR XML clause in SQL I'm specifying a certain name for my root tag, called "Accounts". This works great in Management Studio, however, the Execute SQL Task appends a <ROOT> and </ROOT> tag to the start and end of the string, so now it looks like:

<ROOT><Accounts>...all my elements...</Accounts></ROOT>

I'd like to remove the ROOT tags so that the <Accounts> tags are actually the root for this doc. What would be the best way to remove the ROOT tags from the SSIS string variable?

Thanks!

View 9 Replies View Related

SQL Server 2012 :: How To Preserve Tags In Varchar Output

May 6, 2015

I have following XQuery:

declare @xmldoc as xml
select @xmldoc = '<Text>This is firstline<Break />This is second line<Break />This is third line</Text>'
select @xmldoc.value('(/Text)[1]','varchar(max)')Result is: "This is firstlineThis is second lineThis is third line"

My problem is, that the <Break /> tags within the text are removed in the conversion to varchar. How to preserve the such tags in the varchar output? Or to get the <Break /> tags "translated" to e.g. CHAR(10)?

View 2 Replies View Related

SQL Server 2008 :: Query To Read From Each Column Of XML Tags?

Jul 31, 2015

The below query will read the data in XML format but any query to read from each column of XML tags easily?

SELECT CAST(record AS XML), record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

View 5 Replies View Related

Exclude Html Tags From Full-text Index?

Oct 18, 2007

I ran a CONTAINS query for the word "target" in a bunch of index web pages. I came up with lots of matches -- but they were all inside html tags:

<a href="www.foo.com" target = "_blank">lorem ipsum</a>



Is there a good way to exclude tags (and their attributes) from the full-text index?


Thanks!

View 4 Replies View Related

A Relational Technique To Strip The HTML Tags Out Of A Ntext Datatype Field

Nov 27, 2007

I had a problem with the ntext datatype. I need to strip the HTML tags out of a ntext datatype column. I have sample query for that, which works fine for STRING, as stuff is the string function, what to do for ntext field.

=======The Process follows like this =========

--**************************************
--
-- Name: A relational technique to strip
-- the HTML tags out of a string
-- Description:A relational technique to
-- strip the HTML tags out of a string. Th
-- is solution demonstrates how to use simp
-- le tables & search functions effectively
-- in SQL Server to solve procedural / ite
-- rative problems.


-- This table contains the tags to be re
-- placed. The % in <head%>
-- will take care of any extra informati
-- on in the tag that you needn't worry
-- about as a whole. In any case, this t
-- able contains all the tags that needs
-- to be search & replaced.
CREATE TABLE #html ( tag varchar(30) )
INSERT #html VALUES ( '<html>' )
INSERT #html VALUES ( '<head%>' )
INSERT #html VALUES ( '<title%>' )
INSERT #html VALUES ( '<link%>' )
INSERT #html VALUES ( '</title>' )
INSERT #html VALUES ( '</head>' )
INSERT #html VALUES ( '<body%>' )
INSERT #html VALUES ( '</html>' )
go
-- A simple table with the HTML strings
CREATE TABLE #t ( id tinyint IDENTITY , string varchar(255) )
INSERT #t VALUES (
'<HTML><HEAD><TITLE>Some Name</TITLE>
<LINK REL="stylesheet" HREF="/style.css" TYPE="text/css" ></HEAD>
<BODY BGCOLOR="FFFFFF" VLINK="#444444">
SOME HTML text after the body</HTML>'
)
INSERT #t VALUES (
'<HTML><HEAD><TITLE>Another Name</TITLE>
<LINK REL="stylesheet" HREF="/style.css"></HEAD>
<BODY BGCOLOR="FFFFFF" VLINK="#444444">Another HTML text after the body</HTML>'
)
go
-- This is the code to strip the tags out.
-- It finds the starting location of eac
-- h tag in the HTML string ,
-- finds the length of the tag with the
-- extra properties if any. This is
-- done by locating the end of the tag n
-- amely '>'. The same is done
-- in a loop till all tags are replaced.

BEGIN TRAN
WHILE exists(select * FROM #t JOIN #html on patindex('%' + tag + '%' , string ) > 0 )
UPDATE #t
SET string = stuff( string , patindex('%' + tag + '%' , string ) ,
charindex( '>' , string , patindex('%' + tag + '%' , string ) )
- patindex('%' + tag + '%' , string ) + 1 , '' )
FROM #t JOIN #html
ON patindex('%' + tag + '%' , string ) > 0
SELECT * FROM #t
rollback

View 1 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







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