Question

Locked

sql/asp.NET/vb.net Programing Question

By brian ·
I am trying to write a select statement that will join 3 tables togeather, all of them have a common thing which i will use to pull from, ie a row identifier (number 4 for example). Table one is the main table and will only ever have one instance of this number, but table 2 and 3 may have many instances of this number as they store different comments from agents for 1 and admin for 2. I am writing a report using a data grid and would like to pull all of the infromation in from all the tables based on date range (which orderby date will work just fine)...I have tried the different joins, but do not get the desired result...I am using Eval("row1")...to fill in info at desired places...any suggestions?

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Clarify

by robo_dev In reply to sql/asp.NET/vb.net Progra ...

Post your query, what it's returning as-is, and some info about the tables it's pulling from, such as what the primary key is.

Also, what database are you using?

At a high level, what you are doing is called an inner-join

http://databases.about.com/od/sql/a/multiple_joins.htm

Collapse -

well...you asked

by brian In reply to Clarify

The Primary key that ties all of this togeather is the report number (reportid). I am using a data grid to display the information in html. I can get it to display all informaiton for dbo.reports, but can not get it to display information for dbo.comments or dbo.meeting_actions. The system will return nothing (no null values, but a blank white screen) when this is done as a query.

SELECT ISNULL(dbo.reports.eagentid, 0) AS newEagentId, dbo.reports.reportid, dbo.reports.urgent, dbo.reports.client, dbo.reports.address, dbo.reports.city,
dbo.reports.state, dbo.reports.zip, dbo.reports.deptid, dbo.reports.customer_comment, dbo.reports.action_agent, dbo.reports.action_date,
dbo.reports.action, dbo.reports.et_error, dbo.reports.ts_error, dbo.reports.pf_error, dbo.reports.rf_error, dbo.reports.te_error, dbo.reports.af_error,
dbo.reports.ae_error, dbo.reports.phone, dbo.reports.agentid, dbo.reports.bagentid, dbo.reports.date, dbo.reports.status, dbo.reports.traveler,
dbo.reports.nts_error, dbo.reports.supplier, dbo.reports.pcomment, dbo.reports.crequest, dbo.reports.internaldoc, dbo.reports.oagentid,
dbo.reports.pnr, dbo.reports.destination, dbo.reports.dep_date, dbo.reports.refund, dbo.reports.dmemo, dbo.reports.ticket_num,
dbo.reports.cash_expense, dbo.reports.money_due, dbo.reports.reimbursement, dbo.reports.gift_Certificate, dbo.reports.etravel,
dbo.Comment.date AS Expr1, dbo.Comment.agentid AS Expr2, dbo.Comment.comment, dbo.meeting_actions.date AS Expr3,
dbo.meeting_actions.agentid AS Expr4, dbo.meeting_actions.maction
FROM dbo.reports INNER JOIN
dbo.Comment ON dbo.reports.reportid = dbo.Comment.reportid INNER JOIN
dbo.meeting_actions ON dbo.reports.reportid = dbo.meeting_actions.reportid
WHERE (dbo.reports.reportid = '12345')

Collapse -

That's going to be messy

by Tony Hopkinson In reply to sql/asp.NET/vb.net Progra ...

Given you could have data in 2 or 3 or both or neither that's two outer joins of the same field.

Given
Table1 is
RowNumber,
SomeText

Table2 is
RowNumber
AgentComment

Table3 is
RowNumber
AdminComment

then

select RowNumber, "Agent" as CommentType,AgentComment as Comment
Union
select RowNumber, "Admin", AdminComment

will give you all the comments and a type

So
Select Table1.*, CommentType, Comment
inner join (select RowNumber, "Agent" as CommentType,AgentComment as Comment
Union
select RowNumber, "Admin", AdminComment
) comments On Table1.RowNumber = Comments.RowNumber
Where SomeDate Between StartDate and EndDate

might be a bit easier to get the head round.

If you want table 1 records without any comments change inner to outer.

Should work I think.

HtHs

Collapse -

Thanks for the comment

by brian In reply to That's going to be messy

LOL, makes me feel all warm and fuzzy inside when the first words I read are "That's going to be messy". LOL! But none the less, Thank you for the post, I'm going to try it out and see what happens. I'll update in a while.

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums