Help with Sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Sql query

Author

Topic

sartis
Starting Member

11 Posts

Posted - 2014-05-08 : 10:19:44

Hello, I want to select data from 2 tables in my database that are not related to each other. the first table is a news table and the second is a players table. When I output the data on my site I want the data from both tables to be mixed together based on dates....for instance:

The first item may be a new player and the next 3 could be news items, etc just based on the date. I hope this makes sense.

I was trying a join but since they are not related i was not having success. I hope I'm not over thinking this and missed something really easy.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-08 : 10:51:27

maybe a union ?


select col from news
union all
select col from players

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-05-09 : 05:18:55

depends on what all details you want to show. If metadata (columns) required is same you can use UNION ALL as Tan suggested. Otherwise you need to retrieve them as seprate queries. In any case if you can give us an example of sample data and what you need from it that might help.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

sartis
Starting Member

11 Posts

Posted - 2014-05-09 : 10:03:50

ok, Here are the columns for the two tables ......the first table is called news:

news_id
news_date
news_blurb
news_title

Second table is called players:

player_id
player_fname
player_lname
player_uname
player_pwd
player_loc
player_comments
player_date_added
player_fullname
user_last_login_dt
user_last_logout_dt
user_ip_address
login_counter
player_email
player_photo
player_since
player_hand
player_height
player_weight
academy_level
parent_match

I want to create news feed for new things on the site. I have it in 2 different queries right now. I want 1 query that would intermingle the data. I need to select the following columns:

News:
news_date - date field
news_blurb - varchar(max)
news_title - varchar(500)

Players:
player_fullname - computed column from player_fname and player_lname
player_since - date
player_date_added - date
player_photo - varchar(500)

I want to output the data by news_date and player_date_added.

I did try the union all and I was getting the following error.

I hope this helps.

In this screen shot I would want Zoey Clements moved up under the top 2 news items because her data added is 5/8.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-09 : 11:01:46

can you show us your Query ?

KH
[spoiler]Time is always against us[/spoiler]

sartis
Starting Member

11 Posts

Posted - 2014-05-09 : 11:15:23

I tried the union suggestion:

select news_title,news_blurb,news_date
from news

union all

select player_fullname,player_fname,player_date_added
from players

but I'm getting this error:

PLAYER_FULLNAME is undefined in GET_ALL_ACTIVITY.

so it output 1 news item fine but is erring on the first player output......the query will get more involved once I get the basics down. (I will only select like on 2 weeks back, etc. )

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-09 : 20:26:23

what is "GET_ALL_ACTIVITY" " i don't see it in your query

KH
[spoiler]Time is always against us[/spoiler]

sartis
Starting Member

11 Posts

Posted - 2014-05-09 : 23:10:44

it is the name of my query....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-09 : 23:34:18

quote:
Originally posted by sartis

it is the name of my query....

are you using Microsoft SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]

sartis
Starting Member

11 Posts

Posted - 2014-05-09 : 23:48:40

yes and Coldfusion for server side scripting.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-10 : 00:35:41

This site is on Microsoft SQL Server. There might not be many that are familiar with coldfusion. Please post in a coldfusion discussion site

KH
[spoiler]Time is always against us[/spoiler]

sartis
Starting Member

11 Posts

Posted - 2014-05-10 : 08:15:11

I think this is a sql issue, that is why I posted it here.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-11 : 21:40:33

It is not that we don't want to help you but but the error message does not make sense to me based on my knowledge on Microsoft SQL Server.
You will be better and faster help in the appropriate site.

What do you think ? If my BMW won't start and can i bring it to a Mercedes service center ? It is still a CAR right ? I probably can get a general diagnostic (is your battery dead ? Did you press the start button ? etc) and might not be able to solve or replace any fault parts

Well, here is the general diagnostic we can provide.
Few things to check for UNION query
1. run individual SELECT statement, make sure it is correct
2. make sure the no of select columns in all the query are the same
3. make sure the data type of the columns are the same
4. if your DB is case sensitive collation, make sure you specify the column name exactly (case wise) as in the schema

KH
[spoiler]Time is always against us[/spoiler]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-05-12 : 05:18:43

omg teach u got bm d???

sartis
Starting Member

11 Posts

Posted - 2014-05-12 : 09:18:12

so when I run the follow sql command

select news_title,news_blurb,news_date
from news

union all

select player_fullname,player_fname,player_date_added
from players

in management studio I understand why I'm getting PLAYER_FULLNAME is undefined in GET_ALL_ACTIVITY. the only columns returned from the query are news_title,news_blurb,news_date. But it puts the data from the player select clause under these columns.....I'm sure that is the default behavior of a union, correct??

Here's a screenshot:

[url]http://connexzone.artisdesigns.com/union.jpg[/url]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-05-13 : 05:21:36

yes

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources