MySQL

[dyanamo]

Hi

Basically I want to know if the following is possible and how to do it if it is :

I have 2 tables
(table listing) id, part_id
(table part) id, name

Now when I query (table listing) normally I would get a result say 1,5. The result i want however is 1,"part name"

So basically when I query (table listing) it automatically replaces part_id with the value stored in name in (table part) where the id's match.

I can do this programatically but would prefer to do it database level to minimize calls.

Notes
if (table listing) part_id does not exist in table part must just return a empty result.

 

[dequadin]

SQL from memory may not work:

SELECT tblListing.part_id, tblPart.name, 
FROM tblListing INNER JOIN tblPart ON tblListing.id == tblPart.id
WHERE tblListing.id == YourId

EDIT: Not sure how your tables are joining? tblListing.part_id <=> tblPart.id??

Maybe this:

SELECT tblListing.id, tblPart.name, 
FROM tblListing INNER JOIN tblPart ON tblListing.part_id == tblPart.id
WHERE tblListing.id == YourId

 

[dyanamo]

Yeah can use inner join but would prefer if all this was done automatically. Like a stored procedure or something. So if i say "SELECT * FROM table" does inner join automatically because it knows of the relationship that is stored in the db.

 

[The_Assimilator]

dyanamo, look up creating views.

 

[dequadin]

Yeah can use inner join but would prefer if all this was done automatically. Like a stored procedure or something. So if i say "SELECT * FROM table" does inner join automatically because it knows of the relationship that is stored in the db.

You'd still need to code the JOIN inside the stored procedure, so that doesn't really make sense to me..? The JOIN is done by the database engine anyway?

Maybe create a VIEW, which is basically a hard coded query.

 

[dequadin]

dyanamo, look up creating views.

FGITW!

 

[dyanamo]

Ahh awsome I think thats what i was looking for. thanks.

 

[@udiS3]

CREATE PROCEDURE `myDB`.`myProc` (_id INT)
BEGIN
select * from part where id in (select part_id from listing where id = _id);
END

 

[Nod]

CREATE VIEW parts_list AS
select a.id, b.name
from table_listing a, table_part b
where a.id=b.id;

After that:

select * from parts_list;

 

[TKD01]

(I work with MSSQL but relational database principles is the same)

Best suggestion is Stored Procedure - there is lots of motivation (a simple google search will reveal) but its more flexible, better securable and increased performance etc....

A stored procedure can also accept parameters thus much more scalable (as in example below)

CREATE PROCEDURE `myDB`.`myProc` (_id INT)
BEGIN
select * from part where id in (select part_id from listing where id = _id);
END

VERY BAD PRACTICE!! This is going to result in unnecessary table scans - and if you're working with 100 000+ records serious impact on performance.


Best practice (in order):
1) INNER JOIN
2) RIGHT / LEFT JOINS
3) KEY Column join (ex: select * from table_1, table_2 where table_1.KEY = table_2.KEY)
4) Keywords: LIKE, IN, NOT, <> ...

For more on this, google SQL Query Optimiser and Execution plans... Will give you an idea of how SQL engine operates (again this is from MSSQL perspective)



Lastly,

if (table listing) part_id does not exist in table part must just return a empty result.

In practice, you're more likely to want something returned explaining an empty result like "No part number exists" or something similar...
If you want to return the parent ([table listing], id) even if the child does not exist, the query can be something like this:

select [table listing].id, isnull([table part].name, 'No part name')
from [table listing] RIGHT OUTER JOIN [table part]
ON [table listing].part_id = [table part].id

It all depends on what you want to return. Explicitly naming the columns in the select again is good practise (as opposed to "select * ")

 

[FarligOpptreden]

(I work with MSSQL but relational database principles is the same)
Best practice (in order):
1) INNER JOIN
2) RIGHT / LEFT JOINS
3) KEY Column join (ex: select * from table_1, table_2 where table_1.KEY = table_2.KEY)
4) Keywords: LIKE, IN, NOT, <> ...

You forgot UNION, INTERSECT and EXCEPT. Those will be faster than filtering out using IN or NOT IN...

 

[TKD01]

You forgot UNION, INTERSECT and EXCEPT. Those will be faster than filtering out using IN or NOT IN...

You are absolutely correct

 

[DarkStreet]

3) KEY Column join (ex: select * from table_1, table_2 where table_1.KEY = table_2.KEY)

Only say * if you need all the column values, if you only need a few columns, name them. I had to optimise queries for a client's site and specifying column names literally brought this one query down from 12 seconds to 0.0006 seconds.

 

[TKD01]

Only say * if you need all the column values, if you only need a few columns, name them. I had to optimise queries for a client's site and specifying column names literally brought this one query down from 12 seconds to 0.0006 seconds.

Last line of my post:

Explicitly naming the columns in the select again is good practise (as opposed to "select * ")

;-)

 

[DarkStreet]

Yeah, only saw that after I read your post in it's entirety

 

[@udiS3]

"VERY BAD PRACTICE!! This is going to result in unnecessary table scans - and if you're working with 100 000+ records serious impact on performance."

You are quite right as a general rule of thumb...
However in this case we are dealing with a 1-1 mapping
and I would go with 'IN'

Have a look at this:
http://explainextended.com/2009/06/16/in-vs-join-vs-exists/