Web Development

Ask something
Question Locked

sql/asp.NET/vb.net Programing Question

0 helpful
5 replies

by brian

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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?

5 replies

by brian

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')

by tony hopkinson

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

by brian

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.

This discussion is closed to new replies.