SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member neil_crookes's Avatar
    Join Date
    Aug 2002
    Location
    Nottingham, UK
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Complex SELECT - please help

    Consider a directory (as in Yellow Pages) which consists of categories. Each category contains entries/listings. The entries come from multiple sources (3 or 4). The majority of the type of information provided about these entries is consistent accross the different sources, but not identical for each source. Therefore, for ease of maintenance and efficient storage, each source has its own table (source1, source2, source3 etc...). In addition there is another table which stores the information about which entries from which source are allocated to each category, (called the allocations table). I need to perform a SELECT query to get all the appropriate entries from all the source tables given a particular category ID. I need them all in one result set so I can add an ORDER BY clause on one or more of the common fields. I would also like a single set of generic fields in the result set for ease of post-processing (PHP), i.e. Field1, Field2, Field3, Field4, Field5, however note that one or more of the fields may not exist in every source table, in which case NULL or empty string should be returned. The fields are not currently named the same, but that could be altered if required but I'd prefer to just alias them.

    Therefore the allocations table has 3 fields:
    1 Category ID [cid] int
    2 Entry ID [eid] int
    3 Source Table [src] varchar
    ... and each source table has several fields such as:
    1 Entry ID [src1_eid], [or src2_eid etc...] int
    2 Field 2 [src1_field2], [or src2_field2 etc...] varchar
    3 Field 3 [src1_field3], [or src2_field3 etc...] varchar
    4 Field 4 [src1_field4], [or src2_field4 etc...] varchar

    To complicate things a little further, the same entry can be included in multiple source tables. In which case I do not want to duplicate the entry in the directory listings, however I do want to extract all instances of one of the common fields (its a URL that is different for each entry and for each source). There will be no reliable way to identify these duplicates except by my inspection, and telling the system which source table should be the primary one. Duplicates will not be a frequent occurrence so this is not a problem as long as they are identified initially and the fact recorded for all subsequent queries. I'm thinking I'll need another 'duplicates' table for this purpose to identify the eid and the source of the primary source, and the eid and the source of the duplicate source table.

    This has had me stumped for a couple of days now - I'd really appreciate some assistance.

    Neil.

    PHP Version 3.0.18
    MySQL Version 3.23.41
    Apologies for ancient versions
    Last edited by neil_crookes; Jul 3, 2003 at 01:33.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    >> Apologies for ancient versions

    look up the UNION operator in the mysql docs

    then upgrade to 4.0 if you want it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member neil_crookes's Avatar
    Join Date
    Aug 2002
    Location
    Nottingham, UK
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the tip - I've been trying different things all morning and came up with a monster query that has started the ball rolling in the right direction (I'll have a look at UNION in a minute):
    Code:
    SELECT
    allocations.source AS Source,
    
    CASE allocations.source WHEN 'source1' THEN source1.src1_id WHEN 'source2' THEN source2.src2_id WHEN 'source3' THEN source3.src3_id END AS ID,
    
    CASE allocations.source WHEN 'source1' THEN source1.src1_field2 WHEN 'source2' THEN source2.src2_field2 WHEN 'source3' THEN source3.src3_field2 END AS field2,
    
    CASE allocations.source WHEN 'source1' THEN source1.src1_field3 WHEN 'source2' THEN source2.src2_field3 WHEN 'source3' THEN source3.src3_field3 END AS field3,
    
    CASE allocations.source WHEN 'source1' THEN source1.src1_field4 WHEN 'source2' THEN source2.src2_field4 WHEN 'source3' THEN source3.src3_field4 END AS field4,
    
    CASE allocations.source WHEN 'source1' THEN source1.src1_field5 WHEN 'source2' THEN source2.src2_field5 WHEN 'source3' THEN source3.src3_field5 END AS field5,
    
    CASE allocations.source WHEN 'source1' THEN source1.src1_field6 WHEN 'source2' THEN '' WHEN 'source3' THEN '' END AS field6,
    
    CASE allocations.source WHEN 'source1' THEN source1.src1_field7 
    WHEN 'source2' THEN '' WHEN 'source3' THEN '' END AS field7,
    
    CASE allocations.source WHEN 'source1' THEN source1.src1_field8 WHEN 'source2' THEN '' WHEN 'source3' THEN '' END AS field8,
    
    CASE allocations.source WHEN 'source1' THEN source1.src1_field9 WHEN 'source2' THEN source2.src2_field9 WHEN 'source3' THEN source3.src3_field9 END AS field9,
    
    CASE allocations.source WHEN 'source1' THEN source1.src1_field10 WHEN 'source2' THEN source2.src2_field10 WHEN 'source3' THEN source3.src3_field10 END AS field10,
    
    CASE allocations.source WHEN 'source1' THEN source1.src1_field11 WHEN 'source2' THEN source2.src2_field11 WHEN 'source3' THEN source3.src3_field11 END AS field11
    
    FROM
    allocations
    LEFT JOIN
    source1
    ON allocations.eid = source1.src1_id
    LEFT JOIN
    source2
    ON allocations.eid = source2.src2_id
    LEFT JOIN
    source3
    ON allocations.eid = source3.src3_id
    WHERE
    allocations.cid = $CID
    ORDER BY field2
    (Sorry about the formatting - don't know why it won't do new lines in the preview! or the CODE section)

    This query works for 3 source tables, with up to 11 fields in each, and fulfills the first objective of getting data from different tables into the single result set with common field names. However I've not figured out the duplicate issue yet. Do you think the UNION operator will provide advantages over doing it this way? Not really fussed about speed as will only be done 2 or 3 times a day when entries change.
    Neil Crookes


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
  •