?
Solved

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

Posted on 2016-11-10
6
Medium Priority
?
58 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 29

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 29

Expert Comment

by:Pawan Kumar
ID: 41883408

can you give me-


output of 1st query ?

output of 2nd query ?
0
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!

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

770 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