Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 5 of 5
  1. #1
    Junior Member
    Join Date
    Oct 2003
    Posts
    9
    Member #
    3731
    Hi There!

    I have got two tables item_responses & item_audits. What i need to do is pick out values from these 2 tables on the basis of a value passed to it. I am using 2 different Select statement to do that.The queries are as follows:

    SELECT parent_ID FROM item_audits WHERE created>=date OR edited>=date

    SELECT parent_ID FROM item_responses WHERE created>=date

    Now,is it possible to combine the two queries to one query?


    Regards
    Bosky

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Bosky,

    You can do this two different ways...

    JOIN: will only select rows existing in BOTH tables:
    Code:
    SELECT DISTINCT a.parent_ID
    FROM item_audits AS a
        INNER JOIN item_responses AS r ON a.parent_ID = r.parent_ID
        WHERE (a.created >= date OR a.edited >= date)
            AND r.created >= date
    ORDER BY parent_ID;
    UNION: will select rows existing in EITHER table
    Code:
    SELECT parent_ID 
    FROM item_audits 
    WHERE created >= date OR edited >= date
    UNION
    SELECT parent_ID 
    FROM item_responses 
    WHERE created >= date;
    Because you're using MySQL, the second query could give you duplicitous results. If you were using a more powerful database, you could eliminate duplicated ID's by creating a sub-query like so:
    Code:
    SELECT DISTINCT parent_ID
    FROM (
        SELECT parent_ID 
        FROM item_audits 
        WHERE created >= date OR edited >= date
        UNION
        SELECT parent_ID 
        FROM item_responses 
        WHERE created >= date;
    ) AS item_query
    ORDER BY parent_ID;

  4. #3
    Junior Member
    Join Date
    Oct 2003
    Posts
    9
    Member #
    3731
    The second code fetches the records as per my requirement but how do i get unique records.

    Thanks a lot for the help :-D

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Originally posted by bosky
    but how do i get unique records.
    Can't do it with your current database. If you were using MySQL 4.0+ or SQL Server or PostgreSQL or Oracle you could do it with the last example I showed you.

    As it is, you'll have to do it programatically using PHP. Just maintain an array of id's found and scan it for each record when you loop through the results. If the id for the current row is not in the array, process it and then add it to the array. If it is in the array, ignore the row.

  6. #5
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Actually, on rethinking it, you might be able to "trick" MySQL into doing a union by doing an outer join, like so:
    Code:
    SELECT DISTINCT 
       (CASE 
            WHEN a.parent_ID IS NULL 
                THEN r.parent_ID 
            ELSE a.parent_ID 
        END) AS parent_ID
    FROM item_audits AS a
        FULL OUTER JOIN item_responses AS r 
            ON a.parent_ID = r.parent_ID
    WHERE a.created >= date 
        OR a.edited >= date
        OR r.created >= date
    ORDER BY parent_ID;
    This might not work, though. I've never actually tried it before, because I don't use MySQL for queries this complex ;-)


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:19 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com