| |
SQL JOIN |
| |
Hertzsprung (13:42 21/3/2003)
Phlamethrower (14:00 21/3/2003)
ksattic (18:49 21/3/2003)
Phlamethrower (18:57 21/3/2003)
ksattic (19:35 21/3/2003)
ksattic (19:58 21/3/2003)
Matthew (15:57 23/3/2003)
ksattic (16:51 23/3/2003)
Hertzsprung (12:39 25/3/2003)
Phlamethrower (12:51 25/3/2003)
Hertzsprung (13:02 25/3/2003)
Hertzsprung (13:52 25/3/2003)
Phlamethrower (14:16 25/3/2003)
Hertzsprung (14:44 25/3/2003)
mripley (15:05 1/4/2003)
Matthew (17:50 1/4/2003)
Phlamethrower (17:52 1/4/2003)
Matthew (22:58 1/4/2003)
bgilon (14:00 31/10/2003)
Paolo Zaino (14:30 1/11/2003)
|
| |
|
James Shaw |
Message #37865, posted by Hertzsprung at 13:42, 21/3/2003 |
 Ghost-like
Posts: 1746
|
Here's the problem:
I have two tables: group with columns id and name; and profile with columns uid and gid.
How do I select all rows from group where the id does not match any gid in the profile table where profile.uid=n.
Something like this:
SELECT id, name FROM group WHERE id NOT IN (SELECT gid FROM profile WHERE uid=123)
And I want to be able to do it without sub-selects. |
| |
 [ Log in to reply ] |
| |
|
Jeffrey Lee |
Message #37866, posted by Phlamethrower at 14:00, 21/3/2003, in reply to message #37865 |
 Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot stuff
Posts: 15100
|
SELECT * FROM group WHERE NOT EXISTS (SELECT * FROM profile WHERE group.id = profile.gid AND uid=123)
I've copied this from some lecture notes, and it works in Access, so I guess it's OK. I'll have a look around and see if there's any way of getting rid of that sub-select |
| |
[ Log in to reply ] |
| |
|
Simon Wilson |
Message #37898, posted by ksattic at 18:49, 21/3/2003, in reply to message #37865 |
 Finally, an avatar!
