Probably a very simple JOIN question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Probably a very simple JOIN question

Post 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 :P

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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It appears that should work..
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post by jayshields »

Ok, I'll try that out.

Can't yet, need to populate my tables...!
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post 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&#39;m naughty, are you naughty?'>smurf</span> out of new comers to SQL.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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.
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Post 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.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post 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 :wink:

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
Post Reply