Page 1 of 1
Probably a very simple JOIN question
Posted: Wed Jan 25, 2006 8:24 am
by jayshields
Hi guys,
I don't have an awful lot of experience with SQL, and I've managed to get stuck at the first semi-advanced hurdle
I've got a cars table and a makes table. The makes table contains 2 columns, make_id and make, make_id being an auto incremented primary key and make being the make name. The cars table contains loads of details about loads of cars, and uses the column make_id, which refers to the make in the makes table.
I want to fetch all the data from the cars table, but I want it to pull the actual make instead of the make_id. I don't even think I need a join, maybe it's easier.
I've thought about trying this but I think it will be way off:
Code: Select all
SELECT car_id, make, model, description, price FROM cars, makes WHERE cars.make_id = makes.make_id
Sorry if that's close, I haven't tried it because it will mess up the rest of my application and cause me to change some of the code, so I thought it would be best to check first.
What is the correct SELECT query to use?
Thanks.
Posted: Wed Jan 25, 2006 8:29 am
by JayBird
Soemthing like this might do, but i dont know your exact table structure, so may need tweaking
Code: Select all
SELECT `c`.`car_id`, `m`.`make`, `m`.`make_id`, `c`.`model`, `c`.`description`, `c`.`price` FROM `cars` AS `c`, `makes` AS `m` WHERE `c`.`make_id` = `m`.`make_id`GROUP BY `c`.`car_id`
Not sure if you'll need the group clause
Posted: Wed Jan 25, 2006 8:31 am
by feyd
It appears that should work..
Posted: Wed Jan 25, 2006 1:27 pm
by jayshields
Ok, I'll try that out.
Can't yet, need to populate my tables...!
Posted: Wed Jan 25, 2006 5:29 pm
by ody
Pimptastic wrote:Soemthing like this might do, but i dont know your exact table structure, so may need tweaking
Code: Select all
SELECT `c`.`car_id`, `m`.`make`, `m`.`make_id`, `c`.`model`, `c`.`description`, `c`.`price` FROM `cars` AS `c`, `makes` AS `m` WHERE `c`.`make_id` = `m`.`make_id`GROUP BY `c`.`car_id`
Not sure if you'll need the group clause
Woo, that's some impressive "over engineering" you got going on there! Seriously re-frame from the back tick madness when its not needed, it does nothing but scare the <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> out of new comers to SQL.
Posted: Wed Jan 25, 2006 5:53 pm
by raghavan20
ody wrote:Pimptastic wrote:Soemthing like this might do, but i dont know your exact table structure, so may need tweaking
Code: Select all
SELECT `c`.`car_id`, `m`.`make`, `m`.`make_id`, `c`.`model`, `c`.`description`, `c`.`price` FROM `cars` AS `c`, `makes` AS `m` WHERE `c`.`make_id` = `m`.`make_id`GROUP BY `c`.`car_id`
Not sure if you'll need the group clause
Woo, that's some impressive "over engineering" you got going on there! Seriously re-frame from the back tick madness when its not needed, it does nothing but scare the <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> out of new comers to SQL.
When you are scared, you will pay more attention to what has been done. Backticks should be used but not really so much necessary for aliases with single characters.
Posted: Thu Jan 26, 2006 3:18 am
by jayshields
ody wrote:Pimptastic wrote:Soemthing like this might do, but i dont know your exact table structure, so may need tweaking
Code: Select all
SELECT `c`.`car_id`, `m`.`make`, `m`.`make_id`, `c`.`model`, `c`.`description`, `c`.`price` FROM `cars` AS `c`, `makes` AS `m` WHERE `c`.`make_id` = `m`.`make_id`GROUP BY `c`.`car_id`
Not sure if you'll need the group clause
Woo, that's some impressive "over engineering" you got going on there! Seriously re-frame from the back tick madness when its not needed, it does nothing but scare the <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> out of new comers to SQL.
I agree in a sense but using backticks at every given moment is good practice for new comers.
Posted: Thu Jan 26, 2006 3:18 am
by JayBird
ody wrote:Woo, that's some impressive "over engineering" you got going on there! Seriously re-frame from the back tick madness when its not needed, it does nothing but scare the <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> out of new comers to SQL.
Overuse, i think not!
I never used to use the backtick harldy ever, until i tried to move a load of code to a new server, where the queries wouldn't run without them. Now i am just in the habit of doing it to avoid the same problem in the future.
So, on the fateful day when you try and move some code over to a server that
REQUIRES the use of backticks....think back to this thread and how I was following best practice for portability.
As for scaring new comers...new comers should be taught best practice from the start IMO
raghavan20 wrote:When you are scared, you will pay more attention to what has been done. Backticks should be used but not really so much necessary for aliases with single characters.
And this is quite correct...but if im using backticks, then i like to bactick everything for consitency