Graeme McGilvray
asked on
How to show result of a NULL value at random?
Hi All, i want to display a result of a NULL value at random
The current code I have is....
What I want it to detect is.... look into a table called 'Tickets' and if there is none (ticket records), show me that event_ID (events.event_ID=tickets.e vent_ID)
Some other code I found and played with for randomness but no luck, maybe able to add in...?
Thanks in advance
The current code I have is....
Set PreRegister=oConn.Execute("SELECT * FROM events,locations,codes WHERE event_location=location_ID AND event_code=code_short AND code_live=TRUE AND event_live=TRUE AND event_start>Now()")
What I want it to detect is.... look into a table called 'Tickets' and if there is none (ticket records), show me that event_ID (events.event_ID=tickets.e
Some other code I found and played with for randomness but no luck, maybe able to add in...?
Set PreRegisterTickets=oConn.Execute("SELECT * FROM tickets t LEFT JOIN events e ON t.event_ID=e.event_ID WHERE e.event_ID IS NULL ORDER BY RND(-10000000*TimeValue(Now())*e.event_ID)")
Thanks in advance
ASKER
Hi Ryan, yes please that would be best, however it doesnt have to be either
if not, the essential tables:
events
tickets
events.event_ID <- what i need (then i can do another query for the particulars
what else would you need?
if not, the essential tables:
events
tickets
events.event_ID <- what i need (then i can do another query for the particulars
what else would you need?
ok, based on my understanding... perhaps a quick fix like this may work?
SQLstr = "Select a.*, IIF(b.cnt >0, 'what else to show here?', 'Show event id...') as result from (SELECT * FROM events,locations,codes WHERE event_location=location_ID AND event_code=code_short AND code_live=TRUE AND event_live=TRUE AND event_start>Now() ) as a left join (select event_ID, count(event_ID) as cnt from tickets group by event_ID) as b on a.event_ID=b.event_ID "
Set PreRegister=oConn.Execute(SQLstr)
you may try to read the value of field: result from there on
ASKER
Hi Ryan, I am a bit confused for the part after...
SQLstr = "Select a.*, IIF(b.cnt >0,
'what else to show here?', 'Show event id...' <- this part
SQLstr = "Select a.*, IIF(b.cnt >0,
'what else to show here?', 'Show event id...' <- this part
emmm, sure...
so if there is record in table: tickets, what value you would like to return?
similarly, if there is no record in table: tickets, what value you would like to return? event_id ?
so if there is record in table: tickets, what value you would like to return?
similarly, if there is no record in table: tickets, what value you would like to return? event_id ?
ASKER
If it has tickets attached to the event, I dont want a result, just skip and go to all that have NO tickets. event_ID for events that have NO tickets
One possible solution is to use the NEWID() function which bascially generates a random key, then you can sort on that.
sample standalone code:
sample standalone code:
-- Sample Tickets table
create Table #Tickets (
TicketNumber int
,TicketDesc varchar(20)
,event_ID int
);
-- Sample Events table
create Table #Events (
EventDesc varchar(20)
,EventNumber int
,event_ID int
);
-- add some records
Insert into #Tickets (TicketNumber,TicketDesc,event_ID) values (1,'Apple',14)
Insert into #Tickets (TicketNumber,TicketDesc,event_ID) values (2,'Banana',15)
Insert into #Tickets (TicketNumber,TicketDesc,event_ID) values (3,'Pear',16)
Insert into #Tickets (TicketNumber,TicketDesc,event_ID) values (4,'Cherry',17)
Insert into #Tickets (TicketNumber,TicketDesc,event_ID) values (5,'Watermelon',18)
Insert into #Tickets (TicketNumber,TicketDesc,event_ID) values (6,'Cantelope',19)
Insert into #Tickets (TicketNumber,TicketDesc,event_ID) values (7,'Pineapple',20)
Insert into #Tickets (TicketNumber,TicketDesc,event_ID) values (8,'kiwi',21)
Insert into #Tickets (TicketNumber,TicketDesc,event_ID) values (9,'strawberry',22)
Insert into #Tickets (TicketNumber,TicketDesc,event_ID) values (10,'blueberry',23)
-- add some records
Insert into #Events (EventDesc,EventNumber,event_ID) values ('abc',101,10)
Insert into #Events (EventDesc,EventNumber,event_ID) values ('def',201,11)
Insert into #Events (EventDesc,EventNumber,event_ID) values ('geh',251,12)
Insert into #Events (EventDesc,EventNumber,event_ID) values ('ijk',151,13)
Insert into #Events (EventDesc,EventNumber,event_ID) values ('lmn',101,14)
Insert into #Events (EventDesc,EventNumber,event_ID) values ('opq',201,15)
Insert into #Events (EventDesc,EventNumber,event_ID) values ('rst',252,16)
Insert into #Events (EventDesc,EventNumber,event_ID) values ('uvw',152,24)
SELECT top 3 NEWID() AS RandomID
,*
FROM #Tickets t LEFT JOIN #Events e
ON t.event_ID=e.event_ID
WHERE e.event_ID IS NULL
order by RandomID
ASKER
Hi Jeff and thanks for that, from reading over it, it is creating new records?? is that correct?
The important part is the query. The create table commands and insert statements are there only to create some test data as a sample.
This is not creating new records. It is returning records from the join of Tickets and Events where there is an event record, but no ticket record. The field RandomID is part of that query. you can remove the top 3 if you want to show them all in random order.
SELECT top 3 NEWID() AS RandomID
,*
FROM #Tickets t LEFT JOIN #Events e
ON t.event_ID=e.event_ID
WHERE e.event_ID IS NULL
order by RandomID
This is not creating new records. It is returning records from the join of Tickets and Events where there is an event record, but no ticket record. The field RandomID is part of that query. you can remove the top 3 if you want to show them all in random order.
ASKER
Oh ok, thanks for that
have just tested your query, an error on the line
have just tested your query, an error on the line
Microsoft JET Database Engine error '80040e14'
Syntax error in FROM clause.
Set PreReg=oConn.Execute("SELECT top 1 NEWID() AS RandomID ,* FROM #Tickets t LEFT JOIN #Events e ON t.event_ID=e.event_ID WHERE e.event_ID IS NULL order by RandomID")
Sorry, I assumed that since you were using ASP that you were using SQL Server.
it looks like you are using Microsoft Access. There is probably an equivenant function that generates a GUID or something. I don't know off the top of my head.
it looks like you are using Microsoft Access. There is probably an equivenant function that generates a GUID or something. I don't know off the top of my head.
ASKER
yes I am using Access, its ok, i totally missed it as well, just have to remove the #hastags
error now:
error now:
Microsoft JET Database Engine error '80040e14'
Undefined function 'NEWID' in expression.
ASKER
Hi jeff, just having a scan over, it doesnt :(
ok, how about this..
tested in Microsoft Access:
tested in Microsoft Access:
SELECT *
FROM (SELECT rnd(t.event_id) AS RandomID, *
FROM Tickets AS t LEFT JOIN Events AS e ON t.event_ID=e.event_ID
WHERE e.event_ID IS NULL
) AS G1
ORDER BY G1.RandomID;
ASKER
Thank you for that Jeff
What is the outcome?
e.event_ID ?
What is the outcome?
e.event_ID ?
The result is that the values are returned in random order.
Maybe I'm misunderstanding what you are looking for.
Maybe I'm misunderstanding what you are looking for.
ASKER
What I have is...
events which some have tickets, some do not have tickets
I am after the event_ID of the events that do NOT have tickets
events which some have tickets, some do not have tickets
I am after the event_ID of the events that do NOT have tickets
sample access database table and SQL
ee2016111401.accdb
ee2016111401.accdb
ok, had events and tickets switched.
here are events that do not have tickets.
here are events that do not have tickets.
SELECT *
FROM (SELECT rnd(e.event_id) AS RandomID, *
FROM Events as e LEFT JOIN Tickets AS t ON t.event_ID=e.event_ID
WHERE t.event_ID IS NULL
) AS G1
ORDER BY G1.RandomID;
If you only want the event_id
SELECT e.event_id
FROM (SELECT rnd(e.event_id) AS RandomID, *
FROM Events as e LEFT JOIN Tickets AS t ON t.event_ID=e.event_ID
WHERE t.event_ID IS NULL
) AS G1
ORDER BY G1.RandomID;
ASKER
and the output is e.event_ID or event_ID ?
ASKER
are we able to add in another table with a variable?
table - codes
column - code_live = TRUE
relationship - event_code = code_ID
table - codes
column - code_live = TRUE
relationship - event_code = code_ID
@Graeme,
can you provide some sample data and your expected output? it helps to visualize your issue and explain to us what you want. cheers
can you provide some sample data and your expected output? it helps to visualize your issue and explain to us what you want. cheers
ASKER
Hi Ryan and not an issue at all
Here is the layout and some essential and non-essential feilds
events-
event_ID
event_name
event_code = code_ID
event_live = TRUE
event_start > NOW
tickets-
ticket_ID
ticket_name
ticket_live = TRUE
event_ID = events.event_ID
codes-
code_ID = event_code
code_name
code_live =TRUE
The end result I would like is the events.event_ID's of all the events which have NO tickets listed/attached to them, displayed at random
Here is the layout and some essential and non-essential feilds
events-
event_ID
event_name
event_code = code_ID
event_live = TRUE
event_start > NOW
tickets-
ticket_ID
ticket_name
ticket_live = TRUE
event_ID = events.event_ID
codes-
code_ID = event_code
code_name
code_live =TRUE
The end result I would like is the events.event_ID's of all the events which have NO tickets listed/attached to them, displayed at random
try something like this..
in general, it's quite similar to what Jeff has proposed.
Select e.event_ID
from events as e
inner join codes as c on c.code_ID = e.event_ID
left join (select * from tickets where ticket_live = True) as t on t.event_ID = e.event_ID
where e.event_live = True and e.event_start > Now() and c.code_live = True and e.event_ID is null
group by e.event_ID
order by RND(-10000000*TimeValue(Now())*e.event_ID
in general, it's quite similar to what Jeff has proposed.
ASKER
Thanks for the Ryan, had to make an alteration in the query to match up the tables (to c.code_ID=e.event_code)
now:
now getting this error:
now:
SELECT e.event_ID
FROM events AS e
INNER JOIN codes AS c ON c.code_ID=e.event_code
LEFT JOIN (SELECT * FROM tickets WHERE ticket_live=TRUE) AS t ON t.event_ID=e.event_ID
WHERE e.event_live=TRUE AND e.event_start>NOW() AND c.code_live=TRUE AND e.event_ID IS NULL
GROUP BY e.event_ID
ORDER BY RND(-10000000*TimeValue(Now())*e.event_ID
now getting this error:
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'c.code_ID=e.event_code LEFT JOIN (SELECT * FROM tickets WHERE ticket_live=TRUE) AS t ON t.event_ID=e.event_ID'.
how you construct your SQL in your ASP code?
can you post your codes here?
make sure you leave a "space" at the end of your string if you got multiple strings to join.
can you post your codes here?
make sure you leave a "space" at the end of your string if you got multiple strings to join.
ASKER
Set PreRegister=oConn.Execute("SELECT e.event_ID FROM events AS e INNER JOIN codes AS c ON c.code_ID=e.event_code LEFT JOIN (SELECT * FROM tickets WHERE ticket_live=TRUE) AS t ON t.event_ID=e.event_ID WHERE e.event_live=TRUE AND e.event_start>NOW() AND c.code_live=TRUE AND e.event_ID IS NULL GROUP BY e.event_ID ORDER BY RND(-10000000*TimeValue(Now())*e.event_ID")
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
works brilliant
ASKER
Ryan are you able to help out here ??
slight modification needed for this Query
https://www.experts-exchan ge.com/que stions/290 08854/Adju st-an-exis ting-ASP-Q uery.html
slight modification needed for this Query
https://www.experts-exchan
can you provide us some sample data and you expected result?