Solved

Query not working correctly? (or how i want it to...

Posted on 2016-11-10
6
51 Views
Last Modified: 2016-11-14
Hi all I have a query which isnt showing the results i want it to, i think it is something to do with how it is set out

Set Top4Ticket=oConn.Execute("SELECT * FROM tickets,ticket_stands,ticket_type,site_curr WHERE curr_ID=ticket_curr AND (tickets.type_ID=18 OR tickets.type_ID=2) AND tickets.type_ID=ticket_type.type_ID AND tickets.stand_ID=ticket_stands.stand_ID AND event_ID="&Onsale("event_ID")&" AND ticket_live=YES ORDER BY ticket_adtgross")
CountTop4Tickets=0
Top4Ticket.MoveFirst
Response.Write(Top4Ticket("ticket_ID"))
CountTop4Tickets=CountTop4Tickets+1
Top4Ticket.MoveFirst
Do Until Top4Ticket.EOF
If CountTop4Tickets=4 Then
	Exit Do
Else
	Response.Write(Top4Ticket("ticket_ID"))
	CountTop4Tickets=CountTop4Tickets+1
End If
Top4Ticket.MoveNext
	Loop

Open in new window


what is happening is...

the 1st result and 2nd result are the same

eg, 1,1,2,3

instead of:
1,2,3,4

Thanks in advance
0
Comment
Question by:Graeme
  • 3
  • 2
6 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41883357
Try

Set Top4Ticket=oConn.Execute("SELECT * FROM tickets,ticket_stands,ticket_type,site_curr WHERE curr_ID=ticket_curr 
AND (tickets.type_ID=18 OR tickets.type_ID=2) AND tickets.type_ID=ticket_type.type_ID AND tickets.stand_ID=ticket_stands.stand_ID AND 
event_ID="&Onsale("event_ID")&" AND ticket_live=YES ORDER BY ticket_adtgross")

CountTop4Tickets = 1
Top4Ticket.MoveFirst
Do Until Top4Ticket.EOF
If CountTop4Tickets = 5  Then
	Exit Do
Else
	Response.Write(Top4Ticket("ticket_ID"))
	CountTop4Tickets=CountTop4Tickets+1
End If
Top4Ticket.MoveNext
	Loop

Open in new window

0
 

Author Comment

by:Graeme
ID: 41883403
HI Pawan and thanks for your help, however its the same result
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41883408

can you give me-


output of 1st query ?

output of 2nd query ?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 41883514
Cause you have two output paths. The correct loop pattern is:

Set Top4Ticket = oConn.Execute("SELECT * FROM tickets,ticket_stands,ticket_type,site_curr WHERE curr_ID=ticket_curr AND (tickets.type_ID=18 OR tickets.type_ID=2) AND tickets.type_ID=ticket_type.type_ID AND tickets.stand_ID=ticket_stands.stand_ID AND event_ID="&Onsale("event_ID")&" AND ticket_live=YES ORDER BY ticket_adtgross")
CountTop4Tickets = 0
If Not Top4Ticket.BOF And Not Top4Ticket.Eof Then
    Top4Ticket.MoveFirst
    Do While Not Top4Ticket.EOF Or CountTop4Tickets < 3
        Response.Write(Top4Ticket("ticket_ID"))
        CountTop4Tickets = CountTop4Tickets + 1
        Top4Ticket.MoveNext
    Loop
End If

Open in new window


But this is not necessarily the source of your problem. Maybe you'll get this, due to your JOIN's in you SQL statement. Which needs a rework.

1) Which database has a constant literal named YES?
2) Return only the needed data.
3) Use table alias names and qualify each column with the correct alias. Currently it's not clear, what column is in which table.
4) Use the JOIN syntax instead. It should read

SELECT  T.ticket_ID
FROM    tickets T
        INNER JOIN ticket_stands TS ON T.stand_ID = TS.stand_ID
        INNER JOIN ticket_type TT ON T.type_ID = TT.type_ID
        INNER JOIN site_curr SC ON T.curr_ID = SC.ticket_curr   -- guess
WHERE   ( T.type_ID = 18
          OR T.type_ID = 2
        )
        AND T.event_ID = @event_ID                              -- guess
        AND TT.ticket_live = 'YES'                              -- guess
ORDER BY T.ticket_adtgross;                                     -- guess

Open in new window


-- guess means I've do not know the correct table.

5) If ticket_stands mean that you have multiple points of sale, then you SQL statement currently returns the most sold tickets per stand and not overall. So the question is: What do you want to output?
In the overall case you need a GROUP BY with a SUM() over the correct column. But without knowing which column  belongs to which table and why the table site_curr is necessary, it's hard to tell..
1
 

Author Comment

by:Graeme
ID: 41885946
Hi Pawan, my result is 1,1,2,3, yours is the same 1,1,2,3

ste5an - am going to try yours now, will get back to you
0
 

Author Closing Comment

by:Graeme
ID: 41886132
Works a dream! thank you!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sorting - Classic asp putting a specific text @ the top of the table 3 51
Live mode in DW, need to creae Session 4 107
XML Parsing Classic ASP 5 64
Hide Table in merge 3 31
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question