Link to home
Start Free TrialLog in
Avatar of Graeme McGilvray
Graeme McGilvrayFlag for Australia

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....
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()")

Open in new window


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.event_ID)

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)")

Open in new window


Thanks in advance
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

do you want to do it in one single SQL statement?

can you provide us some sample data and you expected result?
Avatar of Graeme McGilvray

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?
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)

Open in new window

you may try to read the value of field: result from there on
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
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 ?
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 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
  

Open in new window


User generated image
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.

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

Open in new window


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.
Oh ok, thanks for that

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")

Open in new window

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.
yes I am using Access, its ok, i totally missed it as well, just have to remove the #hastags

error now:
Microsoft JET Database Engine error '80040e14'

Undefined function 'NEWID' in expression.

Open in new window

Hi jeff, just having a scan over, it doesnt :(
ok, how about this..

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;

Open in new window

Thank you for that Jeff

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.
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
sample access database  table and SQL
ee2016111401.accdb
ok, had events and tickets switched.

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;

Open in new window

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;

Open in new window

and the output is e.event_ID or event_ID ?
are we able to add in another table with a variable?

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
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
try something like this..

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

Open in new window


in general, it's quite similar to what Jeff has proposed.
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:
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

Open in new window


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'.

Open in new window

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.
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")

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
works brilliant
Ryan are you able to help out here ??

slight modification needed for this Query

https://www.experts-exchange.com/questions/29008854/Adjust-an-existing-ASP-Query.html