join returning results from two tables...

[GraphicsFactory]

I am trying to return the results of two tables with one query. The problem is results from only one table are getting returned. Here is my query...

SELECT * FROM table1,table2 WHERE table1.ID = table2.LinkID OR table1.ID = '$ID' ORDER BY table2.dotTimestmp,table1.Timestmp DESC

What mistake did I make in that query?

 

[gmmastros]

It's hard to say what the mistake is. Can you explain your data and what you are trying to accomplish. Specifically... sample data from both tables and expected results would be helpful.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[GraphicsFactory]

Thanks, let me try to explain...

table1 has a column name of ID along with Title, Descriptions etc... to go along with it. table2 has a column name of LinkID with dotTitle, dotDecription, etc.. to go along with it.

I am trying to get the results from both tables and sort them based on their timestamp. I want to display the newest listings from those two tables using a single query.

I may just attack this from a different angle. Let me think about this and I'll post back.

 

[gmmastros]

It sounds to me like you want to list both tables as though they are a single table. If so, then Union ALL is your best bet.

Something like:

Select ID, Title, Description
From   Table1

Union All

Select LinkID, dotTitle, dotDescription
From   Table2

When using UNION ALL, each query must have the same number of column and the data types from each must be the same. What I mean is... in the example above, ID should have the same data type as LinkId. Title should have the same data type as dotTitle, etc...


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[GraphicsFactory]

Yes, I am trying to use both tables as a single table. Cool, I will try that.

So does this look correct?

SELECT * FROM table1 Union All SELECT * FROM table2 WHERE table1.ID = table2.LinkID OR table1.ID = '$ID' ORDER BY table2.dotTimestmp,table1.Timestmp DESC

 

[gmmastros]

SELECT * FROM table1 

Union All 

SELECT * FROM table2 

ORDER BY Timestmp DESC

I don't know what you are trying to accomplish with the where clause. The code above will return all the data from both tables order by the timestmp column desc.

As a general rule, I don't use Select * when writing a union all query because it is too hard to control. What I mean is... each table needs to have the same columns and the same ordinal position of the column within the table. If someone were to add a column to one of the tables, or even just change the order of the columns, your query will either break because of incompatible data types or return the wrong data. This is why I always spell out the column names.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[GraphicsFactory]

OK, thanks for the tips. It solved some of my problems.

 

[gk53]

or you cna use query like that

Select	id = ID, 
		Title = Title, 
		Description = Description
From   Table1

Union All

Select id = LinkID, 
	    Title = dotTitle, 
	    Description = dotDescription
From   Table2
order by id

as gmmastros posted "each query must have the same number of column and the data types from each must be the same", but name you can change and use nre column name to set order ob filter results