Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 65
  • Last Modified:

INNER JOIN ?

Hi there I am not sure if this should be an INNER JOIN or not...

What I want is the outcome of the different event_IDs (which sit in tickets, however to get to tickets I need relate back to itin_items)

Set PackageEvents=oConn.Execute("SELECT * FROM itin_items,tickets WHERE itin_ID="&Package("itin_ID")&" AND itin_items.ticket_ID>0 AND itin_items.ticket_ID=tickets.ticket_ID")

Open in new window


Any help is appreciated

Thanks
0
Graeme
Asked:
Graeme
  • 4
  • 3
1 Solution
 
Jeff DarlingDeveloper AnalystCommented:
I would code the join explicitly like this instead of the comma.

SELECT * FROM itin_items I
join tickets T on I.ticket_ID=T.ticket_ID
WHERE I.itin_ID=itin_ID 
  AND I.ticket_ID>0 

Open in new window

0
 
Graemewebber4technologiesAuthor Commented:
Microsoft JET Database Engine error '80040e14'

Syntax error in FROM clause.

/home.asp, line 1722
Set PackageEvents=oConn.Execute("SELECT * FROM itin_items I JOIN tickets T ON I.ticket_ID=T.ticket_ID WHERE I.itin_ID=itin_ID AND I.ticket_ID>0")

Open in new window

0
 
Ryan ChongCommented:
don't quite understand your requirements, can you provide us some of the data and your expected output?

and what's the value of Package("itin_ID") ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Graemewebber4technologiesAuthor Commented:
Hi Ryan, what I want to do is show the event_IDs (not multiple of the same event_ID - which there maybe)

The expected output (event_ID) is numeric (within the table tickets)

Package("itin_ID") is numeric
0
 
Ryan ChongCommented:
do you mean to get the distinct Event ID from table: tickets ?

if yes, then can try:
Set PackageEvents=oConn.Execute("SELECT tickets.event_ID FROM tickets inner join itin_items on tickets.ticket_ID = itin_items.ticket_ID WHERE itin_ID="&Package("itin_ID")&" AND itin_items.ticket_ID>0 Group By tickets.event_ID")

Open in new window

if this is not what you want then pls visualize your requirements.
0
 
Graemewebber4technologiesAuthor Commented:
Cheers for that, giving an outcome now, however it is showing the same event_ID many times

eg.
90
90
90
90

instead of just once
0
 
Ryan ChongCommented:
did you add the " Group By tickets.event_ID" in your select SQL statement as mentioned in ID: 41861684?
0
 
Graemewebber4technologiesAuthor Commented:
Sorry Sorry! it works, I have the event_ID showing a few times! Cheers!!!
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!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now