| 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.msg
FROM dbo.historylist INNER JOIN dbo.requestlist ON dbo.historylist.requestID = dbo.requestlist.ID
ORDER 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 table
I 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 advance
Regards
Ray |
|
|
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)
ST
SELECT
dbo.historylist.artist,
dbo.historylist.title,dbo.requestlist.fromwho,
dbo.requestlist.name, dbo.requestlist.msg
FROM dbo.historylist
RIGHT JOIN
dbo.requestlist ON dbo.historylist.requestID = dbo.requestlist.ID
ORDER BY
CASE WHEN dbo.historylist.date_played is null then '1/1/1970'
else dbo.historylist.date_played end
DESC
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 lol
God 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 help
Regards
Ray
It'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.msg
FROM dbo.historylist
LEFT JOIN
dbo.requestlist ON dbo.historylist.requestID = dbo.requestlist.ID
ORDER BY
CASE WHEN dbo.historylist.date_played is null then '1/1/1970'
else dbo.historylist.date_played end
DESC
Terry
-- 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 thanks
Regards
Ray
It'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??