?
Solved

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

Posted on 2016-11-10
6
Medium Priority
?
60 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 32

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 32

Expert Comment

by:Pawan Kumar
ID: 41883408

can you give me-


output of 1st query ?

output of 2nd query ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Accepted Solution

by:
ste5an earned 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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 is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 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