Not unique table/alias

I have 2 tables, servers and ports…
Ports has a foreign key which is the primary key in the servers table
I have

INSERT INTO servers (server_id,name) VALUES (1, 'Test Server');

INSERT INTO ports (port_id,server_id) VALUES (1,1);
INSERT INTO ports (port_id,server_id) VALUES (2,1);
INSERT INTO ports (port_id,server_id) VALUES (3,1);

I’m trying to query the database so I get the count of ports the server has as well as the servers name…Like

SELECT servers.name, COUNT(port_id)
FROM ports
INNER JOIN ports ON servers.servers_id = ports.server_id;

I get the error


Whats the deal?

Read that query again… :shifty:

dang, wrote it wrong

We’ve all done it…after looking at it forever and sure it’s right, then you show it to someone and they go “it’s right there” and you want to go hide in a hole. :lol:

1 Like

thats the way it goes, if you write it out, the problem is obvious.

1 Like

found a problem, the query is

SELECT chassises.chassis_id AS asset_id,chassises.name,asset_type,chassises.created_by,chassises.created_date,count(port_id)
        FROM ports
		INNER JOIN chassises ON ports.chassis_id = chassises.chassis_id

the result


I dont get why the count(port_id) result is 4, shouldn’t it be 3?
Heres an exert from the ports table
image

it seems you have forgotten your GROUP BY clause

edit oh wait, you’ve moved on from the COUNT(*) query?

my bad

Do I need something to ensure the ports.chassis_id is the same when they are counted?

the same as what?

please show the query you ran which includes a GROUP BY clause

Dang, I really bad at explaining…
I have 2 tables which are connected (ports foreign key is chassises primary key. Im trying to put data from both using

SELECT chassises.chassis_id AS asset_id,chassises.name,asset_type,chassises.created_by,chassises.created_date,count(port_id)
        FROM ports
		INNER JOIN chassises ON ports.chassis_id = chassises.chassis_id

image
If I look in the ports table, only 3 ports have a chassis_id of 1 and only 1 has a chassis_id of 2 .
If I attach a GROUP BY clause I get


Thats the correct result, is this ok if I do this?

gee, what do you think?

p.s. i cannot read your screenshots

would it really be too much trouble to copy/paste the actual SQL here?

1 Like