Not unique table/alias

[lurtnowski]

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?

[DaveMaxwell]

@lurtnowski

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

Read that query again…

[lurtnowski]

dang, wrote it wrong

[DaveMaxwell]

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.

[lurtnowski]

@lurtnowski

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

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

[lurtnowski]

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

[r937]

it seems you have forgotten your GROUP BY clause

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

my bad

[lurtnowski]

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

[r937]

the same as what?

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

[lurtnowski]

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

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?

[r937]

@lurtnowski

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?