Solved

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

Posted on 2016-11-10
6
53 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
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 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pass through dll 2 99
ASP/VB email question 4 62
Hide Table in merge 3 41
Prevent certain words from being typed in a form 6 28
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…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

756 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