help - how to get different values from same table

Dear Friends,
I have 2 tables --> User and Car. Both have unique ids and each user has 2 cars.

for example,
Jim has Ford and Honda
Jill has Toyota and Subara
Jack has Toyota and Honda
John has Ford and Ford

So I am storing the 2 car ids in the user table. I am not creating any bridge table.

So in the report I want the user and their car. But I get the following report on the above data:

Jim -- Ford
Jill -- Toyota
Jack -- Toyota
John -- Ford -- Ford

Notice, that the first 3 records just display first car. The forth record displays both cars.

In the crystal report, I did like this in the "SQL Expression Fields" to get the name of first car:

(SELECT CAR.NAME WHERE USER.FIRST_CAR_ID = CAR.CAR_ID)
to get the name of second car:

(select CAR.NAME where USER.SECOND_CAR_ID = CAR.CAR_ID)
Kindly help me to resolve this problem.
Thanks.

[972 byte] By [surajguru] at [2007-12-16]

# 1

I suppose you need to select the cars table twice in your query with different aliases, something like the following.

SELECT user.name, car1.name, car2.name
FROM user, car as car1, car as car2
WHERE car1.car_id = user.first_car_id and
car2.car_id = user.second_car_id

Rahim at 2007-9-9 >

# 2

Hi Rahim,
Thanks for the code, it gets the proper names and generates the report as required.
Thanks again.

surajguru at 2007-9-9 >

Visual Basic Hot Topic