Igor Tandetnik wrote:
> Stef Mientki <s.mien...@ru.nl> wrote:
>   
>> I'm just an amateur  struggling with databases,
>> so forgive if  I ask  a question every database user should know.
>>
>> The situation is like this (simplified):
>>
>> I've a table with persons: name, address, phone, etc.
>> I've another table with vehicles: car_or_bike, brand, type,
>> build-year, etc. The 2 tables are bounded by bind table: person_ID,
>> vehicle_ID
>>
>> I know that every person has 1 car and 1 bike
>>
>> I want to show up a table, with 3 columns:
>>      persons.name          vehicle.brand[car]
>> vehicle.brand[bike]
>>     
>
> select p.name, v1.brand, v2.brand
> from persons p join bind b1 on (p.person_ID = b1.person_ID)
>     join vechicles v1 on (b1.vehicle_ID = v1.vehicle_ID and 
> v1.car_or_bike = 'car')
>     join bind b2 on (p.person_ID = b2.person_ID)
>     join vechicles v2 on (b2.vehicle_ID = v2.vehicle_ID and 
> v2.car_or_bike = 'bike');
>
> Igor Tandetnik 
>
>   
thanks Igor,

That works very good and fast, and still very readable code.
I just tried it with 1 to 5 columns on the same database
and it's much faster then mine 1 to 2 columns.
I tried some code quiet similar to yours, but got errors,
now I see an essental part is to use aliases for the tables used more 
than once.

thanks!

cheers,
Stef
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to