Extracting data from two tables

Question

I have( for example) a table called A and a table called B. Table A contains tons of user data( age, country bla bla) and is connected to B by forein_key=id( B only has ID/NAME. I want to find out how many records of each user are there in the table A ( for example a person named 'Gal' has 20records there ) and if its greater than 10 I want to show the user's name from table B aswell as the rest of the data from table A. hope I was understandable. thanks

-- those are the lines that activate my result so far, already pulled the count and the user, now I need to display the rest of the details from my other table (again, they're connected by ID).! thanks again

Answer 1

Let's first create an example:

CREATE TABLE users(
    id int,
    name varchar(50)
);

CREATE TABLE user_info(
    id int,
    id_user int,
    somevalue varchar(50)
);

INSERT INTO users VALUES 
(1, 'John')
,(2, 'Maria')
,(3, 'Jason')
,(4, 'Alice');

INSERT INTO user_info VALUES
(1, 1 , 'gzergzrgzr'),
(2, 1 , 'gzergzrgzr'),
(3, 1 , 'gzergzrgzr'),
(4, 2 , 'gzergzrgzr'),
(5, 2 , 'gzergzrgzr'),
(6, 2 , 'gzergzrgzr'),
(7, 1 , 'gzergzrgzr'),
(8, 1 , 'gzergzrgzr'),
(9, 4 , 'gzergzrgzr'),
(10, 4 , 'gzergzrgzr'),
(11, 4 , 'gzergzrgzr'),
(12, 4 , 'gzergzrgzr'),
(13, 4 , 'gzergzrgzr'),
(14, 3 , 'gzergzrgzr'),
(15, 3 , 'gzergzrgzr'),
(16, 1 , 'gzergzrgzr'),
(17, 4 , 'gzergzrgzr'),
(18, 4 , 'gzergzrgzr'),
(19, 1 , 'gzergzrgzr'),
(20, 1 , 'gzergzrgzr'),
(21, 1 , 'gzergzrgzr'),
(22, 4 , 'gzergzrgzr'),
(23, 4 , 'gzergzrgzr'),
(24, 4 , 'gzergzrgzr'),
(25, 1 , 'gzergzrgzr');

Let's say we want to select all info of the users here that have more than 10 entries. We'll have to work a bit backwards:

• First we filter out all users that have 10 or more entries -we put them in a view (with-clause) using the COUNT keyword to get the ones that have more than 10
• Then we do our select on the tables -JOIN them on their relevant id's and filter out to have only the ones IN our resultset:

Example:

with allten as(
    SELECT u.id FROM users u JOIN user_info ui
    ON ui.id_user = u.id GROUP BY u.id having COUNT(*) >= 10
)

SELECT u.id, u.name, ui.someValue
FROM users u
JOIN user_info ui ON ui.id_user = u.id
WHERE u.id in (select * from allten)

I'm very bad at explaining some things. So if it needs clarification please ask.