SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Italy
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Conditional JOINs

    Hi all,

    Quick description:
    4 Tables: Names, Presentations, NormalSessions, ParallelSessions.
    Names and Presentations are joined by a common field (UserID) - no problem.
    Presentations has a field called 'Session'; this field can be simply n (an integer, that enables me to join it to NormalSessions.SID), or P-n (in which case n is used to join Presentations to ParallelSessions.PS_ID).
    So far, I've always had to deal with EITHER NormalSessions or ParallelSessions, so my queries could be done quite simply. Now, however, I'd like to run a query on all the Presentations, linking all the tables... I've been trying different approaches and even if logic seems to be on my side, mySQL disagrees and gives me a lot of garbage... :'(
    I'm sure that mySQL is right, though, and I'd appreciate a word of wisdom from those of you more experienced in these logical battles
    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,269
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the design should change so that you have two separate columns, not n and p-n
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Italy
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I suspected that, but it will drive me nuts changing something that's in place already... Can you see no way around the problem? Thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,269
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    write a query to join to NormalSessions, then write another query to join to ParallelSessions, the use UNION ALL to combine the two queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Italy
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah... My lack of experience shows: I haven't used UNION ALL before...
    But I'll read through the manual now - thanks for the pointer.

  6. #6
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Italy
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again,

    I read the manual, scratched the old head, got a couple of neurons to fire and in the end I solved the problem differently. One "old-fashioned" query (no joins), but with an IF condition halfway through it; in case it helps others in this forum, here's the "trick"...
    Code:
    IF	
    	(
    	SUBSTRING(P.Session,1,1) = 'P'
    	,
    	SUBSTRING(P.Session,3) = PS.PS_ID
    	AND
    	PS.SID = N.SID
    	,
    	P.Session = N.SID
    	)
    The funny formatting is just to make things clearer -- I hope!
    I'm still not convinced that this is the most elegant solution but, hey, it works!

    Thanks for the input, anyway.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •