3

I have a table named Cars and a function named PartsPerCar

there is no direct link between carid and partid. the link is though 3 tables in the middle. I do have a function called PartsPerCar which gets carid (it goves via the tables in the middle) and give back all the partid related to carid.

for example : if I choose carid=125 I can call PartsPerCar(125) and get:

part id    part name
10          Wheel A
12          Wheel D
13          Glass F

what I want to get eventualy is to get a list of all cars and all it's parts.

something like:

  Car ID ,   Part ID , Part Name
    1            15    engine A
    1            17    engine C
           .
           .
           .
    125         10     Wheel A
    125         12     Wheel D
    125         13     Glass F

the thing is that there is nothing to do join on.

I know I can do:

select PartsPerCar(carid)
from Cars

but it doesn't help me so much as I need the partid to use with the WHERE statement. In this way I can't do that.

Improve this question

1 Answer 1

Reset to default
2

Your table uses a carid value to retrieve the corresponding part_ids from function PartsPerCar() which returns a set of rows, a so-called table function. In sub-section 5 (keep on reading) we see that

Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional; the function's arguments can contain references to columns provided by preceding FROM items in any case.

You have all that you need to perform your query:

SELECT carid, partid, part_name
FROM cars, PartsPerCar(carid) AS carparts;

This creates a CROSS JOIN between the table cars and the set of records from the function PartsPerCar() but since the output of the latter is constrained by the parameter taken from the former it effectively works like a join condition.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

It gives false result. I checked it with count. For Carid 539 i have 96 parts, if i change ur query to SELECT carid, count (partid) FROM cars, PartsPerCar(carid) AS carparts GROUP BY (carid) it give on id 539 count of 960
And what do you get with SELECT count(*) FROM PartsPerCar(539)?
i get 96 which is the real count of parts for this carid. I think I know what the problem is. Table Cars isn't a list of unique Cars... there are 10 records there of car 539... it's working fine. Thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.