select from this table or that table ??

Question

Post reply

select from this table or that table ??

Wayne Coles

SSC Enthusiast

Points: 121
More actions
January 26, 2011 at 10:04 am

#233408

I want to query three tables in my database the tables are a “Company details” table including such fields as “Co_Name” & “Main_Tel”

A History table where completed actions are stored and a pending table where upcoming actions are listed

Joining the three tables to query is not a problem, but what I am struggling with is not duplicating the data and making the results clearer to the eye.

I would like to say select all events from history that have happened since a particular event and also any pending activities scheduled relating to that event.

But the only way I know how to do this is to put it all on one row so if I have five historical events and two pending ones I end up with 10 results returned. Each historical event duplicating twice as it is recorded against each pending, does that make sense.

I think what I want to say is “select this from that table” and then “select some other from another table” and have all of the results listed in one dataset…possible?

Any help appreciated

Cheers

Wayne

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

To help others help you with a tested solution, please post table definitions, sample data (suitably altered to hide sensitive information), required results.

For an easy method to do so please read the article by clicking on the first link in my signature block.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 2

Just speaking in theory, because without code & structures, it's hard to know what you're going for, you can combine two different result sets using the UNION or UNION ALL statement. But, the result sets have to have a common set of columns. They can come from totally different tables or no tables at all, but they can be combined. UNION will result in a unique aggregation of the two datasets. UNION ALL will simply concatenate the two sets.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Wayne Coles SSC Enthusiast Points: 121 More actions · Answer 3

Hi

Thanks for replying,sorry for delay in my response, other things...u know

below is a sample of my code

select a1.id, a1.CoName,a1.Tel, a2.agent, a2.ondate,a2.result, a3.agent,a3.ondate,a3.nextaction

from details a1, history, a2, pending a3

where a1.id *= a2.id and a1.id *= a3.id

and a2.ondate > '2010-10-01'

Basically a simple (&hopefully clear) statement that pulls a Company's name & tel no along with any historical activity linked to that company since request date and any upcoming pending actions

but as i said if i have multiple pending and historical activity i get each combination where i would like only one return for each, is that possible?

Cheers

Wayne

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

Wayne Coles (1/27/2011)

Hi

Thanks for replying,sorry for delay in my response, other things...u know

below is a sample of my code

select a1.id, a1.CoName,a1.Tel, a2.agent, a2.ondate,a2.result, a3.agent,a3.ondate,a3.nextaction

from details a1, history, a2, pending a3

where a1.id *= a2.id and a1.id *= a3.id

and a2.ondate > '2010-10-01'

Basically a simple (&hopefully clear) statement that pulls a Company's name & tel no along with any historical activity linked to that company since request date and any upcoming pending actions

but as i said if i have multiple pending and historical activity i get each combination where i would like only one return for each, is that possible?

Cheers

Wayne

Right, assuming I understand what you're saying, yes, UNION is what you're looking for. Something like this:

select a1.id, a1.CoName,a1.Tel, a2.agent, a2.ondate,a2.result



FROM details AS a1

LEFT JOIN history AS a2

ON a1.id = a2.id

WHERE a2.ondate > '2010-10-01'

UNION

SELECT a1.id,a1.ColName,a1.Tel,a3.agent,a3.ondate,a3.nextaction AS result --the columns have to be the same name & datatype

FROM details AS a1

LEFT JOIN pending AS a3

ON a1.id = a3.id

You just have to make sure that you have the same number and type of columns from one to the next between the two UNION statements

Notice, I replaced the deprecated ANSI 89 JOIN syntax with the ANSI 92 syntax. You might as well practice that since, starting in SQL Server 2008, that old-style syntax is completely unsupported.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Wayne Coles SSC Enthusiast Points: 121 More actions · Answer 5

Thanks bud

I see what you have done and i think it makes sense to me. Unfortunately i am in the uk and i cannot test this until i am back tomorrow, but i will then and reply

I also note what you say about JOINS I did attempt them a few years back, but the old *= seems so much easier to me, still i expect it covers aspects i have not encountered

with regard to this i am anticipating a move to 2008 soon, my database has around 300 views i have written many with this type of join, will i need to manually rewrite them all?

thanks again

Wayne

Wayne Coles SSC Enthusiast Points: 121 More actions · Answer 6

Thanks Grant

You are a star, and you have opened up a whole new avenue for me to explore

I'm making some headway, my history table also includes 'nextaction' so I have now put both in the query and when querying the pending table i included,' ' as 'result' , and it worked i have a blank entry where they would not and should not exist.

Yeh probably basic stuff, but its always good when you ask yourself, "I wonder what'll happen if i do that, and then you press F5 and see the world turn"

Me one happy chap 😀

s'pose i gotta get my heade around these JOINS now, inner, outer, left, right...

The world does still turn in 2008, doesn't it?

Cheers

Wayne

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 7

Glad I could help.

Yeah, 2008 you no longer have a choice on the JOIN syntax. Might as well jump.

Also, keep an eye out for opportunities to use UNION ALL instead of UNION. If you don't need the aggregation that UNION does (getting a distinct list), you'll see a huge performance improvement with UNION ALL.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software