Solved

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

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

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41883408

can you give me-


output of 1st query ?

output of 2nd query ?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 32

Accepted Solution

by:
Stefan Hoffmann 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

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…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now