-2

I am trying to write a query which is hypothetical at this point that would look something like this:

SELECT customers.*, 
       customers_cars.car
        (SELECT main_vehicle 
         FROM customers 
         WHERE customer='Joe') 
FROM customers 
WHERE type='CoolCustomer';

My table actually has values like car1, car2, car3 and I'd like to have those values "formatted" on runtime. Unfortunately, I don't know how to do that.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Stewie
  • 23
  • 2
  • so what is the error you are getting – Satya May 07 '12 at 01:14
  • coulld you pl take a look at this segment customers_cars.car(SELECT main_vehicle FROM customers WHERE customer='Joe') and correct the err – Satya May 07 '12 at 01:22
  • 2
    That is not valid syntax in any flavor of SQL that I'm aware of. –  May 07 '12 at 02:04
  • 3
    And "just doesn't work" is not a built-in error message in any RDBMS. –  May 07 '12 at 02:06
  • 1
    @Jack Maney: there is a special newbies edition that literally outputs "I don't work" I suppose – zerkms May 07 '12 at 02:11
  • 2
    You can practice query logic at http://sqlzoo.net , it has sample data. You can also practice at http://sqlfiddle.com , it has no sample data however. SqlFiddle is the bee's knees. Its Text to DDL is very cool, accepts virtually all kind of table format; it can even run multiple statements in one go, you can see the each result neatly segregated; of which for example pgAdmin(Postgresql admin tool) cannot, pgAdmin can only output the result of the first query of multiple queries, succeeding queries doesn't appear on grid – Michael Buen May 07 '12 at 02:52
  • @MichaelBuen thanks for the sql links, i hadn't known about this, will check them out... – tim peterson May 07 '12 at 03:24

2 Answers2

0

You've not shown us the schema of your table; that makes it harder than it might be to help you.

If you are seeking to get car1 column or car2 column or ... then there are at least two problems:

  1. Your table is incorrectly designed.
  2. You cannot build up the name of a column (or table) as part of the query.

You should put the information about each of a customer's cars in a separate table from the information about the customer. Thus, the information that the customer's name is 'Joe' belongs in one table; the fact that he drives a Lamborghini should be recorded in a separate table. There might be multiple records in the table of cars for each customer.

You'd also need to join the two tables; your syntactically incorrect query doesn't attempt to do that.

You have two tables with columns:

  • Customers: customer, type, ...

  • Customers_Cars: main_vehicle, car1, car2, car3

You probably have, but haven't shown, some sort of 'customer' column in Customers_Cars. In fact, you should probably have a 'Customer ID' column in each table. Further, the Customers_Cars table should be just:

  • Customers_Cars: CustomerID, CarNum, CarInfo, MainVehicle

In this, I'm assuming MainVehicle is a boolean flag, and that there is one entry tagged true per customer.

Your SELECT statement then becomes:

SELECT U.*, 
       C.CarInfo
  FROM Customers      AS U
  JOIN Customers_Cars AS C ON C.CustomerID = U.CustomerID
 WHERE C.MainVehicle = TRUE
   AND U.Customer = 'Joe'
   AND U.Type     = 'CoolCustomer';
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
0

i think you are referring to giving columns alias names. Please see this reference on use of aliases: Using column alias in WHERE clause of MySQL query produces an error

But the bottom line with aliases is to use the AS keyword. You may require an alias when you use a subquery as you are doing here with the query in parentheses.

In your example, try the following:

SELECT customers.*, car1 AS customers_cars.car
   (SELECT car2 AS main_vehicle 
     FROM customers 
     WHERE customer='Joe') 
FROM customers 
WHERE type='CoolCustomer';
Community
  • 1
  • 1
tim peterson
  • 23,653
  • 59
  • 177
  • 299