Professional Web Applications Themes

Advanced sorting with multiple tables - Coldfusion - Advanced Techniques

I have 3 separate tables with the same ID corresponding to what matches to what. What I would like to do is create a simple output where I can sort, but between the three tables. Currently, I just have one select query and then in the loop I have selects for the 2 other queries to pull that info, but now I wish to sort by all three tables? Is there some sort of union/join type of query I can do that I can do? Table 1 ID, Year, Make, Model, Examples: 1, 2004, Ford, Taurus 2, 2005, Honda, Civic ...

  1. #1

    Default Advanced sorting with multiple tables

    I have 3 separate tables with the same ID corresponding to what matches to
    what. What I would like to do is create a simple output where I can sort, but
    between the three tables. Currently, I just have one select query and then in
    the loop I have selects for the 2 other queries to pull that info, but now I
    wish to sort by all three tables? Is there some sort of union/join type of
    query I can do that I can do?

    Table 1
    ID, Year, Make, Model,

    Examples:
    1, 2004, Ford, Taurus
    2, 2005, Honda, Civic

    Table 2
    ID, Cost

    Examples:
    2, 15000

    Table 3
    ID, User

    Examples:
    1, Bob

    So my output looks like this:
    1, 2004, Ford, Taurus, , Bob
    2, 2005, Honda, Civic, 15000,

    What I would like to be is be able to sort by all of these features even
    though they come from different tables. Is this possible?:confused;

    annamjmi Guest

  2. #2

    Default Re: Advanced sorting with multiple tables

    Say Table 1 is called Car
    Table 2 is CarCost
    Table 3 is CarOwner

    SELECT Car.Year, Car.Make, Car.Model, CarCost.Cost, CarOwner.User
    FROM Car INNER JOIN CarCost on Car.ID=CarCost.ID
    INNER JOIN CarOwner ON Car.ID=CarOwner.ID

    And you can order or filer on any of these.
    ie add on WHERE Car.Year > 2000
    OR ORDER BY Car.Year, Car.Mike, Car.Model

    Kling Guest

  3. #3

    Default Re: Advanced sorting with multiple tables

    Not with that table structure.
    First, unless a car can have more than one cost, or the cost of more than one
    car can be changed by the same amount, why don't you make cost a field of the
    car table instead of a separate table?

    Next, you need another table to store who is using what car. This will have
    either a one to many or many to many relationship between users and cars,
    depending on your business rules.

    Originally posted by: annamjmi
    I have 3 separate tables with the same ID corresponding to what matches to
    what. What I would like to do is create a simple output where I can sort, but
    between the three tables. Currently, I just have one select query and then in
    the loop I have selects for the 2 other queries to pull that info, but now I
    wish to sort by all three tables? Is there some sort of union/join type of
    query I can do that I can do?

    Table 1
    ID, Year, Make, Model,

    Examples:
    1, 2004, Ford, Taurus
    2, 2005, Honda, Civic

    Table 2
    ID, Cost

    Examples:
    2, 15000

    Table 3
    ID, User

    Examples:
    1, Bob

    So my output looks like this:
    1, 2004, Ford, Taurus, , Bob
    2, 2005, Honda, Civic, 15000,

    What I would like to be is be able to sort by all of these features even
    though they come from different tables. Is this possible?:confused;



    Dan Guest

Similar Threads

  1. CS - InDesign Tables - Sorting
    By Jamison_Deluski@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 2
    Last Post: September 21st, 09:53 PM
  2. Replies: 4
    Last Post: May 22nd, 05:58 PM
  3. Sorting HTML tables
    By Perl in forum PERL Beginners
    Replies: 3
    Last Post: August 6th, 12:53 PM
  4. Tables and Sorting
    By Darren in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: November 28th, 10:26 AM
  5. sorting by multiple criterias (sub-sorting)
    By Tom in forum PERL Miscellaneous
    Replies: 3
    Last Post: October 11th, 05:16 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139