How to show data in one list from two SQL tables with different columns

I need to get data from two tables with different columns in to one table. Utlimately it will be shown in a repeater control on a web page.
Table 1
Headline
URL
ContentIDTable 2
Product
DateCreated
ReviewURL

I want to show the top 20  in a list, ordered by date created. Is it possible to put them in temporary table to do this?
nhmediaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nativCommented:
Sounds like you want to use a UNION ALL
Sel ect Headline, URL, ContentID, NULL Product, NULL DateCreated, NULL ReviewURL
from Table1
UN ION ALL
sel ect NULL, NULL, NULL,Product,DateCreated,ReviewURL
From Table2

Open in new window

0
silemoneCommented:
or you could use left outer join and it will return all columns for each row...

0
silemoneCommented:
oops
seems a full outer join is the way  to go

http://msdn.microsoft.com/en-us/library/ms187518.aspx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

nhmediaAuthor Commented:
This looks good. Thank you. How would I additionally select the top ten order by ID and with a where clause for each table?
0
nhmediaAuthor Commented:
One more thing would it be possible to create a computed column so that ReviewAlias and URL Alias appear in one column Item alias?

0
silemoneCommented:
Select Top 10 T.ID from (Sel ect Headline, URL, ContentID, NULL Product, NULL DateCreated, NULL ReviewURL
from Table1
UN ION ALL
sel ect NULL, NULL, NULL,Product,DateCreated,ReviewURL
From Table2) as T
where some condition
order by T.ID
0
nhmediaAuthor Commented:
Thanks for your help. I am not getting any records from Table2 for some reason. Also, I need to apply the where filter to table1 before joining it (the criteria are for columns that only exist in table 1)
Any suggestions?
0
nativCommented:
silemone: YOur query will not work as there is no ID in the select of the table.
Which brings me to a question to nhmedia:
Where is this ID you want to order by?
You cannot order each sub query individually, only the whole results.

As for for the "Alias" you don't need to computer them.
Since they are both varchars then you can simply have them in the same position in the select.
Select Headline, URL, ContentID, NULL Product, NULL DateCreated, NULL ReviewURL
from Table1
UNION ALL
select NULL, ReviewURL, NULL,Product,DateCreated
From Table2

Open in new window

0
nhmediaAuthor Commented:
oh dear, that's a problem. I have 10,000 article in the first table, and I need to get the 10 most recent AND filter by a condition before joining them with the other table, which is much smaller.

Is there another approach I could take?

I basically need to join the top 10 from table 1 to the top 10 in table 2...
0
nativCommented:
I'm slightly confused.
What is the join between Table1 and Table2?
At the end of the day you want only 10 rows?
Does that mean 5 from Table1 and 5 from Table2, or is there some joining factor which links table1 to table2?
0
nhmediaAuthor Commented:
Sorry if my explanation is not clear. Ultimately, I need to show items from both tables in a repeater control on a web page. So first I want to splice the tables together into one list that my repeater can reference.
The Two Tables They have one common field - DateCreated.
0
nativCommented:
I'm still not with you.
You want to have your top 10 based on the ID.
Which table's ID are you reffering to?
Based on your table, the rows from Table1 are not related to Table2.
So you want 10 rows from Table one followed by 10 rows from Table2?

How would you order the rows.
0
nhmediaAuthor Commented:
Maybe what I need is to add the items from both sources to a temporary table where I can make the field names common.
 Temporary table
I would add the top ten items from table 1 where contenttype = 123 order by ContentID desc
Then I would  add the top ten items from table 2 where Manufacturer =LG order by ReviewID desc

The resulting table I would then sort by DateCreated desc

Thank you for your patience. I hope that helps
0
nativCommented:
I have re-read your original question, and I should have read it better the first time.
What you describe is a good solution.
It can be compacted into one query using CTE, but I doubt will get any benefit, it will just look different.

Do you need help implementing the inserts and selects from the Temp table?
0
nhmediaAuthor Commented:
Yes I do, my sql skills are very basic I am afraid, so any help you are willing to give would make a great difference.
0
SharathData EngineerCommented:
Please post some sample data with expected result
0
nativCommented:
Here is some code.
I'm not using temp table, but a CTE.
It is almost the same as a temporary table.
Basically you have "t1" which is your query for table1
then "t2" which is your query for table 2
and following that is the actual select which uses the t1 and t2 results.

Just so you understand, t1 and t2 can only be used in the select directly after were we defined them.

This is not like a temporary table which can be used again and again.
Since in this case you just need the information for the single select, thus i chose this way to write the query.
;with t1 as 
   (Select top 10 Headline, URL, DateCreated 
      from Table1 
     where ContentType = 123 
     order by ContentID desc),
      t2 as 
   (Select top 10 Product, ReviewURL, DateCreated
      from Table2
     where Manufacturer = 'LG'
     order by ReviewID desc)
Select Headline, URL, DateCreated
from t1
UNION ALL
select Product,ReviewURL,DateCreated
From t2
order by DateCreated

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nhmediaAuthor Commented:
Absolutely outstanding! Very helpful, and the solution worked perfectly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.