begginer question about sql query

[umen242]

Hello all im total beginner that never did more then simple select / insert sql
but now i need something more complex and i need help
im looking to construct sql statement that will do the flowing :
i have 2 tables
one is cars that has 3 column
car_id,car_name,car_type_id
and i have another types that called car_types that has 2 coulums
car_type_id,car_type_name
i like to get all car_names and there car_type_name based on this 2 tables
what i need to do ?

[Fou-Lu]

You need to join you're two tables together. Depending on what you want to do with empty rows depends on what kind of join you'll use (LEFT JOIN if you want to keep a car with an invalid car type, inner join if you only want to keep both that are valid, and right join if you want all car types and any matching car).


SELECT c.car_id, c.car_name, ct.car_type_name
FROM cars c
INNER JOIN car_types ct ON (ct.car_type_id = c.car_type_id)


As you move into a more normalized level of databases, you'll be using a lot more joins. You'll get plenty of practice on them thats for sure.

[umen242]

Hello and thanks for the reply
the example is working great but i now like to compere between columns that contains strings and not integers with this
inner join .
but with no success

i just replaced :
JOIN car_types
ON car_types.car_type_name = cars.car_type_name

and it gives me error :
Error: Unknown column 'car_types.car_type_name in 'on clause'

any idea why ?

[Fou-Lu]

Its says that car_type_name doesn't exist in table car_type.
Post you're full query.

According to you're initial schema though, its the cars.car_type_name that should be causing it (since that field doesn't exist).

[umen242]

hello and thanks for the replay , well its working great
but now i add new column to the tables and i like to insert data to all of them
at once
im trying to do :
insert into car_types(car_types_insertion) values(now()) where car_type_id > 0;

im getting error .. how can i insert to all the columns the data ?
Thanks

[oracleguy]

hello and thanks for the replay , well its working great
but now i add new column to the tables and i like to insert data to all of them
at once
im trying to do :
insert into car_types(car_types_insertion) values(now()) where car_type_id > 0;

im getting error .. how can i insert to all the columns the data ?
Thanks

Insert statements cannot have where clauses, an insert creates a new row. If you want to update a column in one or more rows you need to use an update statement.


UPDATE car_types SET car_types_insertion = now() WHERE car_type_id > 0