Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: UNION ALL, Joins and no Joins

    I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,

    Table A Contains a Main Image, this image is displayed in the results
    Table B Contains an Icon, this image is displayed in the results
    Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.

    Select title, description, image from tableA
    UNION ALL
    Select title, description, icon as image from tableB
    UNION ALL
    title, description, ( inner Join SELECT top(1)
    from imageTableC where imagetableC.FK = tableC.PK)
    as image from tableC


    Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.

  2. #2
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    Are you using SQL 2000 or SQL 2005? In 2005 you can create a user defined function and use the CROSS APPLY join operator.

    Code:
    CREATE FUNCTION dbo.TopImage (@PK AS int)
    RETURNS TABLE
    AS
    RETURN
    SELECT TOP 1 Image
    FROM imagetableC
    WHERE FK = @PK
    GO
    Then you can call it like this.
    Code:
    Select title, description, image from tableA
    UNION ALL
    Select title, description, icon as image from tableB
    UNION ALL
    SELECT title, description, Top1.image
    FROM tableC
      CROSS APPLY TopImage(PK) AS Top1
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Yeah SQL 2005, Ill give that a try, thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Do NOT use User-defined function for this. It is both inefficient and unnecessary.
    Same for CROSS APPLY.
    You need to explain to us what you mean by TOP image. Neither your post, nor wayneph's code includes any sort order, so TOP is meaningless and you won't be able to guarantee consistent results between consecutive executions.
    You also need to supply us with the DDL of your tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    The only other thing I can think of to get the top 1 record for other rows would be to use the RANK and or ROWNUMBER functions. Obviously which also would require an ORDER BY which I did leave out above.

    At the risk of thread jacking, how else would you match the records in one table with just 1 of the records in a 1 to many relationship?
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The first step is to decide which one of the many is appropriate for the join. Then you can either snipe that row out via the join or the WHERE clause.

    -PatP

  7. #7
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Blindman, the Data of this table is random (wiki), Ive associated certain images with certain wiki data which are inserted directly into the wikidata text as child images in the image table, hope that makes sense, Ideally, this would grab an image an place it next to this text in the result set for a more aesthetic and identifiable display, the image or its order is not particularly important, hopefully somewhat unique for all items but not necessary, and the image within the returned text is stripped using a req exp.

  8. #8
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Works a treat,

    It will be stored in the web cache, so if slow, it won't matter to much,

    CREATE FUNCTION fncGetWikiinfoImage (@ID as int
    )RETURNS TABLE
    AS
    RETURN
    (
    -- Add the SELECT statement with parameter references here
    SELECT TOP 1 wiki_Images.sImage FROM
    wiki_Images
    WHERE wiki_Images.wikiID = @ID OR
    wiki_Images.ParentID = @ID
    ORDER BY NEWID
    )
    GO

    Can I place an Order By new ID in here?, it doesn't seem to like it? Can I make the Return table to something like return image, then just place the newID in the function call like normal?

  9. #9
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Try this
    (sorry this syntax is from memory, no sql box on pc to test it with)

    CREATE FUNCTION fncGetWikiinfoImage (@ID as int
    )RETURNS [ImageDataType]
    AS
    RETURN
    (
    SELECT TOP 1 Image FROM
    (
    SELECT NewID() id, wiki_Images.sImage Image
    FROM wiki_Images
    WHERE wiki_Images.wikiID = @ID OR
    wiki_Images.ParentID = @ID
    ) dt
    ORDER BY id
    )
    GO


    This should be done with a correlated sub query rather than function, but as your popping a web page with only 1 / a couple of rows would'nt make much diff to speed anyway.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by wayneph
    The only other thing I can think of to get the top 1 record for other rows would be to use the RANK and or ROWNUMBER functions.
    I can think of MIN and MAX. Look them up.

    Nate:
    Please understand that if your table has a mere 1000 records, that function will need to run 1000 times in order to return the results if you include it in the SELECT clause of an SQL statement.
    Please understand that the function will need to make a table scan each time it is run, so...1000 table scans.
    Please understand that you cannot use NewID within a user-defined function, as it is niladic.
    Please understand that NewID() generates a random value, so you would get a different result each time the function is executed, even if it did work.

    Now, if you really want a workable, efficient solution to this issue, do what I asked in my previous quote. Otherwise, I'll step out of this thread.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Fair enough a thousand records want take long to reach, whats the syntax to return the image as a join for this table

    Select title, description, image from tableA
    UNION ALL
    Select title, description, icon as image from tableB
    UNION ALL
    title, description,
    (inner Join SELECT top(1)
    from imageTableC where imagetableC.FK = tableC.PK order by NEWID())
    as image from tableC


    The NewID is not required but would be better as 15 or so topics will then not have the same image.

  12. #12
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Wot a Plonker I am, forgot about using non-determnisic functions wthin a UDF, sorry.

    An interesting point for Info is that SQL2005 does now allow the following in UDF's

    CURRENT_TIMESTAMP
    @@MAX_CONNECTIONS
    GET_TRANSMISSION_STATUS
    @@PACK_RECEIVED
    GETDATE
    @@PACK_SENT
    GETUTCDATE
    @@PACKET_ERRORS
    @@CONNECTIONS
    @@TIMETICKS
    @@CPU_BUSY
    @@TOTAL_ERRORS
    @@DBTS
    @@TOTAL_READ
    @@IDLE
    @@TOTAL_WRITE and
    @@IO_BUSY

    Nate If I were you I would quickly explain which of the many (child) images you wish to return.

    MIN - MAX - Random - (don't care = MAX)

    possibly try this?

    Select title, description, image from
    (
    Select Id,title, description, image from tableA
    UNION ALL
    Select id, title, description, icon from tableB
    UNION ALL
    Select c.id, c.title, c.description,(SELECT MAX(sImage) FROM wiki_Images wi
    WHERE wi.id = c.id)
    FROM TableC c
    ) dt
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Nate1
    Fair enough a thousand records want take long to reach, whats the syntax to return the image as a join for this table

    Select title, description, image from tableA
    UNION ALL
    Select title, description, icon as image from tableB
    UNION ALL
    title, description,
    (inner Join SELECT top(1)
    from imageTableC where imagetableC.FK = tableC.PK order by NEWID())
    as image from tableC


    The NewID is not required but would be better as 15 or so topics will then not have the same image.
    Too many syntax errors for me to bother with. When DDL is posted, I'll provide a solution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    GWilly, For that particular table there is a folder of images that is available for Users to insert inline images into the wiki, these images upon insert are registered against the child table, then are available to be displayed in the advanced search function as "a" image for that topic/article? it might sound confusing but its not. The Idea is that in this particular folder 6 - 7 topics may request images, If I do max or whatever, the images are likely to end up as the same image for these topics, I would prefer the system to make a random selection of these items.

  15. #15
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    mmmm - OK - Random (possibly) it is then

    Code:
    
    Select title, description,image from
    (
    Select Id,title, description,image from tableA
    UNION ALL
    Select id, title, description, icon from tableB
    UNION ALL
    Select c.id, c.title, c.description,
    (SELECT sImage FROM
    (SELECT TOP 1 sImage 
    FROM wiki_Images wi WHERE wi.id = c.id ORDER BY NewID())dt1
    )
    FROM TableC c
    ) dt2
    
    Last edited by GWilliy; 03-03-08 at 10:37.
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

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