• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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