Posts: 1291
|
SELECT * FROM group, profile WHERE uid = 123 AND id <> gid |
| |
[ Log in to reply ] |
| |
|
Jeffrey Lee |
Message #37899, posted by Phlamethrower at 18:57, 21/3/2003, in reply to message #37898 |
Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot stuff
Posts: 15100
|
Nope; he wanted a query to list all the groups which a user is not a member of.
(Don't know why he didn't just say that in the first place, but that's just  for you  ) |
| |
[ Log in to reply ] |
| |
|
Simon Wilson |
Message #37900, posted by ksattic at 19:35, 21/3/2003, in reply to message #37899 |
Finally, an avatar!
Posts: 1291
|
Yeah, I thought I had misunderstood his request. After I posted, I thought my code looked a bit too short. I still don't really understand what he's trying to do with those schema!  |
| |
[ Log in to reply ] |
| |
|
Simon Wilson |
Message #37901, posted by ksattic at 19:58, 21/3/2003, in reply to message #37900 |
Finally, an avatar!
Posts: 1291
|
Could someone put me out of my misery and tell me what's wrong with my query (  )?
Before the join, assume the tables contain:
id name
1 X
2 Y
3 Z
uid gid
4 1
5 2
6 2
After the join, the table looks like this, with the selections from the where clause highlighted:
id name uid gid
1 X 4 1
2 Y 4 1
3 Z 4 1
1 X 5 2 <--\ <--\
2 Y 5 2 <--+-- uid = 5 +-- and id <> gid
3 Z 5 2 <--/ <--/
1 X 6 2
2 Y 6 2
3 Z 6 2
Isn't this the desired result (the groups [1 and 3] that user 5 isn't a member of)? Where's the :confused: smiley?  |
| |
[ Log in to reply ] |
| |
|
Matthew Somerville |
Message #37948, posted by Matthew at 15:57, 23/3/2003, in reply to message #37901 |
Posts: 520
|
If your second table was instead:
uid gid
4 1
5 2
5 3
6 2 Then the result with your query is:
1 x 5 2
3 z 5 2
1 x 5 3
2 y 5 3
but you only want the answer 1 (as 5 is a member of 2 and 3). |
| |
[ Log in to reply ] |
| |
|
Simon Wilson |
Message #37950, posted by ksattic at 16:51, 23/3/2003, in reply to message #37901 |
Finally, an avatar!
Posts: 1291
|
Thanks, Matthew - I understand my error now. |
| |
[ Log in to reply ] |
| |
|
James Shaw |
Message #38073, posted by Hertzsprung at 12:39, 25/3/2003, in reply to message #37950 |
Ghost-like
Posts: 1746
|
Thanks, Matthew - I understand my error now. So, erm, what's the answer?  |
| |
[ Log in to reply ] |
| |
|
Jeffrey Lee |
Message #38075, posted by Phlamethrower at 12:51, 25/3/2003, in reply to message #38073 |
Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot stuff
Posts: 15100
|
That there's still no way to do it? |
| |
[ Log in to reply ] |
| |
|
James Shaw |
Message #38079, posted by Hertzsprung at 13:02, 25/3/2003, in reply to message #38075 |
Ghost-like
Posts: 1746
|
That there's still no way to do it? Bah, there /must/ be! |
| |
[ Log in to reply ] |
| |
|
James Shaw |
Message #38092, posted by Hertzsprung at 13:52, 25/3/2003, in reply to message #37898 |
Ghost-like
Posts: 1746
|
SELECT * FROM group, profile WHERE uid = 123 AND id <> gid Perhaps
SELECT DISTINCT * FROM group, profile WHERE uid = 123 AND id <> gid
might work? |
| |
[ Log in to reply ] |
| |
|
Jeffrey Lee |
Message #38095, posted by Phlamethrower at 14:16, 25/3/2003, in reply to message #38092 |
Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot stuff
Posts: 15100
|
Nope. |
| |
[ Log in to reply ] |
| |
|
James Shaw |
Message #38113, posted by Hertzsprung at 14:44, 25/3/2003, in reply to message #38095 |
Ghost-like
Posts: 1746
|
Nope. |
| |
[ Log in to reply ] |
| |
|
Malcolm Ripley |
Message #39040, posted by mripley at 15:05, 1/4/2003, in reply to message #37865 |
Member
Posts: 5
|
Here's the problem:
I have two tables: group with columns id and name; and profile with columns uid and gid.
How do I select all rows from group where the id does not match any gid in the profile table where profile.uid=n.
Something like this:
SELECT id, name FROM group WHERE id NOT IN (SELECT gid FROM profile WHERE uid=123)
And I want to be able to do it without sub-selects. Try this :
select id,name,uid
from group,profile
where gid(+) = id
group by id,name,uid
having uid <> 123
You need to have the superfluous grouping to be able to use the having clause.
I've probably got the left/right join the wrong way round.
Malcolm |
| |
[ Log in to reply ] |
| |
|
Matthew Somerville |
Message #39112, posted by Matthew at 17:50, 1/4/2003, in reply to message #39040 |
Posts: 520
|
select id,name,uid from group,profile where gid(+) = id group by id,name,uid having uid <> 123 How is this different from SELECT id,name,uid FROM group,profile WHERE gid=id AND uid<>123 ? Does the (+) mean something? It doesn't work with the (+) in MySQL here, and without the (+) appears to be the same as the simpler SELECT statement.
I've been playing around and I can't see any way of doing it in one SELECT. I personally would just use a sub-select or even two separate selects for clarity of code - why does it have to be done in one? |
| |
[ Log in to reply ] |
| |
|
Jeffrey Lee |
Message #39113, posted by Phlamethrower at 17:52, 1/4/2003, in reply to message #39112 |
Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot Hot stuff
Posts: 15100
|
Apparently the web host he's using doesn't have a version of MySQL that supports sub-selects. |
| |
[ Log in to reply ] |
| |
|
Matthew Somerville |
Message #39176, posted by Matthew at 22:58, 1/4/2003, in reply to message #39113 |
Posts: 520
|
Then I'd just not waste any time on it and do one select to find out the groups the uid belongs to, and then another select to fetch the complement of that. The overhead, unless this is for some *huge* site, will be minimal, I should think. |
| |
[ Log in to reply ] |
| |
|
Benoît Gilon |
Message #47618, posted by bgilon at 14:00, 31/10/2003, in reply to message #37865 |
Member
Posts: 2
|
OK I think that the outer join is the solution to your asking.
"select g.id, g.name
from
group as g outer join profile as p on g.id = p.gid
where
p.gid is null
group by g.id, g.name;"
Provided that the gid attribute is defined with the "not null" cnstraint in the profile table definition.
Here you can use either the "group by" clause or the distinct clause after the "select" keyword..
Perhaps you should have to swap the two tables references in the out join clause(do not have MySQL on hand for testing, going with PostgreSQL..).
But my advice would be to go for subselects as the query looks much clearer for a human.
Hope this helps,
Benoît |
| |
[ Log in to reply ] |
| |
|
Paolo Fabio Zaino |
Message #47640, posted by Paolo Zaino at 14:30, 1/11/2003, in reply to message #37865 |
Member
Posts: 61
|
If i undestood well your request this should be your query:
SELECT DISTINCT dbo.group.id, dbo.group.name
FROM dbo.group LEFT OUTER JOIN dbo.profile ON dbo.group.id = dbo.profile.gid
WHERE (dbo.profile.uid IS NULL)
This optimized query will show you only group.id, group.name that doesn't match profile.gid where profile.uid=123.
I used the DISTINCT in case you can obtain duplicated rows.
MySQL supports INNER JOIN, on mySQL you should omit the 'dbo.' and maybe the 'LEFT' too.
Hope this helps
[Edited by Paolo Zaino at 16:48, 1/11/2003]
________
RISC OS Projects: https://github.com/RISC-OS-Community
RISC OS Blog: https://paolozaino.wordpress.com/category/risc-os/
Cheers! |
| |
[ Log in to reply ] |
| |
|
