Webmaster Forum / ColdFusion / Advanced Techniques / October 2005

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

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;