Produce a list - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Produce a list

Author

Topic

wrz234
Starting Member

8 Posts

Posted - 2010-04-06 : 05:59:02

I have three table created and how to retrieve the data from each of the 3 tables? Because i need to produce a list of approved claims show 'claim number', 'employee identity followed by employee name shown in employee column', 'claim code followed by claim type shown in claim column' and 'amount'.

Table of claim
-claimno
-empID
-status

Table of claimtype
-code

table of employee
-id
-name

The sql statement i wrote was

select claimno, empid + ' ' + name AS employee
cliamcode + ' ' + code AS claim
amount
from claim, employee, claimtype

Please help

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-06 : 06:21:14

What is the common column between Claim table and ClaimType

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 06:25:44

You need to tell SQL Server how to link these tables together. To link the claim & employee tables, you can use this:

FROM claim, employee
WHERE claim.empID = employee.ID

Or you could use an inner join (This one also demonstrates how to use aliases:

FROM claim c
INNER JOIN employee e
	ON c.empID = e.ID

However, you claim table doesn't appear to have anything that identifies what claimtype matches each claim. You need to add a claimTypeID to both the claimtype and claim tables. Then you can join those 2 tables in the same way as above.

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 07:08:09

are you trying to generate csv file out of this data? if yes, you could use options like bcp of OPENROWSET.
Anyways code you've writen above takes a cross join between tables returning all possible combinations of thier values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wrz234
Starting Member

8 Posts

Posted - 2010-04-07 : 03:37:24

Dear all,
Thanks for all the info. I think this need to extract information from 3 table in once. I need use which join operators in this case? Sorry, i am newbie here, so is quite difficult to explain here. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 03:59:33

depends on situation. is relation strictly one to one? will there be a matching row in all tables always?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wrz234
Starting Member

8 Posts

Posted - 2010-04-07 : 04:12:36

not quite understand.. Example like, i want 'employee name' from table 'employee' but together need the 'employee id' from table claim'. and put these 'employee name' and 'employee id' into one column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 04:16:28

put these 'employee name' and 'employee id' into one column
do you mean 1 table? its not a good idea to put multiple values in same column

the question i earlier asked is whether you'll have a record in claims for all employees?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kashyapsid
Yak Posting Veteran

78 Posts

Posted - 2010-04-07 : 04:18:31

Nice Question

KaShYaP

wrz234
Starting Member

8 Posts

Posted - 2010-04-07 : 04:27:56

yes, it contain record also

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources