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.
| Author |
Topic |
|
HANKyPARK
Starting Member
4 Posts |
 Posted - 2009-05-31 : 16:48:23
|
Hello I am using or rather trying to create an sql select to do the following...I have 2 tables, historylist and requestlist. the historylist table i want 2 values and from requestlist i want 3 values, they have a relating field or column which is requestID. the problem I have is that the requestID field in historylist table is not always a value that is in requestID table.i.e. A user places a request with a dedication in the requestlist table and the auto number is put into the requestID field in the historylist table, making an inner join possible, however if no request is placed the value in the requestID of the historylist table for the playing song is then 0.I used this code...SELECT TOP 1 dbo.historylist.artist, dbo.historylist.title,dbo.requestlist.fromwho, dbo.requestlist.name, dbo.requestlist.msgFROM dbo.historylist INNER JOIN dbo.requestlist ON dbo.historylist.requestID = dbo.requestlist.IDORDER BY dbo.historylist.date_played DESC that works fine if there is a request in place but i need it to display artist, title, [name = "no name"], [msg = "no msg"], [fromwho = "nobody"] when there isn't a request placed, meaning the value of requestID in historylist table is = 0 with no relating value in requestID in requestlist tableI have tried to explain as best I can, seems easy to me cos I know what am trying to achieve and am just hoping someone can understand and please help if possible?Many thanks in advanceRegardsRay |
|
|
souLTower
Starting Member
39 Posts |
Posted - 2009-06-01 : 08:08:56
|
The following query will return requests which have a history AND requests which do not have a history. Records with no history are assumed to occur first (see the case statement in the order clause)STSELECT dbo.historylist.artist, dbo.historylist.title,dbo.requestlist.fromwho, dbo.requestlist.name, dbo.requestlist.msgFROM dbo.historylist RIGHT JOIN dbo.requestlist ON dbo.historylist.requestID = dbo.requestlist.IDORDER BY CASE WHEN dbo.historylist.date_played is null then '1/1/1970' else dbo.historylist.date_played endDESC God Bless |
 |
|
|
HANKyPARK
Starting Member
4 Posts |
Posted - 2009-06-01 : 15:03:07
|
| Hi There,Firstly thank you very much for your reply is much appreciated, however, the new select query you have sent still only shows entries where a request has been placed. When a song is played it is put in the history table, if the song is one of a random select by the radio itself then there will be no record (requestID) in the request table and the requestID in the historylist table will = 0, and this is where i fall short because i still need the information of that song playing i.e. artist, title to display, when a request is placed be it a standard or dedicated request it will obviously have a match requestID in both tables.Many thanks again for the time you have taken to help me this.I am ok with the simple queries but this one has my head in a bit of a spin lolGod Bless[/quote]It's never a mistake to care for someone. That's *always* a good thing! outbreakdjs.co.uk |
|
|
|
HANKyPARK
Starting Member
4 Posts |
Posted - 2009-06-01 : 18:04:09
|
| I have emptied both tables and ran through 3 songs and snap shotted the results from each table in the hope it will show better what am trying to do and is on my site here [url]http://www.outbreakdjs.co.uk/sample10.asp[/url] thank you for any helpRegardsRayIt's never a mistake to care for someone. That's *always* a good thing! outbreakdjs.co.uk |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
Posted - 2009-06-02 : 10:47:28
|
| Try a left join:SELECT dbo.historylist.artist, dbo.historylist.title,dbo.requestlist.fromwho, dbo.requestlist.name, dbo.requestlist.msgFROM dbo.historylist LEFT JOIN dbo.requestlist ON dbo.historylist.requestID = dbo.requestlist.IDORDER BY CASE WHEN dbo.historylist.date_played is null then '1/1/1970' else dbo.historylist.date_played endDESCTerry-- Procrastinate now! |
|
|
|
HANKyPARK
Starting Member
4 Posts |
Posted - 2009-06-02 : 12:12:21
|
Amazing  it works, I am very grateful for your help it has driving me mad for over a week trying to get the dam thing to work lol Just as an additional question can I ask if anybody could recommend any good yet easy to understand books for SQL, books being quite expensive I don't wantt to waste money on stuff that just goes striaght over my head as a beginner.Many many thanksRegardsRayIt's never a mistake to care for someone. That's *always* a good thing! outbreakdjs.co.uk |
|
|
|
tosscrosby
Aged Yak Warrior
676 Posts |
|
|
|
|
|
|
|
SQL Select problem??